You are here: Home / Charts

How to summarize data using a pivot table

October 7, 2008 By admin

What is a pivot table?

Pivot table is a feature of Excel that takes a long list of data, summarizes it into a shorter list that is more easy to analyze and assimilate.

The best way to explain how to create a pivot table is by using an example.

In the image below you see a list of products, salesmen and total sales for a product / salesman.

To create a pivot to summarize the total sales by product, you perform the following steps:

1. Prepare your data table and make sure you have columns headings.

2. Select the data range or simply click on a cell within the range.

3. In the data menu click Pivot Table and then follow the wizard.

4. When you get the pivot table field list:

  1. Drop the “Product” field in the area where its says “Drop row fields here”.
  2. Drop the “Total Sales” field in the area where its says “Drop data items here”. You will get the pivot table as shown here:

5. You can then plot a chart using the pivot table instead of your detailed data.

Applies to Excel 2003

Filed Under: Charts, Data Tagged With: Data, Pivot Table

How to update existing chart, Part 3: Chart Options

July 13, 2008 By admin

As I mentioned in part1 and part 2 of this series you can update an existing chart to change some of its properties without having to recreate the chart from scratch. In this post we will see how to update the chart options:

  1. Click on the chart.
  2. On the top menu Click Chart then click Chart Options.

  3. The Chart Options window will open.

chartoptions2

There are six tabs in Chart Options window.

1. Titles

You can change the chart main title, category (X) axis title or Value (Y) axis title.

2. Axes

Here you specify what you want to see as labels in the category (X) axis or the Value (Y) axis.

3. Gridlines

Specify whether you want gridlines in the category (X) axis or the Value (Y) axis.

4. Legend

Specify where you want the legend to appear relative to the chart.

5. Data Labels

Data labels allow you to put labels on your data (the columns in our example). You can chose to put series names, category names or values.

6. Data Table

Data table gives you the option to show your actual data below the chart.

Please note that some of these options are not available for certain types of charts.

Filed Under: Charts Tagged With: Chart Options, Charts

How to update an existing chart, Part 2: Data Series

July 9, 2008 By admin

As I mentioned in part 1 of this series you can update an existing chart to change some of its properties without having to recreate the chart from scratch. In this post we will see how to update the data series:

1. Click on the chart.

2. On the top menu Click Chart, then click Source Data.

\

3. The Source Data window will open.

\

4. Click on the Series tab.

5. On the left you will find a list of your series. When you click on an individual series, its name and value will show on the right. You can override those with actual values or with cell references.

6. To change the name of a series for example, click on the Name textbox. Either right the name directly, or to reference a cell go to your worksheet and select the desired cell.

7. Here you can also change the Category(X) axis labels. Click on the textbox then type the names of the labels separated by commas, or you can refer to worksheet cells as I mentioned earlier.

Applies to: Excel 2003

Filed Under: Charts Tagged With: Charts, Data Series

How to update an existing chart, Part 1: Data Range

July 6, 2008 By admin

If you have an existing chart and you want to change the source data of that chart, by expanding it for example, then instead of recreating the chart you can just update it as follows:

1. Click on the chart.

2. On the top menu Click Chart, then click Source Data.

\

3. The Source Data window will open.

\

4. Click on the Data Range tab.

5. In the Data Range textbox (below the chart) highlight the current data range.

6. Go to your worksheet and select your data range.

7. The new data range will be reflected on the textbox. And the chart will be updated accordingly.

Applies to: Excel 2003

Filed Under: Charts Tagged With: Charts, Data Range

A primer on chart terminology

June 25, 2008 By admin

If a picture worth a thousand words as the saying goes then a chart could be a good proof of this.

Excel has a chart wizard that provides the user with a variety of chart types and many options to customize those charts. So in this post I will not get into the details of how to create a chart but will try to clarify some of the jargons related to charts, and I will take the Column Chart as an example.

Before plotting a chart you have to arrange your data in a way suitable for the chart type that you are going to use.

Chart data consist of one or more series (a group of data). The series could be in a row or in a column. In case of the column chart, each series appear in the chart as a column, and also appear as a legend or caption to identify the relevant group of data.

The chart itself has two axis; The X-axis (horizontal line) which is called the Category and the Y-axis (vertical line) which is called the Value. Along the X-axis you will find the category name and category labels. Along the Y-axis you will find the value name. In our example if you put headings for the columns then they will be reflected in the legends of the chart.

There are many other chart options that can be customized like gridlines, data labels and data table which can be accessed by right-clicking on the chart.

Chart Jargon

Applies to: Excel 2003

Filed Under: Charts Tagged With: Charts