Leave a Reply

Your email address will not be published.

How to Hack Excel — and Add Totals to the Tops of Stacked Column Charts

Updated on: Jun 20th, 2022
Data Visualization in Excel
, , , , ,

“But you can’t do that in [Excel / Tableau / Numbers / Google Sheets / insert your own…]!”

Well, maybe you could.

When you work with data, you usually use the same tools over and over. You become familiar with them. You know the ins and outs, and you work faster because you remember the shortcuts and hidden menus.

Even with all your know-how, you can still come up against a roadblock, unable to do what you need.  How about taking what you know and use it…but differently. Hack it.

Hack? What? 

What hacking am I talking about? Not any of the Merriam-Webster definitions (think literally beating around the bush 😉). This hacking is more like the idea behind ikeahackers – taking a product and using it in a different way or for a different purpose than the original creator intended. Psst.. It doesn’t have to be ikea. You can take 2 giant foam cushions, and turn them into a side table.

What Do You Want?

Microsoft Excel added a map to its chart types. Great!

But what if you want to create a hex map?

A hex map is a map that represents an area (a US state for example) as a single hexagon, in the approximate position it should be. This eliminates problems that come up because of the different sizes of states. In the US it is especially helpful when including Alaska and Hawaii since they are both positioned far away from the contiguous US states.

A hex map would have equal representation for all the US states. No state would be too small (hello Rhode Island) or too big (looking at you, Alaska).

Get from Where You Are…

Ok, how to do that? Does Excel map include a hex map option? Nope.

Well, it looks like it’s organized in rows and columns.

Could we use the Excel cell structure for that? Maybe, but how will we get the data to the cells?

Well, a scatter plot could be the basis of that but you would need to build the whole map somehow.

How?

…to Where You Need to Go!

Other people (like David Napoli and John Schwabish) who understand Excel quite well sat down and wrote the process.

They did not use code. They did not use a special hidden feature.

They hacked Excel by using a standard feature such as the scatter plot.

It was done by understanding how scatter plots are built.

Scatter plots place your data in certain positions, based on the x,y coordinates provided.

You could use that to prepare the data for a scatter plot that looks like the US, then add the data, add hexagons, and so on. Since someone already did that, you can simply read up on their work.

Keep it Simple, S…Sweetheart

There are many more simple hacks you can use in Excel. 

A useful one I recently used is showing the total in a stacked bar or column chart.

A stacked bar chart has the option to display the data labels of each series but not the total.

Consider this example displaying the number of projects in different divisions.

Aside from the clutter, if users want to know the total per quarter, I don’t expect them to start adding up the numbers themselves.

But it’s not an option!

Wait, Excel displays the data labels of each series.

Why not create a series summing up quarterly projects and then just showing the data labels?

Did it. 


Then added it to the chart where it was displayed in all its glory.

Once I choose “No Fill” and “No Line” and add the data labels, things look different.

If you don’t like to think of all those stacked pieces jutting out of the chart, or you want the vertical axis to be determined automatically there’s another thing you can do.

Edit the chart type and choose the Grand Total series to be something other than a stacked column.

Same result, one more step, but a step that can keep the chart looking this way even if the data changes and your axis needs to change as well.

Gimme More…

I also have to mention one of Ann’s many hacks, because it is just so useful and so simple – using the Webdings font as a dynamic graphic component.

Read it and congratulate yourself on learning something small but powerful.

Now What?

Stop.

Stop and think. 

Stop and find time to invest in your professional development and think on the tools you are using. Break away from the “walls” of the examples you’ve learned from and truly think about features you know.  Try to imagine them as simple, skeletal definitions of input-output. “I have data and Excel places it on a grid.” “If there is space in the data, there is space in the table.” “My chart doesn’t have to show everything included in it, some of it can just be structure.”

Stop before you create a chart, table or report and think about what you want to do, without restricting yourself to whatever tool you need to use. Sketch it, describe it in words, use whatever helps you have it defined in your mind. Only after that think of your implementation, think of your own view of features in your tools and plan on how to use them. 

Now, go forth and be technically creative!

Connect with Anat Zohar

LinkedIn: https://www.linkedin.com/in/anatzohar/

More about Anat Zohar
As an experienced data professional, Anat Zohar knows what it takes to distill company data into informed decisions that lead to profitable outcomes. Anat has discovered that the key to making a company’s data useful is combining business understanding with the know-how to wrangle data into actionable insights that lead to effective business decisions and actions. One of her colleagues commented: “During the time I worked with Anat, I found her to be a knowledgeable individual with a great set of skills in data analytics. She was excellent in explorative data analysis and solution architect. She was also very adaptive to new technology and tool and was able to navigate amongst technical challenges and always find a solution in the end. It was such a pleasure to work with her.” Her experience has shown me that companies recognize the importance of their data but do not have the ability to translate this data into meaningful insights that will serve as the basis of important decisions that are vital to their success. The primary service Anat offers is empowering companies to make better use of their data so they can make informed decisions that lead to increased efficiencies and profitability. Her ideal clients recognize there are obstacles on their data journey and are willing to accept my guidance and support in order to achieve success.

Leave a Reply

Your email address will not be published.

You Might Like

Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.

Enroll

Need practical how-to tips? In this course, you’ll learn how to make great graphs inside software you already have. Includes beginner, intermediate, and advanced tutorials for making charts from start to finish.

Enroll

Subscribe

Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.