/>
X

Join or Sign In

Register for your free ZDNet membership or if you are already a member, sign in using your preferred method below.

Use your email Use Linkedin Use Facebook

Six clicks: Excel power tips to make you an instant expert

Excel isn't just for number crunchers with accounting degrees. In this gallery, I present six of my favorite hidden features to make you more productive when working with Excel spreadsheets.

|
ed-bott.jpg
|
00-excel-power-tips.png
1 of 7 Ed Bott/ZDNet

Tips and tricks for Microsoft Excel

Spreadsheets don't have to be ugly, and the process of creating a list or analyzing numbers doesn't have to be tedious. In this gallery, I present six shortcuts and hidden features that can make you more productive in Excel 2010 and Excel 2013.

01-convert-formulas-to-values.png
2 of 7 Ed Bott/ZDNet

Convert formulas to their results, instantly

This is probably my single favorite Excel shortcut.

Worksheets commonly contain one or more calculated columns: averages of a range of data, converted text, and so on. What if you want to eliminate those formulas and convert the contents of the column to the results?

The secret is to copy the range you want to convert and then use the Paste Special function (Ctrl+Alt+V), which has an option to paste just the values from the formulas you copied.

If you do this often enough, you can even streamline the process with keyboard shortcuts. Select the range to be converted, and press Ctrl+C to copy it to the Clipboard. Now, without changing the selection, press Alt+E, S, V, and then press Enter. The formulas in the selected range are now replaced with their results.

02-sparklines.png
3 of 7 Ed Bott/ZDNet

Add sparklines to show visual trends

Think of Sparklines as tiny charts that fit inside a single cell. They offer a really easy way to help visualize trends in data over time, especially when those trends aren't immediately obvious from the raw numbers.

A sparkline goes at the end of a row of numbers. To insert a sparkline, select the cell (or cells) to the right of the row(s) of data you want to visualize. Then, from the Insert tab on the ribbon, click one of the three choices in the Sparklines group: Line, Column, or Win/Loss.

This example shows a very simplified use of the Line option, which makes it immediately obvious that one of these four rows of data is on a different trend line than the other three.

For more ideas on how to use this feature, see this detailed online help article.

03-conditional-formatting.png
4 of 7 Ed Bott/ZDNet

Use conditional formatting to automatically highlight data

One of the most common uses of an Excel spreadsheet is to show large amounts of data in numeric form. But the trouble with raw numbers is that your audience has to stare at those numbers to pick out patterns or identify possible problems.

The much better alternative is to use conditional formatting to add color to cells whose contents match rules you set up. So, if you have a worksheet filled with sales numbers by product category, you can automatically highlight below-average numbers in red and above-average results in green.

You'll find the Conditional Formatting menu on the Home ribbon. In the example shown here, I've used the Green-Yellow-Red Color Scale option to automatically analyze a large range of numbers and apply gradient color shading based on their contents.

If you just want to identify cells that contain unusual results, use the Highlight Cells Rules option and define formatting based on a formula or an absolute number: If you've calculated actual sales versus projections, you can identify underperformers by applying deep red text on a light red background to any cell whose value is lower than 100 percent.

04-copy-a-sheet-tab.png
5 of 7 Ed Bott/ZDNet

Drag and drop to copy a worksheet tab

There are lots of reasons to copy a worksheet tab. You might want to copy last month's budget to a new tab and then replace the numbers with this month's results, for example. Or maybe you want to make a backup copy of a worksheet tab so you can experiment with its data without tampering with the original numbers.

Although you can use menus and dialog boxes to copy worksheets, there's a much faster way. Point to the tab you want to copy, then hold down the Ctrl key and click to begin dragging the tab. The mouse pointer changes to an icon with a plus sign on it. When you reach the spot where you want the new tab to appear, let go of the mouse button to drop it there.

05-filter-list.png
6 of 7 Ed Bott/ZDNet

Filter the contents of a list

One of the most useful things you can do with Excel is track data in lists. The data doesn't have to be numeric. It can include text and dates as well.

For long lists, it's often helpful to be able to filter the list's contents so you can zero in on a portion of the list without being distracted by the full data set. For that task, the built-in filtering options are a godsend.

To start, you need a list that has headers (labels above each column) with no empty rows. Click anywhere within the list and then, on the Data tab, click the Filter button. That displays a small arrow alongside each column header, which you can click to reveal the filter list.

Clear any checkbox to filter out cells that match that value. If you've chosen the filter list for a column containing dates, you can select by year, month, day, or click the Date Filters option and build a filter using relative values (Last Quarter, Year To Date) or operators like Between, Before, and After.

06-autosum-alternate-functions.png
7 of 7 Ed Bott/ZDNet

Use AutoSum to calculate more than just sums

The AutoSum button has been in Excel since the earliest days. Most of the time, it does exactly what its name suggests: If you click the AutoSum button on the Home tab, Excel automatically inserts a formula in the current cell that uses the SUM function to add the values in the column above or the row to the left.

But click the drop-down arrow to the right of the AutoSum button and you get some extra options. Instead of the sum, you can calculate the average of a row or column, for example, or count the number of entries.

But the AutoSum button does much more when you use it in conjunction with Excel tables. To see this feature in action, turn your list (with or without headers) into a table, by choosing Table from the Insert tab or using the Ctrl+T shortcut.

With a table, the AutoSum button works differently. Click in any cell to the right of your table and click AutoSum to create a new calculated column that uses a special synta you would never want to enter by hand. Or click at the bottom of a column, as in the example shown here, and then use the drop-down arrow to customize the formula Excel creates, which uses the Subtotal function.

Related Galleries

Azure Synapse Analytics data lake features: up close
azure-ml-integration-via-notebook.png

Related Galleries

Azure Synapse Analytics data lake features: up close

Pitfalls to Avoid when Interpreting Machine Learning Models
1-bad-model-generalization.jpg

Related Galleries

Pitfalls to Avoid when Interpreting Machine Learning Models

When chatbots are a very bad idea
When Chatbots are a very bad idea ZDNet

Related Galleries

When chatbots are a very bad idea

How ubiquitous AI will permeate everything we do without our knowledge.
How ubiquitous AI will permeate everything we do without our knowledge ZDNet

Related Galleries

How ubiquitous AI will permeate everything we do without our knowledge.

Streaming becomes mainstream
streaming1.png

Related Galleries

Streaming becomes mainstream

Photos: How FC Barcelona uses football player data to win games
barca4.jpg

Related Galleries

Photos: How FC Barcelona uses football player data to win games

Heart and sleep apps that work with the Apple Watch
heart-graph.jpg

Related Galleries

Heart and sleep apps that work with the Apple Watch