Create An Improvised Bar Chart In Excel

Tue 4th January 2011

Although Excel can display data in all manner of charts, this simple formula will convert numbers into an improvised bar chart that appears in a column, using the letter I to mark the measurements.

At the firm of T Pegg & Son, Camping Goods Warehouse, the eight employees looked up from their teacups as the boss marched into the canteen, evidently not best pleased. He cleared his throat to get their attention, and then told them all to meet in his office immediately after lunch. They slurped their tea and dunked their biscuits wondering what on earth could be wrong. Gingerly they gradually filtered into the office.

It had been three months since the boss had installed CCTV around the premises and while studying the footage he noticed that staff had sometimes been rude to customers. He decided to sift through the whole three-month archive to see how prevalent this rudeness was and he was extremely disappointed at the results. He assured the staff that the CCTV had been installed for security purposes and not as a snooping device, but added that the firm's reputation must not be tarnished.

At his instruction the staff shuffled up and gathered around the computer screen. On it they saw an Excel chart with all their names in column A and the number of times they had been rude to customers in column B. The staff craned their necks in an attempt to make sense of the figures but it was all a jumble to them. What could the boss do to make the data more understandable?

He could sort the data to make a 'league table', but the ones at the back would still not be able to see the figures.

He could convert the data into a chart - if he knew how.

Or he could do this:

(If you would like to try this for yourself put the label Name in column A and No: in column B. Then type eight names into A and eight random numbers, say between 10 and 30 with some highs and lows, into B. Finally drag column C out to about the 25.00 mark and you are ready to go.)

In C2 type the following simple formula: =REPT("I",B2) and press Enter. You will see that, whatever figure is in B2, that's how many times the capital I appears in C2. Now select C2 again and click on the small dark square at the bottom right of the cell. Drag this down until you are level with the last name on the list (C9) and release. You now have what resembles a bar chart on which the worst offenders and the comparative angels can clearly be seen.

To break this formula down 'REPT' simply means repeat and the latter I is chosen only because it makes the finished result look like a marked measure such as you would see on the edge of a ruler - you could insert any letter in there. B2 is obviously the cell that the repeat refers to.

Now I know that there are more practical ways of showing data than this. But it is never a bad thing to have a few extra tricks up your sleeve and this particular one is at least functional. It also serves as a good demonstration of how the functions of an application can be ingeniously adapted to meet an unexpected need.

Author is a freelance copywriter. For more information on excel training london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1361-create-improvised-bar-chart-in-excel.html