Sunday, November 27, 2011

Comparison Charts With Excel

Today we are going to learn how to make a chart which compares two or more data in a table. It is very useful when we want to compare between two values very easily within no time. As you will agree that comparing data with a graph is far more better than digging into a lot of data in a table. So lets get started. this is what we are going to create.

Follow these easy step by step method to create the chart as in the above image. Lets say we want to compare a list of items A to E for number of units in stock, purchased and sold.
Step 1
Create a sample data as below.

Step 2
Go to Insert > Charts > Column and then select Clustered Column chart from 2D Column.

Step 3
Select Layout 5 from Chart Layouts in Chart tools in Design tab.

Step 4
Right click on chart area and click on Select Data.

Step 5
In the Select Data source window click on Add.

Select the title "In Stock" for Series Name and Data below In Stock title for Series Values and click OK.

Notice a series In Stock is added in the Legend Entries (Series) list box. Now click on Edit button.

Select Item names A to E for Axis label range and click OK.

Similarly Add Two more series Purchased and Sold  as illustrated above for Purchase and Sold units.

Step 6
After completing above five steps you should get a graph as shown in the image.

Right click on Axis Title and choose Edit Text. Delete the text and write "Number of Units". Select the text and change the font size to 12 and the result is. . . 

Leave me a comment if you like this post.

Friday, November 25, 2011

Dynamic Chart

Today we are going to make our charts dynamic in nature. By dynamic I mean that the chart will update automatically on adding new data to the table. As I always say, just follow these simple steps to make your charts update as you enter new data. You can use this tutorial to create resource chart also.

Step1:- First of all open excel  and create sample data as in the image below.

 Step 2:- Go to Formula tab in excel and click on Define Names. A pop-up window opens up.

              In Name write "Day".
              In Refer to write " =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)".
              Click OK.

              Again click on Define Names.
              In Name write "Resource".
              In Refer to write "=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)".
              Now save your book. I saved it as "Dynamic.xls".

Step 3:- Now go to Insert > Charts > Column Chart and select Clustered ColumnType

Step 4:-  Go to Chart Layouts and Select Layout 8 from the layouts.  

Step 5:- Create the chart as in my previous tutorial Create a Simple Chart in Excel.
              While Selecting data do follow these steps

              In the series values write "=Dynamic.xls!Resoures". 
              Remember to replace Dynamic.xls with your own file name.

              In the Axis Label Range write "=Dynamic.xls!Day"
Note:- Confusion regarding file extension as pointed out by my friend.
For Excel 2003 "Your_File_Name.xls".
For Excel 2007 "Your_File_Name.xlsx".
Above example is as per 2003 format. 

Step 6:- Change Chart style to Style 16 and it should look like this.

Now try adding new data to your table, you will notice that it automatically updates in your graph. Leave a comment if it works.

Referred Links
Dynamic Charts (Peltier Tech Blog)

Wednesday, November 23, 2011

Add Data Labels to Excel Charts

Last time we created a simple stacked column charts in 6 easy steps. Today I am going to teach you how to add data labels to your charts. This is what we are trying to achieve.

Earlier you learned how to create simple stacked column chart. Now to Add Data Labels to the chart follow these simple steps.
Step 1:- Open the chart which you have prepared. Now right click on the chart and click on Add Data Labels.

Now your chart should appear like the image at the bottom.

Step 2:- Again Right Click on the chart and then Click on Format Data Labels. A pop-up window will appear. In the pop-up window choose Label Option and check Value and Series Name check box and also click on the Inside Base radio button.

Now choose Alignment in the Format Data Labels Options, then from the drop down list of Text direction choose Rotate all text 270 degree.

There you go. You just added data labels to your chart and the final result is this.

Tuesday, November 22, 2011

Create a Simple Chart in Excel

Today we are going to create a simple Column chart in excel showing sales per year.

Follow these simple steps to create a simple chart in excel. I am using excel 2007 for this example.
Step 1:- Create a simple table in excel as shown in the figure below.

Step 2:- First of all click on an empty cell, then go to Insert > Charts > Column and then click on Stacked Column.

Step 3:- An empty Chart Area will be created. Right click on the Chart Area and then click on Select Data.

Step 4:- Select Data Source dialog box will open. Now click on the Add button as in the image below.

Another pop up dialog box will appear. In the Series name text box select the title sales and in the Series values box click on the first data under sales and then click and drag it up to the last entry of the table.
Click on the first number under Sales hold Shift and then click on the last data.

And then click OK. 

Step 5:- Now the earlier pop up window will reappear but a Series named Sales will appear on the Left hand side. Now click on the Edit button on the right hand side of the window.

One more window will appear. This time select all the years under the title Years and click OK.

Step 6:- Now again click OK on the pop-up window and the final chart will look like this.

HostGator Promos