Excel Tips and Tricks for Publishers – GRAPHS & PIVOT TABLES

This tutorial is part three of the Excel Tips and Tricks for Publishers series. The first tutorial, Excel Basics, can be found here. The second tutorial, Cell Tricks, can be found here.

Graphs & Pivot Tables

Creating Graphs

A great way to use data is to change it into a graph, to understand it visually, or to illustrate a point. As long as your data is on an Excel sheet, it’s very easy to put it into a graph that you can customize the way you want it.

When setting up your data, having headers and row titles for each point of data is useful as it will be included in the final graph. However titles are not necessary to create it, you just need the numbers. To create your graph, click and drag to select the data you want included. Under the Insert tab, there is a box called Charts which has all the different options for displaying data.

ExcelGP1

Depending on what you want to illustrate with your data, you can choose from the many options of charts. Each type has its own drop down menu, where you have further choices for 3D graphs, shapes of columns, and styles. Typically, while 3D charts look cool, they are not recommended for serious data as the 3D effect can make the data harder to read. For this example, we have selected Line and then a simple 2D line graph.

ExcelGP2

Now that you have a chart, you can decide how you feel about the data presented. Sometimes the data doesn’t come out looking the way we want it to – perhaps you were hoping that each line on the graph would represent a title, rather than a year.

ExcelGP3

The quick way to do this, without having to move all your data around is the Switch Row/Column button, under the Data box. This reverses the order of data so that in this case, the titles will be represented by the lines, and the years will be across the bottom.

ExcelGP4

Formatting

Once you have the graph showing the data in the way you want, you can format the style easily using the top bar that appears called Chart Tools. This is where you find all the customizing options for adding a title to the chart, changing fonts, and even altering the horizontal and vertical axis formatting on the chart. An easy trick to customizing your chart is by choosing a Chart Style or colour palette under Design. By selecting any of the options there, you change the colour scheme and sometimes the line width. These options will change depending on what kind of chart you are using. In Excel 2013, you can choose your colours closer to home by selecting the paintbrush icon beside your chart and clicking the “Color” tab.

ExcelGP5

Another formatting option is using Chart Layouts under the Design tab to format the chart as a whole. Use it to add a title, add data points, change how the axes are formatted, and generally organize the chart for you.

ExcelGP6

Pivot Tables

Pivot tables are extremely useful for organizing large groups of information, and have the added bonus of hiding and unhiding information and reorganizing easily so that you can get a better look at your data for patterns. For example, if you wanted to organize your data by a specific title or perhaps the region it is selling in then creating a pivot table is a good way to do that. Ultimately it is a way for you to see all of your data in different ways, and learn how your books are doing without having to build multiple tables to illustrate it.

To start a pivot table, click and drag the cells you want included in the table, or click the little box at the top left corner of the sheet to select everything. Under the Insert tab select Pivot Table.

A pop-up menu will appear confirming that the correct cells are selected. Click OK and a new sheet will be created with the Pivot Table ready to be customized.

In the new sheet the headers from your data appear in the PivotTable Field List where you can select whether or not you want them visible in your sheet.

By selecting the options on the right, data appears in the sheet. More options then appear in the bottom four boxes, which you can click and drag to reorganize the way the table is presented.

If you drag and drop to swap the positioning of Values and Title, you switch the table to focusing on the values in a different way.

There are also handy drop down menus in each option so that you can view specifics or organize a list however you want. You can find these filters on both the PivotTable Field List or on the item itself in the table.

This tool has so many options for viewing and organizing, and while it can look intimidating at first, it is worth experimenting with it to get more comfortable. Drag and drop the categories into different boxes, and hide/unhide options to get a good look at your data and find patterns.


Up Next… VLOOKUP!