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

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.
  All worksheets to merge should have the same layout.
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.
  The newly created “Merged Master” worksheet should have the same layout, but without any data.
Select cell B5 again. Navigate to Data → Consolidate.
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.
Under Reference click the small 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.
Click the small 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.
Repeat step six for March. Your Consolidate dialog box should now look like this:
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
Excel automatically summed the data from all three worksheets, sans formulas or VBA.

A few notes

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.
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.
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

2 Replies

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.

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:

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.
Create an immersive environment full of movies, music, books and other media in Spanish (see above).
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.
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!

Want Hotel Status and Upgrades? Just Ask.

Leave a reply

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.

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.

Save at Airports: Bring Your Empty Water Bottle

Leave a reply

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?

Leave a reply

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 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.

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.

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

Leave a reply

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:

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.

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.
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.
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.
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.