101 Excel 2013 Tips, Tricks and Timesavers (6 page)

Figure 9-2:
Using a formula to display a list of sheet names.

The list of sheet names will adjust if you add sheets, delete sheets, or rename sheets — but the adjustment doesn’t happen automatically. To force the formulas to update, press Ctrl+Alt+F9. If you want the sheet names to adjust automatically when the workbook is calculated, edit the named formula to make it “volatile.”

=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)&T(NOW())


What good is a list of sheet names? Figure 9-3 shows a table of contents created by using the HYPERLINK function. The formula in cell B1 is

=HYPERLINK(“#”&A1&”!A1”,”Go to sheet”)


Clicking a hyperlink activates the worksheet and selects cell A1. Unfortunately, Excel doesn’t support hyperlinking to a chart sheet, so you get an error if a hyperlink points to a chart sheet.

Figure 9-3:
Creating a list of hyperlinks.

If you use this technique, you must save the workbook as a macro-enabled file (with an *.xlsm or *.xls extension).

Tip 10: Using Document Themes

Over the years, I’ve seen hundreds of Excel workbooks that were created by others. A significant percentage of these workbooks have one thing in common: They are ugly!

In an effort to help users create more professional-looking documents, Microsoft designers (starting with Office 2007) incorporated the concept of Office
document themes.
Using themes is an easy (and almost foolproof) way to specify the colors and fonts and a variety of graphical effects in a document. Best of all, changing the entire look of your document is a breeze. A few mouse clicks is all it takes to apply a different theme and change the look of your workbook.

Importantly, the concept of themes is incorporated into other Office applications. Therefore, a company can easily create a standard look for all its documents.

Elements within Excel that are controlled by document themes are

→ Cells and ranges that use theme colors (as opposed to standard colors)

→ Tables

→ Charts

→ Conditional formatting (but not always)

→ Sparkline graphics

→ Pivot tables

→ PivotTable slicers and timelines

→ Shapes

→ SmartArt

→ WordArt

→ Sheet tab colors

Figure 10-1 shows a worksheet that contains various Excel elements. These items all use the default theme, which is known as Office Theme.

Figure 10-2 shows the same worksheet after applying a different document theme. The different theme changes the fonts, colors (which may not be apparent in the figure), and graphical effects for the SmartArt diagram.

Figure 10-1:
The elements in this worksheet use default formatting.

Figure 10-2:
The worksheet, after applying a different theme.

Applying a theme

Figure 10-3 shows the theme choices that appear when you choose Page⇒Layout⇒Themes. This display is a live preview. As you move your mouse over the theme choices, the active worksheet displays the theme. When you see a theme you like, click it to apply the theme to all worksheets in the workbook.

A theme applies to the entire workbook. You cannot use different themes on different worksheets within a workbook.

Figure 10-3:
The built-in Excel theme choices.

When you specify a particular theme, you find that the gallery choices for various elements reflect the new theme. For example, the chart styles that you can choose from vary, depending on which theme is active.

Because themes use different fonts and font sizes, changing to a different theme can affect the layout of your worksheet. For example, after you apply a new theme, a worksheet that printed on a single page may spill over to a second page. Therefore, you may need to make some adjustments after you apply a new theme. For best results, decide on a theme before you do too much work on the file.

Customizing a theme

Office 2013 includes quite a few themes. If that’s not enough, you can modify them and even create your own themes.

Notice that the Page Layout⇒Themes group contains three other controls: Colors, Fonts, and Effects. You can use these controls to change just one of the three components of a theme. For example, if you like the Urban theme but prefer different fonts, apply the Urban theme and then specify your preferred font set by using the Page Layout⇒Themes⇒Fonts control.

Each theme uses two fonts (one for headers and one for the body), and in some cases, these two fonts are the same. If none of the theme choices is suitable, choose Page Layout⇒Themes⇒Fonts⇒Create New Theme Fonts to specify the two fonts you prefer (see Figure 10-4). When you use the Home⇒Fonts⇒Font command, the two fonts from the current theme are listed first in the drop-down list.

Figure 10-4:
Use this dialog box to specify two fonts for a theme.

Use the Page Layout⇒Themes⇒Colors command to select a different set of colors. Also, if you’re so inclined, you can even create a custom set of colors by choosing Page Layout⇒Themes⇒Colors⇒Customize Colors. This command displays the dialog box shown in Figure 10-5. Note that each theme consists of 12 colors. Four of the colors are for text and backgrounds, six are for accents, and two are for hyperlinks. The first ten are the colors that appear in theme color selector controls. As you specify different colors, the Preview panel in the dialog box is updated.

Figure 10-5:
If you’re feeling creative, you can specify a set of custom colors for a theme.

Theme effects operate on graphical elements, such as SmartArt, shapes, and charts. You can’t customize theme effects.

If you customize a theme by using different fonts or colors, you can save the new theme by choosing Page Layout⇒Themes⇒Save Current Theme. Your customized themes appear in the theme list in the Custom category. Other Office applications, such as Word and PowerPoint, can use these theme files.

Tip 11: Understanding Excel Compatibility Issues

The most recent version of Excel is known as Excel 2013, and it’s version 15. Microsoft’s version numbering is a bit misleading because they’ve only released 12 versions of Excel for Windows. The first version was Excel 2, and they skipped right over versions 6 and 13.