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 to do it

  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 merged data should 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 that you want Excel to use to consolidate your data.
    1. We’ll leave the default, Sum, since we’re looking to summarize 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 LiveMocha.com 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!

SPG Platinum

Want Hotel Status and Upgrades? Just Ask.

OK first of all, the Starwood Preferred Guest (SPG) program is amazing. In fact I’m comfortable declaring that it’s my hands-down favorite and the best loyalty rewards program I’ve ever used to date. The reasons are many, but I’ll save extolling these virtues for a later post. Before I go on gushing and retell how I achieved Platinum status in nearly a third of the typical time required, let me first disclose that while it would be sweet, I did not receive any discounts, free nights, or any other cool stuff as a result of this post. I just happen to travel a lot, and I happen to stay mostly with SPG hotels, and they happen to rock.

Now back to the gushing. When I first learned I’d be traveling to Toronto every week I asked a few traveling buddies for hotel recommendations around downtown Toronto. Curiously my cohorts spent more time raving about SPG than about any particular hotel, so after the third or fourth SPG recommendation I signed up for an account and booked my first few weeks at the Sheraton Centre Toronto Hotel on Queen St.

Sheraton Centre Toronto Lobby

Grand lobby at the Sheraton Centre Toronto

It turns out this Sheraton is a popular hotel – when I first strolled through the lobby a check-in line stacked 15 people deep greeted me, along with a host of front desk reps hustling back and forth. As I waited I noticed a much shorter, faster moving line over at the SPG Gold & Platinum desk. This line, I decided, was where I needed to be, and I immediately began hatching schemes to get there.

I became Gold later that very night, not more than an hour after I checked-in for the very first time and well before I spent a single week at the Sheraton. Six weeks (and only 18 total nights) later I became Platinum. For reference it normally takes 10 stays or 25 nights to reach Gold and 25 stays or 50 nights for Platinum. So how did I get to Gold before staying a single night and Platinum after just 18 nights? I simply asked.

After I checked-in and settled into my room I jumped on to their super-convenient online chat and asked something to this effect:

me: hi there. Looks like I’ll be spending six months to a year in your hotels and was hoping you could upgrade my account to Platinum in advance?

SPG rep: Hello and welcome to SPG Online Chat, my name is so-and-so.

SPG rep: While I cannot upgrade your account directly to Platinum, I went ahead and upgraded you to Gold to welcome you to the SPG program.

SPG rep: I’ve also enrolled you in our SPG Platinum Challenge Promotion. If you stay 18 nights during a consecutive three-month period we will automatically upgrade you to Platinum.

That was literally all I did. At three nights per week it would have taken me over four months to achieve the 50 night minimum for Platinum. I hit it after just six weeks, and enjoyed Gold status perks along the way.

I’ve come to learn that other loyalty programs work similarly. If you have status with one hotel brand, other hotel brands will often “status-match” your account if you just ask. For example, let’s say you have Platinum status with SPG but Hilton is the only player at your destination. All you have to do is contact Hilton Honors customer service and ask if they’ll match your SPG status. Sometimes they’ll ask for proof of your existing premier status with the other program, but more often than not they’ll be happy for your business.

Rumor has it some airlines, in rare acts of benevolence, will also play the status-match game. I haven’t tried this personally (yet), but it doesn’t hurt to ask. Just asking bumped me into Gold status immediately; it also led me to the previously unknown Platinum Challenge promotion.

It’s all gravy from here.

Downtown Toronto in the Distance

Save at Airports: Bring Your Empty Water Bottle

If you find yourself trudging through airports every week, as I often do, I don’t doubt you’ve noticed that bottled water doesn’t sell for less than $3. Quite often even that is cheap. Nearly ever week since January I’ve wandered through terminals searching for bottled water, and nearly every week I shout (in my head) “Damn, $4.50!”

I’m sure this tip has already been revealed elsewhere, but it only recently occurred to me and I feel it’s worthy enough to repeat: bring your empty water bottle through airport security, then fill it up at water fountains. Since I usually fly twice a week this saves between $6 to $8 dollars each week just in bottled water, or at least $240 to $320 per year if I fly about 40 weeks between January and December. Is it a mountain of cash? No, but hey, it looks nice in my pocket and it’s better for the environment, so that’s like a double bonus.

Of course this won’t work for everyone: first of all, this tip assumes there’s a reliable supply of safe drinking water wherever you’re traveling. If you fall into this category, or aren’t sure, I’d say play it safe and spring for bottled water – Montezuma’s revenge is no joke. Fellow travelers have also reported that larger water bottles (one liter and above) are often confiscated at security even if they’re bone dry, so keep that in mind.

If we stick with the environmentally friendly theme, I recommend something reusable like a Klean Kanteen or something similar. If not, many hotels provide complementary water bottles that work just fine.

Toronto, eh?

In January I joined a software engineering team on a project in Toronto, Ontario, for the first time departing from my warm, familiar Excel-modeling blanket. Until boarding that inaugural flight, creating Excel models was my one and only jam, my professional bread and butter. Changing career paths is a daunting endeavor – there are many unknowns, many self-doubts and a lot of second-guessing. Fear of failure is a powerful, persuasive force.

Circa 1994.

Circa 1990.

I can remember wanting to learn a programming language from that first, magical time I played Prodigy Online checkers against an unknown opponent from an unknown place, over dialup, on my parents IBM Aptiva desktop.

Compared to today’s standards it was like we scrawled our moves on stone tablets and sent them back and forth by courier. But it was still so cool, and I wanted to learn how to do it myself.

Now almost twenty years later, I’m finally making it happen.

Toronto, my new weekday neighborhood.

Toronto, my new weekday neighborhood.

I’m doing real Java development for a real, paying client. I’m learning SQL, Oracle database tuning, JSP and Apache Ant. I’m exploring a unique, culturally diverse city with so many interesting foods to try I hardly know where to begin.

I joke that I’ve had more Indian food in the past few months than in my entire life (but I’m not actually joking).

It’s a message we’ve all heard a thousand times in a thousand ways: do what you love, follow your dreams, pursue your passion, and so on and so forth. The cruel reality is that most people, for circumstances both in and out of their control, will never practice any of these clichés. A free market economy prevents the masses from traveling the world as freelance writers.

Riding the Toronto Rocket.

Riding the Toronto Rocket.

But that doesn’t mean it’s impossible. My final tipping point came when I realized that the plunge I was about to take wasn’t permanent. Not by a long stretch. If it didn’t work out, if my worst fears came true and I failed spectacularly, I could always return to my familiar blanket.

The Toronto project ends this December; five months in, my fears of spectacular failure seem like a distant memory. What I’ll do or where I’ll go after that is an open-ended question, and that’s okay. It’s actually kind of intoxicating.

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 (a.k.a. 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.