Excel Annoyances

Chapter 4. Manipulating Data Annoyances

Chapters 1 through 3 were all about getting data into Excel, summarizing it, and making it look pretty. In this chapter, you’ll make your data tell you what you need to know—to run a business, to manage a sports team, or to figure out how much money you’ll need to make those mortgage payments.

I’ll start out by showing you how to make your life easy by sorting and filtering your worksheet data. For example, you’ll learn how to create a formula that operates on visible cells only, master multilevel filters, and run sorts that whittle down your data to a manageable size.

Although Excel isn’t a database, you can use it to find values in data lists thanks to the lookup family of formulas. Finally, I’ll dig into PivotTables, the most versatile and useful Excel tool in the book, but one that’s tricky for beginners and annoying for pros. You’ll learn how to avoid the pitfalls when setting up your data, creating PivotTables, analyzing your data, and more.

SORTING AND FILTERING ANNOYANCES

SORT BY VALUES IN MULTIPLE COLUMNS

The Annoyance:

Sorting the data in a single column is so easy, even I figured it out. I just click any cell in the column I want to sort, and then click the Sort Ascending or Sort Descending buttons on the toolbar. But look at my worksheet (Figure 4-1). I recorded my sales data by department (Accessories, Cars, Service), which is a handy enough view. But it doesn’t help me see how I’m doing on a daily basis overall. How can I view my ...