Alternative to Merged Cells (Quick Tip)

Typically, many users are drawn to the Merged Cells feature in Excel, despite it having some significant disadvantages (especially related to copy/paste of ranges).

Frequently, Merged Cells are used in areas to group cells together visually, which can be done differently without causing the issues that merging does.

You can use an option within formatting called ‘Center Across Selection’ which takes the text from the leftmost cell of a range and centers it across the selected range.

Here is a screenshot of how to find it / use it by right clicking on a range and going to ‘Format Cells’.  Then click the Alignment tab as shown below (the Center Across Selection option):

Voila! The text is centered across multiple cells without the need for merging them.

You can also remove the gridlines in Excel to make it look a bit cleaner.

That option can be found here under the ‘View’ tab:

VBA Walkthrough #2: Message Encoder & Decoder

This VBA macro is hopefully a bit more interesting than the last, and shows how I created a message encryptor/decryptor in Excel using VBA/macros.

The lesson covers these specific areas of VBA usage:

– For…Next loop (including nested loops)

– If…ElseIf…Then Statement

– Excel functions: CHAR() / LEN() / VLOOKUP()

– VBA functions: Mod / Mid / Asc / Chr

Download the Message Encryptor & Decryptor here to follow along with the video lesson.

VBA Walkthrough #1: Purchase Order Generator

VBA Macro Walkthrough

One of the best ways of learning VBA is to take a look at completely functioning macros & code to see how others approach a problem.  In this video lesson, I walk you through a Purchase Order Generator that I created using Excel and VBA.  Throughout the video, the following topics are discussed:

– How to create unique userforms which allow users to input information in a separate window.

– Usage of VBA userform toolbox items such as Label, ListBox, TextBox, and Buttons.

– Coding related to specific events (when an item in a list is chosen, or a button is clicked).

– Declaring variables, and using worksheet functions in VBA.

– Populating Purchase Order sheet with relevant product information based on user’s selections.

Download the Purchase Order Generator workbook to follow along.

Also, this is the first video lesson with HOMEWORK!!  Please feel free to complete the assignments in the worksheet and send to ben@excelexposure.com for feedback.

 

See the Guest Video Lesson of the Homework Solution here!

Video Lesson: Conditional Formatting in Excel

Conditional Formatting can be very useful when presenting data in a spreadsheet, or for allowing information to jump out to you easily.

In the following video lesson I go over the following uses of it:

– Formatting based on numerical values (greater than, less than, between, etc).

– Formatting based on text contained in cells.

– Quick overview of Data Bars, Color Scales, and Icon Sets

– How to use custom formulas with Conditional Formatting to highlight entire rows or other cells (extremely useful / versatile).

Make sure to download the most recent Master Workbook to follow along!

Correction: The ‘Conditional Formatting’ option usually is located under the ‘Home’ tab and not the ‘View’ tab as referenced in the video.  That is due to my custom group that I have on my home tab.  To learn more about customizing your ribbon (aka the top menu area), see my Five Powerful Excel Tips video.  Sorry for any confusion, and thanks to Elizabeth in the comments for pointing it out!

Video Lesson: Introduction to Macros & VBA

Using macros in Excel requires knowledge of Visual Basic for Applications (VBA).  In this video lesson you will get an introduction to how macros work within Excel and how to build them.  The video lesson includes the following topics:

– Setting up Excel workbooks to use Macros & VBA (filetype, menu and security settings).

– How to record a macro and edit recorded VBA code.

– How to write custom macro including InputBox and MsgBox functions.

– How to assign buttons and shortcuts to macros.

Make sure to download the Visual Basic Examples workbook here to follow along.

Video Lesson: Introduction to Pivot Tables

Pivot Tables are an extremely useful way of analyzing large sets of data quickly and easily in Excel.

This video lesson provides a beginner’s introduction to Pivot Tables.

Download the most recent Master Excel Workbook to follow along.


Now that you know the basics, it’s time to get your hands dirty with Advanced Pivot Tables – Round 1

Video Lesson: Advanced Filtering in Excel

Using auto-filters in Excel provides a useful way to drill down and extract data from spreadsheets.

This video lesson explains multiple ways you can use filters in Excel.

Make sure to download the most recent Master Workbook so that you can follow along.

Next in the Lesson Guide is Creating and Designing Charts (including Sparklines)

Video Lesson: Logical Functions and Formulas

This Excel video lesson is about Logical Functions and Formulas in Excel.  Here are the formulas that I go through in the video lesson:

Logical formulas are extremely useful in more advanced Excel spreadsheets/methods and they are a great tool to have at your disposal.

Make sure to download the most recent Master Excel Workbook so that you can follow along. The spreadsheet I’m reviewing is in the Function Examples sheet in that Excel file.

The formulas covered in this video lesson are:

IF – IFERROR – AND – OR – TRUE – FALSE – NOT

Thanks for watching and feel free to leave any questions or comments in the comment box below!  I will respond via email if you include it with your comment.

Quick Tip: Remove Duplicate Values from List

Many times there are instances when you’ll have a large amount of data and you’ll want to be able to extract a listing of one specific column with only one line for each item type (for example, you may have a listing of all 2,500 customer addresses with a country field and may want to know the full list of countries represented by the customers in the listing).

Here’s a quick and easy method I use to extract the information (and this method is also a small introduction into using logical formulas, which will likely be the next video).  Using an IF function and some sorting, you can extract a listing of information without the duplicate values.

Here’s a screenshot with instructions for how to do it (click to see enlarged version):

Remove Duplicate Values

Bonus HW assignment: Try to think of other ways that you may be able to get a similar listing without using my method above.

Excel Video Lesson: Data Validation

This Excel video lesson walks you through how to set up Data Validation rules.  The main purpose of Data Validation is to set up rules so that only certain types of information can be entered into cells (which helps to reduce manual entry error).

Make sure to download the most recent Master Workbook to follow along.

Let me know if you have any questions!

Excel Add-In: ASAP Utilities

Hey Everybody,

Here is a really nifty add-in that I recently found (free for personal use, one-time fee for businesses).  It’s called ASAP Utilities.

Some examples of important features in the add-in:

–       Cell selection based on content, formatting, etc [examples: select all cells between 50 and 250 or all bold cells or all red cells]

–       Advanced sorting [examples: font color, bold, formula, text length, etc]

–       Apply formula/calc to selections [example: add “/24” to the end of all formulas, or change formulas in bulk, copying formulas without moving references]

–       Easily export/import files [Excel, TXT, database, web, create CSV and delimited files]

–       Vision Control [allows for bulk editing of formatting across entire workbook]

–       20+ Additional Excel functions/formulas

Here’s a full list of features by category.

I recommend giving it a spin; it has loads of interesting/useful features and can be especially useful for beginners since it has an easy interface.

Please leave a comment to let me know of any specific sections of the lesson plan that you’re looking forward to.  I appreciate your patience with the videos!

– Ben

Excel Course: Book and Website Recommendations

I wanted to suggest a few books that I’ve used in the past which are extremely helpful in learning Excel.  I’d recommend checking any one of these books out as they have lots of useful information and examples for how to get the most out of Excel:

Excel for Dummies 2010 (All-in-One)

Excel 2010 Bible

Excel 2010: The Missing Manual

Excel 2010: Power Programming with VBA

The last book is specifically related to Macros and involves programming with VBA (Visual Basic for Applications).  I would recommend any of these as self-teaching tools for doing additional learning outside of my lessons.  Many of these have earlier 2007/2003 counterparts which would be good if you have that version of Excel.

Additional Excel Online Training Resources

– Educational: Chandoo

– Educational: OZgrid

– Educational: Chip Pearson

– Educational: Mr. Excel

Educational: Excel Easy

– Help: Microsoft Online Answers Site

– Excel Templates: Vertex42’s list of free Excel templates, spreadsheets, and calculators.

Non-Excel Related Websites

Orlando – Web Design and WordPress Support.

Video Lesson: Lookup / Reference Functions

As listed in the Master Workbook (see link at the top of the page), there are functions grouped into categories. This video is a demonstration of the Experience Level 1 – Lookup & Reference functions.

The functions covered in this video lesson are:

ADDRESS  — COLUMN — COLUMNS — HLOOKUP  — INDEX — INDIRECT

LOOKUP — MATCH — OFFSET  —  ROW  —  ROWS  —  VLOOKUP

Extract of Level 1 – Lookup and Reference Functions from the Master Workbook:

A good follow-up lesson to check out would be either the Advanced Vlookup Examples or Advanced Lookup – Index & Match lessons.

Excel Video Lesson: Naming Ranges and Cells

Named Ranges in Excel

This video tutorial explains how you can name ranges or cells for use within functions & formulas.  It is a great follow-up to the last video lesson on Absolute & Relative cell referencing.

I’ve found it incredibly useful throughout Excel.  It helps to alleviate the concern that you’re pulling information from the wrong area, and makes formulas more easy to follow.

 

If you found this Named Ranges video interesting, you may want to check out the Five Powerful Excel Tips video which goes over Dynamic Named Ranges (which allow for the named ranges to change as more data is added to them).  It’s the first tip in the lesson!

Video Lesson: Absolute & Relative Referencing

Absolute and Relative Cell Referencing

Note: This video was originally the 3rd lesson in the plan, but due to the comments regarding it being too confusing at that level, I have moved it to the Intermediate section effective 3/23/17.

This video tutorial focuses on Absolute vs. Relative cell referencing within Excel.  You likely know it as the dollar sign ($) used in functions/formulas (for example, C5 vs $C$5).

There is also a quick demonstration of the video using a VLOOKUP formula.  The VLOOKUP formula is explained in more detail in these two lessons here and here, but the example in this video gives a basic understanding of how it works in Excel.

Update 5-22: I’ve noticed some users (especially those with Macs) have had difficulty viewing this video.  Since it is one of the older videos, I will be re-recording it and adding information so that it is comparable to more recent videos.

If you’re following the Lesson Guide, next up is Math & Statistical Functions (e.g. SUM, AVERAGE)

Quick Excel Tip – Grouping Rows / Columns

As I mentioned in reply to haeso’s comment on the first video, there is method of joining rows or columns together called ‘grouping’.  It can be very useful when you want to have different ways of displaying data, or be able to collapse certain areas (underlying details, for example).

Here’s a screenshot explaining how you can do this using the menus in Excel 2010:

Explanation of Grouping Rows/Columns in Excel

A shortcut way to group rows or columns is to highlight the rows/columns you wish to group and use ALT+SHIFT+RIGHT ARROW to group the rows/columns, and ALT+SHIFT+LEFT ARROW to ungroup them. You can go multiple levels as well (so you could group rows 1-30, and then group rows 20-25 as a subgroup of the first). The levels are displayed at the top of the red box in haeso’s screenshot and can be clicked to view the entire sheet at any of the levels.  Remember: this can be done with both columns and rows (separately).

Excel Video Lesson: Text Functions

This video tutorial is a demonstration of the Experience Level 1 Text functions.

Make sure to download the newest version of the Master Workbook that I updated earlier today to follow along.

The functions covered in this lesson are:

CONCATENATE — EXACT — FIND — LEFT — LEN — LOWER — MID — PROPER
REPLACE — RIGHT — SEARCH — TEXT — TRIM — UPPER

Extract of Level 1 – Text Functions from the Master Workbook:

Text Functions marked as Level 1

Next video lesson is Logical Functions and Formulas.

Any feedback is appreciated.

Excel Video Lesson: Date & Time Functions

As listed in the Master Workbook, there are functions grouped into categories. This video is a demonstration of the Experience Level 1 Date & Time functions.

The functions covered in this video lesson are:

DATE — DATEVALUE — TODAY  — DAY  — MONTH — YEAR
TIME — TIMEVALUE — NOW — HOUR — MINUTE — SECOND

CORRECTION: I mention in the video that Excel views dates as the number of days since January 1, 1900 but it is actually January 0, 1900 (a bit confusing since January 0 doesn’t make intuitive sense).

Now that you’re an Excel ninja when it comes to Date & Time functions, try mastering Text Functions (e.g. LEN, CONCATENATE)