Merge Worksheets in Excel

It’s no secret that I love Excel. I’ve said it before, I’ll say it now and I’m sure I’ll hum this tune again next week.

Today I’m tackling a common problem that’s plagued office heroes for years. How do you merge multiple Excel worksheets into one master worksheet, automatically, and without macros or VBA? Down the line I’ll discuss other methods to merge and consolidate data in Excel, but today I’m focusing on this particular situation: you have more than one worksheet, all formatted with the same columns and rows, but each worksheet contains different data that you want to pull together and summarize.

Lucky for you, Excel has a neat little Consolidate feature that’s powerful, flexible and very easy to use. Best of all, it requires no formulas, macros or VBA code. Here are some typical cases where this feature comes in handy:

  • Roll up expenses from multiple offices, branches, employees, children, etc.
  • Roll up sales from multiple customers, products, months, quarters, years, and so on.
  • Merge worksheets that were previously distributed among colleagues or other groups of people for updating back into one master worksheet.

The file I’m working with, which you can download here or at the end of this post, is for a fake used car dealership that sells the Honda Accord, Honda Civic, Toyota Corolla, Toyota Matrix and Toyota Camry. There are 30 employees and three separate worksheets (one for January, February and March), each containing the total cars sold that month per employee, per car.

Let’s get started.

How It’s Done

  1. Ensure that the worksheets you intend to merge all have the same columns and rows in the same order.
    1. For example, notice that the January, February, and March worksheets all have the same columns and rows in the same layout.
    2. The only difference between the worksheets to merge is the data contained within these columns and rows.

      Same Layout Required for Merge
      All worksheets to merge should have the same layout.
  2. Create the Merged Master worksheet. This worksheet should look identical to the other worksheets (same columns, rows and layout) except the space to contain the mergeddatashould be blank.
    1. Right click on the January worksheet and select Move or Copy…
    2. Double-click on the newly created January (2) worksheet, type “Merged Master” and press Enter.
    3. Select cell B5, press Ctrl + Shift + ↓ + → to select the entire data range (B5 through G35). Press Delete.

      Blank Merged Master
      The newly created “Merged Master” worksheet should have the same layout, but without any data.
  3. Select cell B5 again. Navigate to Data → Consolidate.
  4. In the Consolidate dialog box that appears, under Function, choose the summary function thatyou want Excel to use to consolidate your data.
    1. We’ll leave the default, Sum, since we’re looking to sum the sales from January, February and March, but I suggest trying out different functions (Count, Average, Max, Min, etc.) to see what they do.
  5. Under Reference click the small Select Range Button button, then select the January worksheet, select cell B5, press Ctrl + Shift + ↓ + → to select the entire data range (B5 through G35) and press Enter.
    1. You’ll see the range you just selected now appears in the Reference box. Click Add to include this range in the All References box.
  6. Click the small Select Range Button button again. This time when you click the February worksheet you’ll notice the same range appears in the dialog box, except it’s been updated to February instead of January. Press Enter, then click Add.
  7. Repeat step six for March. Your Consolidate dialog box should now look like this:Consolidate Dialog Box
  8. When you click OK Excel will complete the merge, and the Merged Master worksheet will now contain the consolidated first quarter sales for each employee. Viola!

    Consolidated Master
    Excel automatically summed the data from all three worksheets, sans formulas or VBA.

A Few Notes

  1. If you select the Create links to source data option, Excel will automatically update the newly consolidated data on the Merged Master worksheet whenever you update any of the source values from the other worksheets.
  2. If the data you want to consolidate is contained in separate workbooks, you can simply select the Browse… button to find that workbook, then click OK to close the Browse dialog box. Excel automatically includes the file path in the Reference box followed by an exclamation point.
  3. It’s important to remember that the space to contain the merged data should be blank, since Excel will automatically overwrite anything it finds in that space when it completes the merge.

Practice Yourself

The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!

Merge Multiple Worksheets

Dynamic Data Validation with Excel 2010 & 2013

It’s Christmas, and like many of you I find myself thinking about dynamic data validation using the structured tables that Microsoft introduced in Excel 2010. Welcome to my life.

What’s Data Validation?

When I say data validation I mean the ability to specify exactly what kind of data users may (or may not) enter into certain cells. This is particularly useful when you are creating forms or spreadsheets for other users to fill out, and you want to make sure that they enter the data you were expecting. For example, you can use data validation to prevent other users from entering numbers or special characters into cells or fields when you only wanted text. Using data validation helps keep your data clean, and everybody should like clean data.

The Old Way: Static Data Validation

In the olden days most people created a one column “source list” on a separate worksheet or somewhere within their current worksheet to contain the source values for in-cell drop-down lists (i.e., data validation). For example, if you only wanted to allow users to enter the values “A,” “B,” “C,” or “D” into a particular cell, you would first type a list of those values, one per line. You would then select the cell on which to use data validation, navigate to Data → Data Validation, select “List” under “Allow” and your list of letters under “Source.” It would look something like this:

Old Data Validation
The source list contains the values available for use under “Select a Value.”

The problem with this approach is that if you wanted add or remove values from the in-cell drop-down lists, first you had to find and edit the source list, then you had to manually update the range (i.e., the “Source”) for every single cell where that source list was used for data validation. In our example above, in order for us to add “E” as an available value in our in-cell drop-down, first we would have to add “E” underneath “D” in our Source List then update the Data Validation “Source” range for our “Select a Value” cell to include cell A12.

In other words, it was a pain in the ass. If you had ten cells referring to a single source list, updating those ten cells to include the updated range might not be so bad. But what if you had 100 cells, or 1,000 cells, all referring to that source list? Starting with Excel 2010, you can now add values to, or remove values from, all cells using a list for data validation by simply adding or removing those values from the source table. No need to find and update the “Source” range for all of the cells using that list for Data Validation. Excel will take care of updating all the cells referring to this source list automatically.

The New Way: Dynamic Data Validation Using Excel Tables

  1. Highlight cells A1:A5 and navigate to Home → Format as Table, and select a table format that suits your style. If you already have a header for your table, check the “My table has headers” box, otherwise leave blank.
  2. If you left “My table has headers” blank, rename “Column 1” to something more meaningful. I typed Data Validation. I then entered values 1 through 5 into the table, but you should enter any values that you actually want to use.
    Data Validation Create Table
  3. Select any row on the table and navigate to Table Tools → Design → Table Name: and rename the table to something meaningful. I entered DATA_VALIDATION.Data Validation Name Table
  4. Select all records on the table (excluding the header), then navigate to Formulas → Define Name and give the highlighted range a meaningful name. I entered RNG_DATA_VALIDATION. Notice in the “Refers to” section is the name of our newly created table.Data Validation Name Range
  5. Select cell C1 and navigate to Data → Data Validation. In the Data Validation dialog box, on the Settings tab, choose List under “Allow.” Under “Source” enter the equal sign (=) then the name of the newly named range (note, do not use the table name). In my case I entered =RNG_DATA_VALIDATION.
  6. Click on the Error Alert tab if you want to display a pop-up box with an error message when the user enters invalid data. I find it’s usually good practice to offer some sort of helpful error message, such as “Invalid Entry: Please choose a value from the in-cell drop-down list.”
  7. Click OK.

You should now see a drop-down arrow appear when you select cell C1; when you click that drop-down you should see the values from the table created earlier. Test your data validation by entering values not contained on your table – if you did it correctly, Excel should display an error message when you try entering a value not found on the in-cell drop-down list.

Data Validation First Table

The most important point here is that you can easily add values to this list by simply adding new values to the source table. Since our data validation references a named range, and since that named range is based on a named Excel table, we never need to adjust the data validation range if we want to add more values to this list. Excel handles it all behind the scenes.

To see what I mean, select the first available cell below your table, type a new value and press Enter. In my case I selected cell A7, typed 6 and pressed Enter. Excel automatically adjusted the table size to include this new record. When you click on the in-cell drop-down list in cell C1, the new value should appear.

Data Validation Final Table

Notice that we didn’t have to go back into the data validation settings for cell C1 and adjust the range to include cell A7? This might not seem like a big deal using data validation in only one cell, but imagine how much time (and headaches) you can save when dealing with multiple (say a few hundred or more) cells using list-based data validation.

Caution: when removing values, do not simply select the table value and press delete. Blank table rows will disable Excel’s data validation and allow users to enter any old value they wish into the cell using data validation. Instead, select the record you wish to remove and navigate to Home → Delete → Delete Table Rows.

Ditch the Mouse: Quickly Select Excel Ranges With Your Keyboard

Mouse: Just Say NO
I may have made this in Paint.

I’m going to write a lot about ditching the mouse in favor of keyboard shortcuts, so consider this one of many to come. And while we’re at it, get used to me spamming this next sentence throughout my shortcut posts: Using keyboard commands over hunting and clicking with the mouse will save you time. Period.

Next time you open Excel to manage a list, a table with headers or just giant blocks of data, please, please avoid using your mouse to select or highlight cells and cell ranges. Use these keyboard shortcuts instead:

  • Ctrl + Shift + Arrow Key: I consider this among the most useful Excel keyboard shortcuts around. Extends the selection of cells to the last nonblank cell in the same column or row as the active cell, or if the next cell is blank, extends the selection to the next nonblank cell.
  • Ctrl + Arrow Key: Jumps to the edge of the current data region in a worksheet.
  • Ctrl + Home: Jumps to cell A1 no matter where you are on a worksheet.
  • Ctrl + End: Jumps to the right bottommost nonblank cell.
  • Shift + Arrow Key: Extends the selection of cells by one cell.

Out of these five navigation shortcuts I probably use the first and second 95% of the time. The first shortcut, Ctrl + Shift + Arrow, to select large blocks of data like a long list or a table. This one is especially useful if you have a table or any list with more than one column because you can press Ctrl + Shift + ↓ to select down the entire first column, then while keeping Ctrl + Shift pressed, hit  to quickly select the remaining columns to the right.

Some of the data feeds I work with regularly generate tables 30+ columns wide and often thousands of rows long, so being able to exactly select the entire table by simply pressing Ctrl + Shift + ↓ + → saves me a lot of time and hassle. The second shortcut, Ctrl + Arrow Key, is how I jump around in Excel to get from one end of my data to the other.

It’s super, so do it.

Excel 2013 Flash Fill

Flash Fill Create Names from Email Addresses

I’ve been playing around with the Excel 2013 preview, and damn. It’s nice.

Excel newbs and ninjas alike will find Flash Fill – one feature new to Excel 2013 – intuitive, powerful and super convenient. Flash Fill takes the grunt work out of list-making and data manipulation, relying on clever algorithms to recognize patterns and complete your lists for you, formula-free. That’s right, sans formula. And since list-making and data manipulation are two popular reasons many people use Excel to begin with, this will definitely be a hit.

Data Manipulation? What?

When I refer to data manipulation in this post I’m talking about any instance where you use Excel to change or modify the way your data looks. More often than not the data exists in lists too large for manual editing. Here are some examples to illustrate situations where you might use Flash Fill:

  • Converting phone numbers from XXXXXXXXXX to (XXX) XXX-XXXX
  • Formatting social security numbers to XXX-XX-XXXX
  • Removing incorrect or extra spaces from text
  • Formatting names in many ways (like Doherty, Ryan J.)
  • Converting dates from YYYYMMDD to M/D/YYYY
  • Inserting the leading zero back into Postal Codes (like 04101)

Flash Fill in Action

Let’s say you have a list of email addresses in the format firstname.lastname@fake-email.com, and you want to quickly create a Name column from that list.

Flash Fill Create Names from Email Addresses
Flash Fill recognized what I was trying to do and did the rest.

In Excel 2010 and earlier, this simple task would require using either Text to Columns, or some combination of the LEFT, FIND and CONCATENATE functions. It would likely need multiple steps. Or manual data entry, and nobody wants that.

In Excel 2013, I created the Name column and just started typing the first and last name in cell B5, like so: Jerry Peters. When I hit Enter to move down to cell B6 and typed “N,” which is simply the first letter of the next persons first name, Flash Fill did the rest. In the screen shot above, the ghost-like list from cell B7 down shows Flash Fill’s suggestion. You can go the other way, too (create email addresses from Name columns).

Flash Fill Create Email Addresses from Names
Once Flash Fill recognizes your pattern it shows a live preview.

Phone Numbers

Flash Fill Phone Numbers
I chose to use (XXX) XXX-XXXX, but could have used XXX.XXX.XXXX as well.

Start typing your data in the format you prefer. In the phone number example, I typed “(555) 123-4567” into cell B5. When I hit Enter and typed “(” into B6, Excel recognized that format and Flash Fill suggested how to finish the list for me. If I had typed “555.123.4567” into cell B5, Flash Fill would have suggested completing my list in that format instead (XXX.XXX.XXXX). (Note: since we’re using numbers mixed with text, Excel treats it all as text instead of numbers, so you will automatically get suggestions).

Flash Fill Treats Numbers as Text

My note above brings up a good point. Flash Fill already handles text like a pro, but definitely leaves room for improvement when it comes to numbers. Our friends from the Excel team sum it up best:

The algorithm engine we’re using looks at everything as text. So it doesn’t know any special rules that go along with numbers (for example, it’s OK to drop leading zeros when dealing with numbers, but not so much when you’re dealing with Postal Codes or other IDs).

So when it comes to using Flash Fill for dates, zip codes or other numeric-only data, we’ll still need a few extra steps to get what we want (like first formatting the target column as TEXT). Either way, it’s still a step forward.

Some Flash Fill Tips

  • To convert lowercase values to UPPERCASE, just start typing in UPPERCASE.
  • To remove extra spaces from values like addresses, just start typing the addresses without spaces.
  • The Flash Fill algorithm for pattern recognition treats numbers and digits the same as text. When working with numbers only, always format the target column as TEXT.
  • To get automatic suggestions:
    • You have to edit right next to related data (i.e. the next column over from your source data with no blank columns between).
    • You have to make two sequential edits, one right after the other.

My experience using Flash Fill so far has been mostly positive, with a few quirks now and then when it doesn’t work quite how I though it would. Come across a pattern that doesn’t work well with Flash Fill? Or think of a unique way to make use of this new feature? Let me know!

Data Cleaning with Flash Fill

Compare Two or More Lists in Excel with Conditional Formatting

I’ve recently posted two easy ways to compare two lists in Excel. One uses ISNA and MATCH and one IF and COUNTIF. I like both of these methods because they allow you to:

  1. Learn how to use and modify four powerful Excel functions
  2. Quickly apply AutoFilter to view just the missing values
  3. Easily manage and compare massive lists of data

For shorter lists, however, there is a much easier, non-formula way to compare two lists in Excel 2007 and later.

How to do it

  1. Arrange the lists in two columns with List A in column A and List B in column B.
  2. Highlight both lists.

    Highlight the entire range of data.
    Highlight the entire range of data.
  3. Navigate to Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values…
  4. In the Duplicate Values dialog box, select “Duplicate” from the drop-down list to highlight values (in our example, names) found on both lists and click OK.

    Apply conditional formatting to highlight duplicate values.
    Apply conditional formatting to highlight duplicate values.

It doesn’t get much easier than that. If you’d rather Excel highlight the values missing from either list, select “Unique” from the drop-down list on the Duplicate Values dialog box (step 4 above).

Apply conditional formatting to highlight unique values.
Apply conditional formatting to highlight unique values.

Practice Yourself

The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!

Compare Two or More Lists with Conditional Formatting

Compare Two or More Lists in Excel with IF and COUNTIF

As I previously discussed, Excel provides many useful ways to automatically compare two lists of data or information. In our other example we compared two lists of four digit account values; for this example we’ll compare two lists of names.

You and a friend are throwing an epic party and have each maintained separate guest lists to track who’s coming. After a few weeks you decide to compare both lists to make sure everyone on your friend’s list (List B) is also on your list (List A).

How to do it

  1. Arrange the lists in two columns with List A in column A and List B in column B.
  2. Create a third column in column D called List C (leave column C blank for easier readability).
  3. In cell D5 enter the formula: =IF(COUNTIF(B:B,A5),A5,0) and press Enter.

    Enter formula into cell D5
    Enter formula into cell D5.
  4. Select cell D5 and navigate to Home → Conditional Formatting → Highlight Cell Rules → Equal To…
  5. In the Equal To dialog box, type 0 and click OK.
  6. Select cell D5 down to the end of the lists and press Ctrl+D to copy the formula and conditional formatting down.

    Copy the formula down to the end of the longer list (List B in this example).
    Copy the formula down to the end of the lists.

The Result

A name in List C means the corresponding name from List A was also found within List B. In the screenshot above, cell D5 displays the “Moon Barrientos,” which means that name was found on both List A and B. On the other hand, cell D12 displays Red Zero which means the corresponding name from List A (in this case, “Aracely Rock”) does not exist within List B.

This example demonstrates how to determine if a particular value – in this case a name – in List A also exists within List B. To find the opposite – whether a name in List B also exists within List A – simply change the formula in cell D5 to: =IF(COUNTIF(A:A,B5),B5,0) and press Enter. Then copy that formula down to the end of the lists.

How & Why it Works

Our formula makes use of two functions, IF and COUNTIF. The IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. COUNTIF counts the number of cells within a range that meet the given condition.

The Formula

=IF(COUNTIF(B:B,A5),A5,0)

COUNTIF has two required arguments, or inputs, to work:

  • range (where should Excel look, B:B in our example)
  • criteria (what should Excel find, A5 in our example)

COUNTIF checks column B (using B:B checks the entire column) for the name in cell A5. If it finds the name (i.e. the criteria you specified was met) it returns 1, otherwise it returns 0. Note: when COUNTIF returns 1 or 0, Excel treats that as TRUE (1) or FALSE (0).

The IF function has three required arguments, or inputs, to work:

  • logical_test (any value or expression that can be evaluated to TRUE or FALSE)
  • [value_if_true] (what value should Excel return if if logical_test is TRUE)
  • [value_if_false] (what value should Excel return if logical_test is FALSE)

In our example COUNTIF is the logical_test that gives the IF function either a 1 or 0, depending on whether it finds the name we specified from cell A5 within column B. If it finds the name (i.e. the logical_test is TRUE), we specified A5 as the [value_if_true] so Excel will display that name and move on. If it does not find the name (i.e. the logical_test is FALSE), we specified 0 as the [value_if_false], so Excel will display 0 and move on.

You can quickly see a list of names missing from List B by filtering List C to show only “0.” The conditional formatting just makes it easier to spot the missing values.

Practice Yourself

The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!

Compare Two or More Lists with IF and COUNTIF

Compare Two or More Lists in Excel with ISNA and MATCH

People compare lists (or columns) of information all the time, and Excel provides several easy ways to it. In fact Microsoft sums up several real life examples quite nicely:

For financial record-keeping, you often have to compare two lists to find data that appears on one but not on the other. Which new clients need to be added to the master database? Which credit card receipts are not reflected on the monthly statement, and vice versa? What checks and deposits are still outstanding? It’s a tedious task if you do it manually.

In the example below we have two large lists of four digit account values: List A and List B. We want to know if any account values from List A are missing from List B, and vice versa. To do this we’ll write a formula using the ISNA and MATCH functions.

How to Do It

  1. Arrange the lists in columns, leaving at least two blank column in between. I chose column A for List A and column D for List B.
  2. Name the columns next to each list “Missing?” In this example, columns B and E are each labeled “Missing?”
  3. In cell B5 enter the formula: =ISNA(MATCH(A5,D:D,0)) and press Enter.
    Enter formula into B5
  4. Select cell B5 and navigate to Home → Conditional Formatting → Highlight Cell Rules → Text that Contains…
  5. In the Text That Contains dialog box, type TRUE and click OK.
  6. Select cell B5 down to the end of List A and press Ctrl+D to copy the formula and conditional formatting down the entire List A.
  7. In cell E5, enter the formula: =ISNA(MATCH(D5,A:A,0)) and press Enter.
  8. Repeat steps 4-6 for cell E5, making sure to copy the formula down the entire List B.
    Copy formulas down both lists

The Result

If any value from List A is missing from List B, a red TRUE will appear next to that value in List A’s “Missing?” column. If any value from List A is found in List B, FALSE will appear next to that value in List A’s “Missing?” column.

A TRUE = value is missing from the other list
A FALSE = value is not missing from the other list

You could take it one step farther by filtering either list to display only TRUE which would then only display the values missing from either list.

Done and done.

How and Why It Works

ISNA is the first function in our formula, which does only one thing in life: checks whether a value is #N/A (the error value Excel uses to say “no value is available”). If a value is #N/A in an ISNA formula, it’ll display TRUE. Otherwise it’ll display FALSE.

MATCH is the second function, which returns the relative position of an item in an array (in our example, either List A or List B) that matches a specified value in a specified order. In our example we are using MATCH to tell ISNA whether to display TRUE or FALSE.

If MATCH finds a value from List A within List B, it’ll return the relative position of the value in List B. Whenever MATCH returns the relative position and not #N/A, ISNA simply spits out FALSE. If MATCH does not find a value from List A within List B, it’ll return #N/A and ISNA will display TRUE.

The conditional formatting just makes it easier to spot the missing values.

The Formula

=ISNA(MATCH(what,where,type))

MATCH has three required arguments, or inputs, to work:

  • lookup_value (what do you want Excel to lookup or find)
  • lookup_array (where should Excel look. In our example, A:A and D:D selects those entire columns)
  • match_type (what type of a match Excel should find: the next largest, an exact match only, or the next smallest)

ISNA literally checks whether the cell value “IS #N/A,” or “IS NO VALUE AVAILABLE?”

  • If it IS #N/A, it’ll display TRUE.
  • If it IS NOT #N/A, it’ll display FALSE.

Practice Yourself

The best way to learn is to practice yourself, so click the link below to download the Excel 2010 workbook used to show the methods described in this post. Please feel free to share!

Compare Two or More Lists with ISNA and MATCH