# Show Top and Bottom Results in a Chart-Table

This Excel table shows the top- and bottom-five results, with charts that show the most recent three-month trends. And it all updates automatically.

3490

The workbook that supports the following figure does a lot of work!

First, it uses Power Query to download the weekly unemployment claims and the number of people covered by unemployment insurance, by state, since January 2019.

Then, for each of the most-recent three months, it calculates the total initial claims per month as a ratio of the average number of people covered during the month.

Then it sorts the data automatically. To do so, a column in one table uses RANK.EQ to rank each state by the current-month’s total. (It didn’t matter whether I used RANK, RANK.EQ, or RANK.AVG, because when we sort by formula, we never can have duplicate values to be ranked.)

Finally, a second table uses XLOOKUP to return an ordered list of the five states in the top and the bottom of the rankings. And finally, the formulas in this table use XLOOKUP to return the best and worst values. (INDEXMATCH Could have done the same thing.)

In this table figure, each column chart has TWO series, not just the one series you see for each state. The hidden series is a line that plots two points: the maximum and minimum values for all data shown. That way, because each chart plots the same max and min values, each chart has the same values in its Y axis.

From a management perspective, internal data tells you what happened. But external data can help to tell you why it happened, what might happen next, and what you might be able to do about it…as this figure suggests.

Specifically, people living in the states on the left side of this figure probably are better able to buy consumer goods than are the people on the right…ceteris paribus.

On the other hand, if you sell red ink…

Previous articleHow to Remove a Speed Bump From Your Excel Charts
Early in my career, I worked nearly 20 years as the CFO of turnarounds and startups. But I eventually got burned out fighting continual struggles with cash flow. That's when I started to write about Lotus 1-2-3, the spreadsheet software that I'd been using most of the time during the CFO days. When Excel was about to be introduced for the PC, one of my magazine editors set up a meeting for me to see the product, talk with the developers, and write a cover story about Excel. So I used the first version of Excel before it was launched. And I had also used the first version of VisiCalc before it was launched. And then,