Excel has one interesting trick for work with data. Ira has already mentioned about it before, but quite briefly and long time ago — so lets refresh these archives of history.
Lets say, you have a table with multiple rows. You also have a column in which all these lines are compared. For example: sales for each store in retail chain, revenue by product category, circulation figures, or other. The main thing is that there is no reason to build a graph or so, but you want to compare the values in the table. For example, we might want to see which Volvo models, on auto.ru, are exhibited most actively.
Data was dragged into Excel:
- Data that we need for the graph is inside the cells.
What should we do? You can build the chart directly in the cell. Excel 2010 can make micro-charts (sparklines), but that is not exactly what we want to do here. We do not need to build momentum in the cell, and compare the line. In this paper we take the formula REPT («Repeat» in the Russian version).
Итак, пишем в строке формулы для ячейки А3 магический код:
So, type in the magic code in the formula bar for A3 cell:
= REPT («I»; A2), A2 is the cell with the number of cars
Result: The cell is filled with «I»characters in amount equal to the figure in each cell.
- In result of this operation we get the number of items in each cell
In the end, just change the width of the column, so you can see the entire length of the line.
Why do we do all this? So we can leave the table clean and neat and, without graphs, make the table readable.
As most savvy of you might noted already that it might not work with large numbers. But no, lets create an intermediate column, in which we can divide thousands or millions of bits and let the values not exceed 50-100.
- The final graph of the cell
That’s not all yet. We go further ahead — the same way we can visualize data in positive and negative values. Negative data will be in one column, and positive in another. Formula controls what to display.
Example: Statistics on changes of Volvo’s proposals (not real figures — just for an example). Here is how it looks like:
- P&L Statement in Excel
How?
1. Building the absolute value of each line, as REPT knows nothing about negative numbers
2. Building the REPT formulas in two columns, so that one column has only positive numbers, and negative has the other
3. Форматируем, и приводим в порядок. Works!
3. Format it. Now it works!
By the way, such exercises are great simulators for logic. Love it!
