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

Starting My Bilingual Summer

Christine Gilbert, author of the wonderful and inspiring travel lifestyle blog Almost Fearless, recently wrote about her intention to learn a new language this summer, and challenged her readers to join. Well, join and compete with her, using Duolingo, a free online language learning site promising no ads or hidden fees now and forever. That’s right, free, forever.

Duo, the Duolingo owl.
Duo, the Duolingo owl.

I’ve been talking about learning a second (or third) language since I was born, so I’ve decided to join and compete with her. She’s crushing me at the moment, but that’s the point. Duolingo brings an unusual twist to the tired language-learning routine: competition. By adding Christine as my friend directly through the site, along with a few friends I’ve taunted or tricked into competing, each of our scores are plotted next to one another on the Leaderboard; the more I practice, the more “skill points” I earn, and the closer I’ll be to dethroning her and dominating my friends. That’s my plan, anyway.

¿Qué Hora Es?

This isn’t my first showdown with Spanish – we were first introduced many years ago in middle school, then again over two semesters in high school; we had another go in college, and one or two more feeble attempts as a young professional. And yet over a decade after our first affair I can only manage basic introductions, a few random verbs, and asking for the time, and that’s only because I’ve probably asked for the time more en Español than I ever have in English.

So what makes this time different? In all of my previous attempts I went about language learning in all the wrong ways. I took classes because I had to; I bought books I never made time to read; I let days and weeks lapse into months without practicing; I never enlisted a tutor’s help or sought conversation with native speakers; I never surrounded myself with the language. I never had a plan.

In her post Christine offers some smart suggestions to go from thinking about learning a language to actually learning one:

Create an immersive environment this summer full of movies, music, books and other media in that language. Reach out to native speakers on sites like for language exchanges. Find local resources. Be creative.

This Time, I Have a Plan

As I begin my own bilingual summer, here’s what I intend to do over the next several months:

  1. Actively use the language every day. There are no limits here. It could be 15 minutes of deconstructing the language, a half hour speaking with a tutor, listening to Spanish podcasts, watching Telemundo, or competing on Duolingo for a bit just after breakfast.
  2. Create an immersive environment full of movies, music, books and other media in Spanish (see above).
  3. Connect with a native speaker. Since I’m traveling each week to Toronto this one might prove tricky, but I’m determined to come up with an arrangement that works and share it here.
  4. Master the most-written and most-spoken Spanish words. In the past I’ve wasted too much time following rigid textbooks that dithered in rarely used vocabulary or complex phrases; since my goal this summer is to confidently converse with native Spanish speakers this feels like a good place to start.

I’ll revisit each of these periodically to write about what’s working and what’s not, but until then: Aquí vamos!

Grado iGi In-Ear Headphones Review

Santa was good to me this year. I’ll use the next few posts to talk about some of the shiny toys I’ve been playing with, but today I’d like to focus on my new headphones: Grado Labs iGi In-Ear Headphones.

Grado Labs is a family owned company based out of Brooklyn, NY, probably best known for their award-winning on-ear style SR80i’s, which were also recently voted by Lifehacker readers as the best (out of the five best) headphones.

I have nothing against on-ear headphones – I’m sure the SR80i’s deliver fantastic sound – but as a traveling road warrior I prefer the minimalist in-ear style so I can save space and pack lighter on trips. With that, let’s dive into this review:

  1. Sound: I’ll say this upfront, I’m no audiophile. I’m not an audio engineer and I’m not a professional mixer. That being said I listen to a lot of music, as in 80% of my day.
    Shown with the white stacked tips.
    Shown with the white stacked tips.

    The iGi’s offer a fairly balanced sound – the bass is there, but doesn’t thump like some other pairs I’ve tried. The mid- and high-ranges are quite clear to my ears, and sound nice while listening to a range of music. If you prefer balance, you’ll like these. If you want an over-driven 808, you might want to look elsewhere.

  2. Comfort: Grado does a nice job of providing options for ear comfort. I’m very particular about the ability to create a seal with my in-ear headphones, which you can definitely do with this pair. The iGi’s come with seven different interchangeable tips – two stacked, one cone, one foam, and three normal buds (small, medium and large) – so it’s very likely you’ll find at least one that works for you.

    From left to right: stacked tip, cone, foam, normal buds. White stacked-tip not shown.
    From left to right: stacked tip, cone, foam, normal buds. White stacked-tip not shown.
  3. Quality: I take extra care of my electronics (i.e. I’m not hard on my possessions whatsoever), so quality is a little harder for me to judge. I’ve had $12 headphones last nearly two years with constant use. The body of the iGi’s are clearly joined plastic (you can see the joining line), so nothing too special there. The left ear-bud does have a raised dot, which is a nice feature when putting them on in the dark. The cord is thin and susceptible to a lot of noise when it brushes against your clothing or any other object, but this is easily fixed by looping the cords over your ears, which I almost always do regardless. The quality of the iGi’s is enough if you take care of them but nothing to brag about, especially when you compare them to the much cheaper Skullcandy Ink’d.
  4. Price: at $89 on Amazon the iGi’s are more expensive than the $27 Skullcandy Ink’d I’ve been rocking as my cheaper-but-solid go to pair, so I would call these a step or two up in terms of price. The price won’t break the bank (in most cases), but it’s high enough that I certainly wouldn’t want to replace them often, if ever.

Overall I’m satisfied with the iGi’s, but not amazed given the Grado name. The delivered sound is clear and balanced, which is refreshing, but I was hoping for a little more bass due to their self-proclaimed “urban-inspired design” methodology, and a little better build quality given the price.

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.

Mount ISO Images in Windows 8

Well, it took a long ass time but Windows users can finally do what Linux and Mac nerds have been able to do… for about as long as I can remember: mount ISO images without third-party tools.

That’s right. Mounting ISO images in Windows 8 is as simple as right-clicking the ISO image file, then clicking Mount.

ISO images are commonly used when downloading programs that you would typically install via CD or DVD. For example, after recently installing Windows 8, I downloaded the Office 2010 ISO image from Microsoft. Normally I’d have to do one of two things to this file before I could actually use it to install Office on Windows 7 and any earlier versions:

  1. Use a third-party tool to mount the ISO image, which then makes all the files it contains available for use.
  2. Burn the ISO image to a DVD and install it like it’s 1995.

Since downloading the correct third-party tools is a hassle, and since 1995 was 17 years ago, you can imagine how happy I was to see this when I right clicked on the ISO image file:

Windows 8: Right Click on ISO Image to Mount
New to Windows 8: right click ISO image files to mount.

As you can see, after clicking Mount Windows assigned the next available drive letter (in my case, E:) and switched to the file browser. All that was left to do was double-click setup and install Office.

Mounted ISO Image
Once mounted, Windows allows you to browse through the files contained within the ISO image.

I like it.

How to Restart, Shut Down & Sleep in Windows 8

Microsoft’s been pretty busy lately, releasing a substantially redesigned Windows 8 just last week and putting the final touches on Office 2013 to be released in Q1 2013.

I just installed Windows 8 a few hours ago, so this first post will address something which has already given many people heartburn: how the hell do we restart this thing? Or shut it down, for that matter?

Good news, it’s still easy to do.

  1. Press the Windows Key + C (Win + C) to pop open the new sidebar.*

    Windows 8 Sidebar
    Press Win + C to activate the Windows 8 sidebar.
  2. Click Settings → Power

    Windows 8 Settings > Power
    After pressing Win + C, click Settings > Power to see sleep, shut down and restart options.
  3. Click either Sleep, Shut down or Restart.

End scene.

* Note: you can also point your cursor in the upper right corner of your screen to activate the sidebar, but I prefer using keyboard shortcuts instead.

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.