Add these 10 Excel tips to your work-day arsenal and unleash your inner spreadsheet maven.
If you work in Excel every day, you’re probably proficient with specific features within the application. But what if you’re so caught up in how you currently perform a task that you overlook a different—and possibly even better—method? Or consider this: you may have devised little workarounds for problem issues—these tis could solve them in an easier way.
This article covers a series of 10 Excel tips to help you work even smarter; some of the topics we look at include: printing, charts, hyperlinks, reusing styles, and trial-and-error calculations. Interested? Read on…
When you need to make a calculation based on worksheet data—but you don’t want the answer to actually appear in your worksheet—let Excel do the math. Select the cells containing the data in question, and then read the calculated results from the Excel status bar.
By default you’ll see the sum of the numbers. You can find other calculations by right-clicking the Status Bar and choosing from these calculations: Sum, Average, Numerical Count, Minimum, and Maximum. You can add as many of these calculations to the Status Bar as you need.
If the values you want to calculate aren’t contiguous, select the first cell or range, and then hold Control as you select each other cell or range you want to include.
When you know the outcome you want, but you don’t know the data that will provide it, you can use Excel’s Goal Seek feature. Goal Seek takes a formula and some known input values and calculates the missing data for you. For example, let’s say you want to save $10,000 in the next 5 years and you anticipate earning 3 percent interest on your savings. The Goal Seek feature can tell you how much you need to save each month.
Enter your input values into a worksheet including a guess as to your monthly savings, and add the relevant function—in this case an FV function. Then select Data > What If Analysis > Goal Seek.
Now set the Set Cell to the cell containing the FV (Savings) function, set the To Value to 10000, and set the By Changing Cell to the cell that contains your guess as to how much you need to save.
When you click OK, Excel calculates the correct savings amount for you. Excel does this by a process of trial-and-error in pretty much the same way that you could do it—only much faster.
When you print an Excel worksheet, save paper by constraining the printout to the exact number of pages you need. You can set a worksheet to print on a single sheet of paper, or you can confine it to a fixed number of pages wide or tall.
Choose File > Print, and then click the Scaling button. Choose from the available options: No Scaling, Fit Sheet on One Page, Fit All Columns on One Page, or Fit All Rows on One Page.
If none of those options work for you, click Custom Scaling Options and you can set the specific number of pages wide or tall. In either case, set only one of the values and leave the other one empty. Excel calculates it automatically.
Formatting a worksheet makes it both attractive and easier to understand. However, creating a new style for each workbook takes a lot of time. Luckily, Excel makes it easy to copy styles from one workbook to another.
First, open the workbook with the style format you want to copy. Now, open the workbook you want to format and, from the Home tab, click the Cell Styles list and choose Merge Styles.
From the Merge Styles dialog, select the workbook that has the styles you want to copy and click OK. These styles will now appear in the Cell Styles gallery for this workbook. And if you haven’t saved the formats in the original workbook, click on a cell that has a format you want to use, go to the Cell Styles dropdown, select New Cell Style, and give it a name.
If you don’t need to print the entire worksheet, you can easily print the portion you need. First, select the cells you want to print. Next, choose File > Print. Now—from the first drop-down list in the Settings area, choose Print Selection.
If you only ever need to print a selection of a larger worksheet, you can make the setting permanent. Select the cells to print, and choose Page Layout > Print Area > Set Print Area. Your selection will be saved with the worksheet. Now whenever you print, the section you designated will be the default printout. Simply choose File > Print.
When you share a workbook with other people, you may find that you each focus on a different aspect of the workbook. If you constantly change the worksheet view to suit your own needs, you can simplify your work life by creating your own View.
First rearrange the worksheet the way you want to see it, and then choose View > Custom Views > Add. Type a name for your view, and select whether to include Print settings and Hidden rows, columns and filter settings. Once you have named your view, save the workbook.
Access your preferred worksheet view by choosing View > Custom Views, and click on your saved view. Your co-workers can configure their own views, too.
Note that Views won’t work with workbooks that include tables. Views do include the currently selected cell—when you display your view the cell pointer moves to that cell automatically.
Hyperlinks not only help us move around the Web, they can help us move around a worksheet. You need to name each cell or range that you want to move to. Select the cell or range and, from the Formulas bar, choose Define Name and type a name for that range.
Next go to the spot where you want to insert the hyperlink, right-click it and choose Hyperlink. In the dialog choose Place in This Document, and then select the name you created to identify the move-to location. Type the text you want to display in the cell, and click OK. You can now move to the named location simply by clicking the hyperlink.
Sometimes you need to add a value with leading zeros to a cell; for example, when you add product numbers. But Excel drops leading zero automatically—making your life more difficult. If you need numbers to show the leading zero—and if you don’t plan to use the numbers to perform math—you can enter them as text values.
Select the range for these values, right-click, and then choose Format Cells > Number, and select Text. However, if you want to enter actual numbers with a leading zero you can create a custom number format to show these zeros.
Start by selecting the cells for these numbers, right-click and choose Format Cells > Number and click the Custom category. Type as many zeros as you have digits in your numbers. For example, to display a 335 as 00335 you must type five zeros (00000) and click OK. The numbers will then display as five digits with as many leading zeros as needed.
When you need to chart multiple data ranges with very different magnitudes of numbers, you will find that the range with very small values won’t be visible. For example, when you combine values and percentages on a chart, the percentages will be so small that they will not be easily seen.
In this situation, add a second axis and plot the percentages against it so that you can see them. First create a chart with both ranges—the large values and the small ones. Select the chart and click Chart Tools > Format tab.
Next go to the selector in the top-left of the Ribbon and select the chart series containing the percentage data. Click Format Selection and—from the Format Data Series panel—click Secondary Axis to add a secondary axis and plot this range on it. This axis will have its own range of values so you can see very small numbers—and very big ones—on the same chart.
Pivot Tables and Pivot Charts are great for analyzing data, and the tools in Excel make them easy to create. When making a Pivot Table consider adding slicers to make it easy to select and view the data you want to see.
Add a slicer: select the Pivot Table (or the Pivot Chart) and from the Pivot Table Tools > Options tab, select Insert Slicer. For a Pivot Chart, select Pivot Chart Tools > Analyze tab and select Insert Slicer.
Choose one or more slicers for the data that you want to analyze. You can then select the data to view in the Pivot Table or the Pivot Chart by clicking the appropriate entry in the Slicer. When selecting options in a slicer, hold the Ctrl key to select multiple data categories for viewing. This is great for your own use, and it also helps inexperienced Excel users to quickly analyze large data sets and to extract only the information they need.
If you have a favorite Excel tip to share, post it in the comments below.
Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her website, HelenBradley.com
|Do you have a comment or question about
this article or other small business topics in general? Speak out in the
SmallBusinessComputing.com Forums. Join the discussion today!