Learn it fast with expert-taught software and skills training at lynda.com.

# Article Center

By Curt Frye | Tuesday, February 03, 2015

## Summarize Data with Conditional Functions in Excel

Excel users are often faced with spreadsheets that summarize sales data for multiple areas, such as states within the U.S. or individual countries.

Functions such as SUM or AVERAGE let you summarize your data as a whole—but it can be difficult to find the totals, averages, or counts for subsets of that data. For example, suppose you want to find the total of all sales to Canada. To do that using a standard SUM formula, you would have to identify cells that contain values for all sales to Canada and then create a formula for just those cells.

Fortunately, there’s a set of conditional functions in Excel that let you specify which values should be included in a sum, average, or count calculation. Those functions are: SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, COUNTIF, and COUNTIFS.

Here’s how to take advantage of them:

By Curt Frye | Tuesday, January 27, 2015

## How to Create Pivot Tables in Excel 2010

Excel workbooks let you summarize your data using a powerful set of built-in functions and features such as sorting and filtering.

That said, basic worksheets are static and make rearranging data difficult. Fortunately, there’s a way to avoid all that cutting and pasting: Pivot Tables.

You can learn everything you’d ever want to know from my lynda.com courses Excel 2007: PivotTables for Data AnalysisExcel 2010: PivotTables in Depthand Excel 2013: PivotTables in Depth.

But here’s a quick-start guide for you:

By Curt Frye | Thursday, November 20, 2014

## Set Up Worksheets Faster with Excel Flash Fill — and More

Setting up worksheets often means entering long strings of data, such as row numbers or dates, to create a framework for your data. This work can be repetitive and boring. What’s worse, it takes time away from analysis.

Let me show you how to make it go faster—with Excel Flash Fill, fill handle, and more.

By Curt Frye | Tuesday, November 04, 2014

## Use Document Inspector in Word — and Avoid Embarrassment

Ever accidentally leave tracked changes in your Microsoft Word document for all the world to see?

I’m going to show you how visible changes in a Word document recently got a political leader in trouble—and how you can use Word’s Document Inspector to avoid making those same embarrassing mistakes in your own docs.

By Curt Frye | Monday, October 13, 2014

## Enhance Excel Lookup Using INDEX and MATCH

Business users of Microsoft Excel take advantage of many of the program’s built-in functions. One of the most popular tools is the VLOOKUP function, which lets you search an Excel worksheet as if it were a database table.

By Curt Frye | Thursday, June 26, 2014

## Getting Started with Excel VBA

Excel is a powerful and versatile tool you can use to analyze data—but not every capability you might want is built in.

Using the Visual Basic for Applications (VBA) language, you can script custom processes in Excel. VBA is an object-oriented language, which means that elements of Excel—such as workbooks, worksheets, and the program itself—are represented as objects. An object has three main components:

• properties, which describe the object
• methods, which are things the object can do
• events, which are occurrences the object can recognize and react to

By Curt Frye | Monday, August 20, 2012

## Highlighting row differences in Excel

Recognizing when your numbers don’t add up is key to successful operations management, which is why organizations of all types and sizes use the Excel spreadsheet program to manage their operations and inventory. A real-world example of this might be conducting a monthly inventory analysis that compares the number of products in your system with the units counted in your warehouse.

In Excel 2007 and 2010, you can quickly check for differences between these two inventory numbers. First, you select the numbers in your worksheet.

Next, go to the Home tab on the ribbon, click the Find & Select button, and then click Go To Special.

Your Excel data is laid out in two columns, so you want to look for differences between the two cells within each row (A2 compared to B2, A3 compared to B3, and so on). To do this, select the Row differences radio button in the Go To Special dialogue box and click OK.

When you click OK, Excel examines the selected cell range for differences between cells in the same row and highlights cells in the right-hand column that are different from their mates in the left-hand column.

In this case, cells B4 and B7 contain values that differ from their mates in cells A4 and A7.

If your data were arranged in rows, you could highlight cells with different values by selecting the data cells in the worksheet and clicking the Column differences radio button in the Go To Special dialog box.

The Go To Special dialog box is often overlooked by even advanced Excel users, but it’s worth exploring all its useful options.

Interested in more?

Suggested courses to watch next:

Curt Frye is the author of over a dozen lynda.com courses and more than 20 books on Microsoft Excel, including Microsoft Excel 2010 Step by Step for Microsoft Press. He is also a popular speaker, presenting his Improspectives® keynote addresses and workshops for corporate clients.

### Get the latest news

•   New course releases
•   Pro tips and tricks

New releases submit clicked

## A lynda.com membership includes:

Certificates of completion
New courses added every week (almost every day!)
Course history to track your progress
Playlists and bookmarks to organize your learning

Thanks for signing up.

We’ll send you a confirmation email shortly.

• new course releases
• general communications
• special notices

Keep up with news, tips, and latest courses with emails from lynda.com.

• new course releases