Format a table using formulas only

First of all, I converted the data to an Excel table. There are many benefits to Excel tables two of them being that they "know" how big they are, so when you add data to them the new data becomes part of the table and formulas, pivot table and charts built on the tables adjust automatically to include exactly the right amount of data. Tables also copy down formulas, formats and validations automatically.

I had to add a helper column so each record would be unique. The last column contains the formula =Row().

Then I built a pivot table based on the table. I had to do some formatting with the pivot table like display it in tabular format.

You can hide the column with the Row Number. The row number forces the pivot table to treat each line as a unique record. Without it, you would get one line for each Name and the total for that name.

For the fun is here another option:

Formula:

Please Login or Register  to view this content.