Excel Lessons Online

You may know how to create a simple chart in excel but what if you could create an even more complex and attracting looking chart like a 3-D column chart from an Excel advanced feature like Pivot Tables? Well, you’re in luck, in this lesson, I will show you step by step how to create a pivot table from raw data and from that pivot table how to create a cool looking 3-D column chart. I promise the final product looks impressive, but you’ll be more surprised at how easy it is to do. With this skill under your belt, you will be a star in front of your marketing and finance colleagues.

For this lesson, we’ll use raw data from a fictitious company called Breeze Distributors. Breeze Distributors sells and distributes beverage products to independent stores within the South Florida area. The raw data that we’ll use contains the top four beverage products sold to three major cities within the county and the sales figures for the months of October 2006 to December 2006.

Below is the raw data that we’ll be using:

Please Note that you’ll need Microsoft Excel 2003 to complete this lesson.

Let’s get started!

1. Go to Start>Programs>Microsoft Excel 2003 to open the application and then go to File>New to open a new workbook. Before going further, go to File>Save As and save your new document so you don’t lose any information.

2. Now enter all the raw data provided at the beginning of the lesson into the spreadsheet. You should have four columns labeled “Month, Product, City and Sales” and their appropriate data below.

3. Now to create a pivot table, go to Data>Pivot Table and PivotChart Report and select it.

4. Now, in this pop-up window, you need to specify where you are getting the raw data, so select the first option, “Microsoft Office Excel list or database,” under the question “Where is the data that you want to analyze?.” Next, select the first option, “Pivot Table,” under the second question “What kind of report do you want to create?” and click Next.

5. Here you need to select all the data to create the pivot table, so select all the raw data area as seen in the image below, and click Next.

6. Next, select the first option “New Worksheet” so that a new pivot table is created and saved in a separate worksheet within the current workbook, and click Finish.

7. Now you see the layout of the pivot table ready for you to arrange. Go to the Pivot Table Field List pop-up window and click on “Month” and drag it to the “Drop Row Fields Here” next, click on “Product” and drag it to the “Drop Column Fields Here” and finally, click on “Sales” and drag it to “Drop Data Items Here.”

8. You just created a table that shows how much each product made per month, but to really appreciate this relationship we’re going to create a 3-D column chart.

9. Before we create the chart, we need to change the sales figures to currency format, so the following screen shots will show you how to change a numeric format in a field within a pivot table. First, click on one of the sales figures, for example, the cell right under Caribbean Juice and next to December 2006 and right click it, and go down to “Field Settings” and select it.

10. Click on the “Number” button within the PivotTable Field pop-up menu.

11. The pop-up window called “Format Cells” appears, under “Category,” select “Currency.” To the right, decrease the decimal points to zero and right under Negative Numbers, select “$1,234” and click OK.

12. Click OK again in the Pivot Table Field pop-up menu.

13. Now, you’ll see that all the sales figures appear in currency format with the dollar sign ($).

14. You will notice that the pivot table has a floating format menu called “PivotTable,” so click on the “Chart Wizard” image and a chart will be automatically created showing the current pivot table information.

15. Now, we’ll change the view, so go to Chart>Chat Type to access a series of options and change the current selection.

16. The pop up window called “Chart Type” appears, select the “Column” chart type and then select the last option under “Chart sub-types” located to the right of the pop up window – as seen in the image below and click OK.

17. Now you have created a neat looking 3-D column chart from the pivot table data. You can go to File>Print Preview to see a preview of the how the chart will print. Go ahead and print a sample to ensure it looks well. Right away you can see analytical highlights from the data like the product Tropical Punch sold the most during October 2006.

Fantastic work! As you can see a 3-D column chart can really impact your audience and quickly give them a glance at the highlights of the raw data without having to go through a table or summary report. It’s really impressive to look at and engages people in a discussion right away.

If this tutorial has helped you, please consider donating.

1 Comment(s)

Comments RSS TrackBack Identifier URI

Leave a comment