Learned how to creat incell bar charts in excel 2007, and it’s so damn cool!!!

  1. First we will insert a column next to the total sales column and call it “last 12 months”. We will use this column to fill up the in-cell bar graph for the last 12 month sales.
  2. Next we need to normalize the sales values for each month to a value between 0 to 9. I have used linear normalization, ie the maximum value across the 12 months would be 9 and everything else will be normalized according this. The formula for one month looked something like this: =ROUND(jan_sales/MAX(all_sales)*9,0). Remember, we need to round the data make it one digit instead of a decimal.
  3. Once we have normalized values for each of 12 sales figures for each row, we just need to concatenate them to create a 12 character long string of numbers and place this values in our newly inserted column. P.S.: =concatenate(C2,…..J2) didnt find a quick way to do this….><
  4. Finally, we will change the font of this column to “bargraph” (just select all the cells under the “last 12 months” and change the font)

P.S.: I also found another way to do this, but that seems a bit dumb comparing to the inserting insell bar way: select the date from each mon, and simply click bar data..change the format of it, and that’ll give us a similar pic. Do it several times repeatedly based on the product data, and we will also have bar chart pics.

Reference: http://chandoo.org/wp/2008/05/13/creating-in-cell-bar-charts-histograms-in-excel/

Advertisements