Book Image

101 Excel 2013 Tips, Tricks and Timesavers

By : John Walkenbach
Book Image

101 Excel 2013 Tips, Tricks and Timesavers

By: John Walkenbach

Overview of this book

Excel is a popular program. Millions of people throughout the world use it on a regular basis. But it’s a safe bet that the vast majority of users have yet to discover some of the amazing things this product can do. 101 Excel 2013 Tips, Tricks, & Timesavers?is packed with information that you need to know in order to confidently and seamlessly master the challenges that come with using Excel! Excel 2013 is excellent, but there's lots to learn to truly excel at Excel! In this latest addition to his popular Mr. Spreadsheet's Bookshelf series, John Walkenbach, aka "Mr. Spreadsheet," shares new and exciting ways to accomplish and master all of your spreadsheet tasks. From taming the Ribbon bar to testing and tables, creating custom functions, and overcoming "impossible" charts, mixing nesting limits, and more,?101 Excel 2013 Tips, Tricks, & Timesavers?will save you time and help you avoid common spreadsheet stumbling blocks.
Table of Contents (10 chapters)
Free Chapter
Table of Contents
Title Page

Tip 80: Creating a Quick Frequency Tabulation

This tip describes a quick method for creating a frequency tabulation for a single column of data. Figure 80-1 shows a small part of a range that contains more than 20,000 rows of city and state data. The goal is to tally the number of times each state appears in the list.

Although you can tally the states in a number of ways, a pivot table is the easiest choice for this task.


Figure 80-1: You can use a pivot table to generate a frequency tabulation for these state abbreviations.

Before you get started on this task, make sure that your data column has a heading. In this example, it’s in cell B1.

Activate any cell in the column A or B and then follow these steps:

1. Choose Insert⇒Tables⇒PivotTable.

The Create PivotTable dialog box appears.

2. If Excel doesn’t correctly identify the range, change the Table/Range setting.

3. Specify a location for the PivotTable.