How to Beautify your Excel Reports 1

If you tired of making obsolete looking Dashboards/Reports then I have 6 awesome (and equally simple) make over tips for you.

We won’t go overboard in decking up our report until it looks horrible and meaning less but just enough to make it classy and beautiful! Let’s get started.. Shall we?

 

Tip #1 Remove all Clutter

Excel comes with it own User Interface, which is no doubt quite good but is becomes redundant once you are done building your Model, Report or the Dashboard. Let’s take a look at how can we strip the Excel’s Interface to its bare basics

Ribbons

Ribbons eat up a lot of your spreadsheet real estate (how much of space is visible in single screen shot). That could be important when you want to display the chart or dashboard in a single view without giving the user the hassle to scroll down

How to Beautify your Excel Reports 5

Beauty Tip – Hide it by pressing Ctrl + F1, Don’t worry you can get it back by pressing Ctrl + F1 once again. It works like a toggle and gives you more spreadsheet space

 

Grids

Gridlines form a criss-cross structure and help to follow along the data in a vertical or horizontal format. There are 3 drawbacks that I can think of straightaway

  1. Girdlines are by default applied to the entire sheet. So even if you need them on a particular set of data, they are going to be present in the entire sheet, which looks a lot cluttered
  2. You may require only horizontal or vertical gridlines but excel gives you both so again it adds to the clutter
  3. Gridlines are not too dark in color but dark enough to clutter your worksheet when data is added

How to Beautify your Excel Reports 6

Beauty Tip – Remove the Gridlines from the View Tab

Beauty Tip – Or when you need them draw thin faded off borders only on your data range

 

Page Tabs, Headings, Scroll bars and Formula Bar

When you really want to squeeze-in the last drop of space available, Excel allows you to even hide elements like Page Tabs, Headings, Scroll Bars and Formula Bar. Head over to hiding tips in Excel to read how to hide these

Beauty Tip – It is advisable to hide these elements when you need more working space. Just make sure that none of these are needed to view/edit or work with your Dashboard or Report. Rest is ok!

 

Tip #2 Hide extra Rows and Columns

I personally do this all the time. It is a very good habit to hide extra rows and columns that are not needed to be seen by the user. This way your report looks tightly packed and gives a customized User Interface by showing only what is needed.

Think of your favorite car where all the engineering is packed tightly under the hood! You don’t need to see it for a pleasurable driving experience! In a similar way our engineering is in the spreadsheet cells and must be hidden if not relevant for the user

How to Beautify your Excel Reports 7Click image to enlarge

In this HR Dashboard I have hidden most of the clutter including grids, page tabs and also the extra rows and columns

Beauty Tip – Hide the extra Rows and Column. Press Ctrl + 9 to hide Rows an Ctrl + 0 to hide Columns

 

Tip #3 Customized Scroll bars

By just removing the clutter you can go a long way in making your spreadsheet report look stunning but let go the extra mile. This tip is again a way in which you can optimize space.

Scrolling List

Think for a moment you have 30+ Departments which will take 30 rows  but what if you have only 5 or 10 rows to present all this data. Just make a customized scrolling list. This ensures that your data is tightly packed within the given set of Rows

 

Tip #4 Add Page Tab like Buttons

This is a step further to take your Dashboard User Interface to the next level. You can use simple shapes (like curved rectangles) to draw customized buttons for any particular actions. I have two examples here

1. Page Tab Like Buttons

You can make make page tab like buttons to indicate the user to move to the next sheet (or a different working area). These look more intuitive than the sheet tabs

How to Beautify your Excel Reports 4

Download Customized Page Tabs file here

 

2. Buttons to link your Macros

In the same way you can also create single buttons to run Macros, again they are a great way in which you can customize your Dashboards or Models. These buttons also invoke the user to take action. Here is how you do it

How to Beautify your Excel Reports 8

  1. Right Click on the Shape
  2. Choose Assign Macro Option / You can also choose Hyperlink to link it to some other Sheet or any other cell
  3. Select the Macro
  4. Ok and Done!

Take a look at how I have linked a short macro to a button, when clicked displays a welcome message

How to Beautify your Excel Reports 2

Download this File

 

Tip #5 Use Form Controls

Form Controls allow the user to talk to the Model or the Dashboard. Form Controls are simple to use, they look stunning and allow you to build interactivity in your Dashboard. To access form controls you would have to activate the Developer Tab in Excel

Here is an example of a Check Button From Control used in a Chart to build Interactivity

Check Button Chart

 

Tip #6 Smart Legends for Charts

Although Excel has come a long way in improving the look and feed of the chart legends, but there is a lot you can do and customize them your way. Take a look at the customized legends for this chart which also display the average number over 5 years

How to Beautify your Excel Reports 3

Download Customized Legends Chart

 

What are your Beauty Tips ?

I have been using these techniques to deck up my Reports and Dashboards. What are your favorite tips to add the oomph factor to your Dashboard. Please put down your views in the comments

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI