The worst Excel user ever?

At the start of my career, I worked with someone who used to moan ALL the time about how terrible Excel was. After weeks of complaints, I decided to see if I could help, just to stop the complaining. I sat down next to him and quickly discovered he was using the 'shapes and lines' functionality of Excel (the drawing tools) to hand-draw the charts as pictures, not realising that Excel would plot charts for you.

Before being too harsh on my former colleague, I have to admit that I had (and have) my own Excel ignorance issues. For many years I struggled with having precisely aligned charts, text boxes and objects on my Excel dashboards. Then I discovered a very simple and easy tip that can dramatically improve the professionalism of your dashboards. That tip is what I want to share today. Try not to laugh at my years of ignorance....

Snapping your chart areas to a cell grid

Step 1: On your 'dashboard' tab decide on the size and shape of the grid to use for your dashboard layout.

Step 2:  Set your row heights and column widths to enable this (right mouse click on the column or row header to change the size). He is an example…

Setting a column width

Step 3: Copy your 'source' chart (the original) onto your 'dashboard' tab. (You can create charts straight onto the dashboard, but I'd recommend separating data, charts, dashboards and variables onto their own tabs for all but the simplest of dashboards).

Step 4: Select your chart - white 'handles' will appear at each corner of the chart. With the chart selected, hold down the 'Alt' key (Option key on Mac) and then drag the corner (if it doesn't work the first time, make sure you held down the Alt/Option key first. You will find the chart then snaps to the cell grid underneath).

Selecting the Excel chart object

Using this simple, systematic, way to lay out your charts you can build up impressive dashboards rapidly. Here's an example of a more complex one, built using the Brilliant Excel Dashboards Kit...

Cat Herding Society sample dashboard

Making sure you aren't a candidate for 'Worst Excel user ever'

My general advice is...

'If you find yourself doing tedious, repetitive, tasks in Excel then stop! Do some research. There's a very good chance that there's a much better way of dealing with your challenge, that you just don't know about'.

It's almost fashionable to knock Excel, but it's a tool with some surprising depths, but sometimes you just need to do some digging to find those depths.

%d bloggers like this: