Learn Clever Excel Tricks

Anyone can learn the basics of Excel and get the job done, but if you really want to power ahead, try some of these clever Excel tricks. If you use Excel a lot, just one of these lessons could end up taking hours off your week, leaving you with more time to get other stuff done.

Once you get used to using Excel, you can find that using the mouse to select data in your spreadsheet is somewhat slow and time consuming. Here's a quick technique for selecting a range of cells in Excel.

1 comment

The SUMIFS function in Excel allows you to sum the values in a range of cells that meet multiple conditions, or criteria. For example, you might use the SUMIFS function in a sales spreadsheet to to add up the value of sales of a specific product by a given sales person (e.g. the value of all sales of a microwave oven made by John). This lesson explains how to use SUMIFS.

42 comments

One of the last keyboard shortcuts I mastered in Excel was moving between worksheets. Fortunately it's easy, and you don't need to wait as long as I did.

When printing in Excel 2010 for Windows, it is sometimes useful to print a set of rows (e.g. column headings) on each page in the print out. There is nothing worse than having a printout that runs to multiple pages, with the column headings only printed on the first page. This lesson shows you how to get header rows printing at the top of every page.

12 comments

Entering the same formula or value into multiple cells can be time consuming and boring. Most people, if they want to enter the same formula into a row or column of data, will enter the formula in the first cell, then copy and paste it into the rest of the cells where they need the formula. This lesson shows you an even faster way to do it.

2 comments

When printing in Excel 2010 for Windows, it is sometimes useful to print a set of rows (e.g. column headings) on each page in the print out. There is nothing worse than having a printout that runs to multiple pages, with the column headings only printed on the first page. This lesson shows you how to get header rows printing at the top of every page.

12 comments

If you have a range of cells, of which some contain values and some are blank, and you want to select just the blank cells, there is a quick way to select those blank cells that doesn't involve manually clicking on every one.

The SUMPRODUCT function allows you to multiply two arrays of numbers together (e.g. Quantity Sold and Price Per Unit) and add the results each individual calculation together. Without the SUMPRODUCT function, you'll find yourself having to create a third column in which you multiply the Quantity by Price for each row, and then find the sum of all the individual formulas. This lesson shows you how to use SUMPRODUCT to do all that with just one formula.

4 comments

In this lesson, we cover how to print an Excel spreadsheet with page numbers on each page. Excel has changed a lot over the years, but one thing that hasn't changed is the way that you insert page numbers into a spreadsheet. Unfortunately, Excel 2010 does not make it easy to find the options for doing this. This lesson covers the basics of inserting page numbers as well as providing an overview of some more advanced options.

1 comment

The SUMPRODUCT function allows you to multiply two arrays of numbers together (e.g. Quantity Sold and Price Per Unit) and add the results each individual calculation together. Without the SUMPRODUCT function, you'll find yourself having to create a third column in which you multiply the Quantity by Price for each row, and then find the sum of all the individual formulas. This lesson shows you how to use SUMPRODUCT to do all that with just one formula.

4 comments

Pages