Nobody likes spending days poring through a spreadsheet,
reorganizing data. Today I share some shortcuts you can use
with my favorite Excel feature, pivot tables. It sounds
boring, but pivot tables can turn 50,000 rows of detailed data
into a summary report for your boss in 30 seconds, saving you
tons of work.
For example, a typical company sales data sheet includes
fields for customer, product, date, sales region, and revenue.
Here's how you create your pivot table.
- Select a single cell.
- Click Data, Pivot Table, and Pivot Chart Report.
- Click Finish.
You'll see a new sheet with the blank pivot table.
If you want to produce a summary by region and by product,
drag Region from the Field list to the Row Fields area and
Product from the Field list into the Columns Fields area. If
you want to show the revenue for each group, drag Revenue from
the Field list to the area marked Drop Data Items Here.
Customize
Once you have the pivot table, you can move fields. If you
suddenly decide you want products listed down one side and
regions across the top, drag and drop the gray fields where
you want them. Or, if you want to see which customers bought
each product, drag the Customer field where you want it.
If you need to produce similar reports for different
regions, drag the Region field to the Page Field area and move
Products over to the column area to create a master report.
Click the Region drop-down menu and select East to customize a
report for the East region. If you just want to find the top
five customers, double-click the Customer button, click
Advanced, and dial the Top 10 Autoshow setting down to five
accounts based on revenue.
AutoFormat your reports
You quickly made your summary reports. Now add some
formatting to make them look professional.
- Highlight the report.
- Click Format and AutoFormat.
- Select the format you want to apply.
- Click OK.
Keep reading to find out how to get information from
websites into Excel and to learn an easy way to point out
deadlines to everyone in your company.
Mr. Excel Bill Jelen runs the Ask Mr.
Excel website and is co-author of Mr. Excel On Excel.
.gif){kind=small}
