Small yet powerful
- Apr. 4, 2021
 | Example files with this article: |
European soccer teams and their revenues
Hi all ! Today I wanted to show you actually a small file, such that you can grasp everything that is created inside, yet a file with many powerful Excel tips and tricks. Honestly, it's jam-packed with a number of goodies that average users would not know or use in their day-to-day Excel work. Everyone will be able to learn a couple of tricks.
As an example, let's look at how many revenues the top 20 European soccer teams genereted during the 2019/20 season. I will explain you nearly everything I did to come up with what you see in the file, and what's more, what you might not see at first glance.
There will be soccer in the example, however, the same methods and logic can be used for the financial reports for your boss or any other important spreadsheets out there.
You can download the Excel file at the top. Below, after the text, the pictures will follow as a guidance for you.
Step 1: the raw data
As you can imagine, I started off with an empty Excel sheet and did the input of country names, soccer teams and generated revenue. As simple as possible. No formatting is needed at this point in time ! I entered the values in millions of euros but with 1 decimal digit, to match the level of detail with the provided data on the internet. Later on, I would hide the decimals anyway. You could leave column A blank to not have to start against the left side of the Excel sheet, you know, it could give a small claustrophobic effect ;-) For now, the totals by country are not important. You can enter a fake value of 100 or a simple SUM formula, it does not matter too much for now. Later on, and this is a spoiler alert !, we will add AGGREGATE formulas.
Step 2: formatting
I added the yellow formatting and dashed borders to the cells for country names. Then I added formatting for the soccer team names, meaning numberformatting to have the visual effect of "a list of teams". I only format one cell, usually the first near the top. Now we come to see the first powerful trick in Excel: styles. With a formatted cell selected, create a new style and give it a name. Like that, I created 6 custom styles:
- Style_Heading_Name: yellow with a dashed border, bold style and indented
- Style_Heading_Number: yellow without a border, bold style, no decimals
- Style_Heading_Percentage: a percentage numberformat notation, no decimals, italicized, between brackets
- Style_Line_Name: a dash is inserted as the first character. It's not part of the cell and you don't type it !
- Style_Line_Number: no background fill nor border, no decimals. Not many bells and whistles, it's just a number
- Style_Total_Number: green with a dashed border, bold style
Our task is now to apply the (relevant) styles to all the cells you need. You can select the cells and tick the style name in the Home tab or the ribbon and its Styles group, or just copy/paste the formatting of a cell with a style towards other cells. Good to know as an alternative method: if you double-click the Format Painter, you can apply it to several areas, one after the other.
The main advantages of using my approach:
- adaptability: it's now very easy to change the formatting of a specific style and all cells in the workbook with that style applied, will change instantly. I'm sure your boss at work might maybe not like that yellow fill colour: change to light green in a matter of seconds ! Or use Excel's built-in color palettes.
- maintainability: the file becomes much more maintainable and does not lead to clutter and a book as colourful as your toddler's books at home !
Step 3: insert empty lines
This is a very important step in order to make your file future-proof and increase its maintainability. We do this in light of the formulas that will be added to the subtotal cellss in step 4 below. For each block of team names within the same country, add an empty row above and below. Decrease the row height such that it does not distract the view of the users. In the formulas, we will extend the range of cells to 1 line above the "revenue data cells" and also 1 line below the "revenue data cells". Like that, inserting an empty row makes the ranges extract and we do not need to change our formulas. Many Excel users would insert a row below the last soccer team, well, even in that case we are covered since the formulas capture that too.
As said, the main advantages of this approach:
- maintainability: you will typically not need to change any formula down the road if you apply this trick. Ranges for formulas will extend and contract automatically.
Step 4: formulas to sum
We are working in Excel so will can/will also add formulas. Formulas to sum up revenues to country totals, for instance. How does the UK compare to Spain ? Yes, a simple SUM formula will do but remember that your spreadsheet might be much more complex in terms of groups and levels: you could apply my logic to a 5-level nested hierarchy of data. Think about a detailed financial chart of accounts. Let me introduce you to the AGGREGATE function in Excel: a versatile function to, well, aggregate underlying data. Sum is only 1 possible outcome, you could switch to average, maximum, minimum, counts, ... with little effort and without changing your formulas to AVERAGE, MAX, MIN, COUNT, etc. Did I say it is a very powerful and flexible function ?
Please have a look at the functions in the yellow cells. For more information on that function, please refer to my earlier article on the AGGREGATE function and SUBTOTAL functions in Excel.
To exploit the flexibility, I did not hardcode 2 arguments of the function. Instead I used 2 named ranges, again an uplift for those of you that have never used this piece of functionality in Excel. By defining a name centrally, and referring to it in your formulas, you can play around with your file. In a whim, you change sums to averages or counts or whatever.
It goes to show that again the main advantages are:
- adaptability: change formulas in seconds, for the entire spreadsheet.
- impress your coworkers and bosses
Step 5: the grand total
Now we have reached the point where you will again leverage the fact that you went the extra mile in step 4. You could have created simple SUM formulas over the relevant ranges and it would have worked. You will understand my choice for the AGGREGATE formula even more: the grand total over all soccer teams will also be created with a AGGREGATE formula ! In doing so the AGGREGATE formula, even if applied on ALL lines with revenues, will happily skip cells within the range that contain an AGGREGATE formula themselves ! This means that we do not need to worry about double countings. The grand total, applied over all cells containing data input and subtotals, will only include the raw data. How good is that ? No more this type of formulas: =D2 + D14 + D22 + D31 + D40. Because you know how it goes: a coworker adds an additional country the next season and forgets to include in the grand total. Darn ! Now we are covered.
We will also give the grand total cell a name: total. This name will come back later on.
Advantages:
- maintainability
- no more duh-moments in front of the boss because of forgetting the new group of data or accounts in the financial statements.
- impress your coworkers and bosses
Step 6: prepare the setup for a visual
A picture says more than 1,000 words, we all know that. Therefore, let's add a chart that reflects the data. We organized our data on the left hand side for easy input and presentation (on a PowerPoint slide for instance), but honestly, this does not make it easy to chart. We need to rework the data and prepare for our chart.
Come to introduce you to dynamic array formulas. They are pretty new in Excel and I'm sure I covered them a few times before on my blog. You will need a relatively recent Excel version to be able to use them. Please have a look at the cells in the range T5:W5. They will expand and contract depending on the number of results they need to show.
Thinking 1 step ahead, when we will add the chart and refer to the cells in this area (that could be hidden), we will also need a named range to capture the dynamic part. The problem is that the chart series are currently not able to chart based on dynamic ranges. However, they can chart based on named ranges, and, named ranges can capture dynamic ranges ! Therefore, a small sidestep allows us to draw fully dynamic charts. I like the custom labels and make information available from just looking at the chart.
Let's call the dynamic ranges:
- Percentages: it refers to Soccer!V5#, which is currently V5:V9
- Labels: it refers to Soccer!W5#, which is currently W5:W9
Step 7: add a visual
For this application I prefer the use of a treemap chart. It's built-in and there is no need to customize things. We will use the named ranges created in the previous step to chart the data and labels. You can read the treemap from left to right, and top to bottom.
Note the yellow rounded rectangle in the top-right corner of the chart. It's simply a formatted shape. When we click on the shape, then click in the Excel formula bar, we can type: =Total. Yes, this is our named range we created.
The advantages of the visual to me, include:
- Appeal: the chart is not difficult to interpret and read. The values are ordered large to small. It's looking good. The labels tell me the country name, the value in relative terms and absolute terms, nicely formatted.
- Relative proportions are clearer than with only data in a table.
- Evolution of absolute values over time: for a 5-season overview, you would typically create 5 such charts but also think about what represents 100%. The grand total can change over seasons so a visual clue to bring this to your audience is needed.
Step 8: the date of last update
If this is a file that you would update regularly, like in my case the file that is at the basis of this idea, then it makes sense to introduce the last modified date within the file. I did this in the bottom-right corner, cell Q44. Again a trick: you don't need to enter the date yourself, the shortcut key is Ctrl + ;. Should you want to have a static version of current date and time, you can do this very quickly as follows:
- Select cell Q44
- Press: Ctrl + ;
- Type a space
- Press: Ctrl + Shift + ;
- Press Enter
Step 9: other ideas
This has been a longer article and I would like to close off here with a couple of thoughts for you.
- You could use the grouping feature in Excel to group rows, and collapse/expand individual or all groups - depending on your audience.
- Make sure the chart still responds by plotting the data, since there is a setting to not plot hidden rows/columns.
- While you are looking at the properties of the chart, have a look at other customizations. It can turn your chart/file into an even more visual experience, but don't overdo it.
- You could write a small VBA macro to duplicate the current row selected. I did this in my Personal.xlsb macro file. It's very useful and saves me time.
I sincerely hope that you learnt a number of new things and that you will apply them in your spreadsheets !
