Short Post but it's still Excel Magic
When
I reread this post this morning, I thought, hmm, I can’t see even a hint of the
magic I referred to back in August 2021. So I went to find the Excel file … GONE!
It took me an hour to find the data file on which the post is based but I could
still not see the magic in the image below!
One
of the problems I face with my work is that I love to work with real data and
this post was based on the Fortune 500 database covering the years 2012 – 2021.
The problem is that the data are not mine so I cannot share them. Hence, no file
posted here and I couldn’t find the file on my own system in spite of searching
for key words, file names, file extensions, date modified and so on.
Still,
I found the source data, at least. So, I set to work and did three things:
The Three Things I did
I
corrected a bug in my Power Query table that I hadn’t noticed before: in one
Query I called a column Market Value at $m and in all other versions of the Query
I, called that column Market Value $m. That meant appending the Queries did not work properly.
I
recreated the table shown in the screenshot: still not seeing the magic!
I
created a Pivot Table to enhance the quality and quantity of my analysis of
these companies in the Fortune 500 over a several year period.
Anyway, as part of my concentrations, I created a spreadsheet in which I developed the following table:
The formulas I used
The
following screenshot shows the formula I used in the Revenue $m column:
remembering that I was getting my data from a Query table:
In my original version of this post, I pointed out, here are some clues as to what I did:
That
table uses these functions: MAXIFS(), MINIFS(), AVERAGEIFS() as well as Data
Validation and Range Names …
I created a Pivot Table
For
this revised version of this post, I then created the following Pivot Table:
Notice, I added sparklines once I had completed the Pivot Table.
One
issue here is that even though I was only calling fiver years’ worth of data
from my ten year database, there were still 656 companies in there, not just the
500. So, when trying to use the Pivot Table to return the results of any one
table, even with a Slicer, it can take a long time to find the company you
need.
Why I Created an Initial Column
To
solve that problem, I went back to my 2012 – 2021 Query and set up an Initial
column: showing the first letter of the company name in each row. That simple
device cuts down company name search by a LONG time on average. The following
screenshot emphasises that point:
Frequency Distribution
Just
for no reason except that it extends the analysis, take a look at the following
column graph:
Summary
Even
though I cannot remember what it is that I did that was magic, this analysis of
the Fortune 500 for the years 2017 – 2021 is rich in terms of the functions and
formulas used, including Power Query. Adding a Pivot Table is not so difficult
these days and that analysis adds a significant and new dimension to the first
table in this post.
Duncan
Williamson
28th
August 2021
Revised
1st March 2022
No comments:
Post a Comment