Doing basic number formatting

The Number group on the Home tab of the Ribbon contains several controls for applying common number formats quickly. The Number Format drop-down control gives you quick access to 11 common number formats. In addition, the Number group contains some buttons. When you click one of these buttons, the selected cells take on the specified number format. Table 21.1 summarizes the formats that these buttons perform in the U.S. English version of Excel.

Getting fancy with custom number formatting

When you apply number formatting, you are giving Excel instructions using a format string. A number format string is a code that tells Excel how you want the values in a given range to appear cosmetically.

To see this code, follow these steps to apply basic number formatting:

1. Right-click a cell or range and select Format Cells. The Format Cells dialog box appears.
2. Click the Number tab; then choose the Number category, select the Use 1000 Separator check box, 0 decimal places, and enclose negative numbers in parentheses.
3. Click the Custom category as shown in Figure 21.1. Excel takes you to a screen that exposes the syntax that makes up the format you selected.

Number formatting strings consist of different individual number formats separated by semicolons. In this case, you see two different formats: the format to the left of the semicolon and the format to the right of the semicolon.

#,##0_);(#,##0)

By default, any formatting to the left of the first semicolon is applied to positive numbers, and any formatting to the right of the first semicolon is applied to negative numbers. So, with this choice, positive numbers will be formatted as a simple number, and negative numbers will be formatted with parentheses, like the following:

1,982
(1,890)

You can edit the syntax in the Type input box so that the numbers are formatted differently. For example, try changing the syntax to the following:

+#,##0;-#,##0

When this syntax is applied, positive numbers will start with the + symbol, and negative numbers will start with a − symbol, like so:

+1,200
-15,000

This comes in handy when formatting percentages. For instance, you can apply a custom percent format by entering the following syntax into the Type input box:

+0%;-0%

This syntax gives you percentages that look like the following:

+43%
-54%

You can get fancy and wrap your negative percentages with parentheses using the following syntax:

0%_);(0%)

This syntax gives you percentages that look like the following:

43%
(54%)

Formatting numbers in thousands and millions

Formatting numbers to appear in thousands or millions helps present values without inundating your audience with overlarge numbers. To show your numbers in thousands, highlight them, right-click, and select Format Cells.

After the Format Cells dialog box opens, click the Custom category to get to the screen shown in Figure 21.1. In the Type input box, enter the following syntax:

#,##0,

After you confirm your changes, your numbers will automatically appear in the thousands place.

The beautiful thing here is that this technique doesn't change or truncate your numeric values in any way. Excel is simply applying a cosmetic effect to the number. Figure 21.2 illustrates this. Compare the values you see in the formula bar versus what is shown in the cells.

The selected cell has been formatted to show in thousands. You see 118k. But if you look in the formula bar above it, you'll see the real unformatted number (117943.60578). The 118k you are seeing in the cell is a cosmetically formatted version of the real number shown in the formula bar.

If needed, you can indicate that the number is in thousands by adding “k” to the number syntax.

#,##0,"k"

This would show your numbers like this:

118k
318k

You can use this technique on both positive and negative numbers.

#,##0,"k"; (#,##0,"k")

After applying this syntax, your negative numbers also appear in thousands.

118k
(318k)

Need to show numbers in millions? Easy. Simply add two commas to the number format syntax in the Type input box.

#,##0.00,, "m"

Note the use of the extra decimal places (.00). When converting numbers to millions, it's often useful to show additional precision points, as in the following example:

24.65 m

Hiding and suppressing zeros

In addition to formatting positive and negative numbers, Excel allows you to provide a format for zeros. You do this by adding another semicolon to your custom number syntax. By default, any format syntax placed after the second semicolon is applied to any number that evaluates to zero.

For example, the following syntax applies a format that shows n/a for any cells that contain zeros:

#,##0_);(#,##0);"n/a"

You can also use this to suppress zeros entirely. If you add the second semicolon but don't follow it with any syntax, cells containing zeroes will appear blank.

#,##0_);(#,##0);

Again, custom number formatting affects only the cosmetic look of the cell. The actual data in the cell is not affected. Figure 21.3 demonstrates this. The selected cell is formatted so that zeros appear as n/a, but if you look at the Formula bar, you can see the actual unformatted cell contents.

[Blue]+0%;[Red]-0%

[Green]+0%;[Red]-0%

[Color10]+0%;[Red]-0%

Formatting dates and times

Custom number formatting isn't just for numbers. You can also format dates and times. As Figure 21.4 illustrates, you use the same dialog box to apply date and time formats using the Type input box.

The code used for date and time formatting is fairly intuitive. For example, ddd is the syntax for the three-letter day, mmm is the syntax for the three-letter month, and yyyy is the syntax for the four-digit year.

There are several variations on the format for days, months, years, hours, and minutes. It's worthwhile to take some time and experiment with different combinations of syntax strings.

Table 21.3 lists some common date and time format codes that you can use as starter syntax for your reports and dashboards.

Format Code	1/31/2022 7:42:53 PM is Displayed As
m	1
mm	01
mmm	Jan
mmmm	January
mmmmm	J
dd	31
ddd	Mon
dddd	Monday
yy	22
yyyy	2022
mmm-yy	Jan-22
dd/mm/yyyy	31/01/2022
dddd mmm yyyy	Monday Jan 2022
mm-dd-yyyy h:mm AM/PM	01-31-2022 7:42 PM
h AM/PM	7 PM
h:mm AM/PM	7:42 PM
h:mm:ss AM/PM	7:42:53 PM

Using symbols to enhance reporting

Symbols are essentially tiny graphics, not unlike those you see when you use the Wingdings, Webdings, or other fancy fonts. However, symbols are not really fonts. They're Unicode characters. Unicode characters are a set of industry-standard text elements designed to provide a reliable character set that remains viable on any platform regardless of international font differences.

One example of a commonly used symbol is the copyright symbol (©). This symbol is a Unicode character. You can use this symbol on a Chinese, Turkish, French, or English (United States) PC, and it will reliably be available with no international differences.

In terms of Excel presentations, Unicode characters (or symbols) can be used in places where conditional formatting cannot. For instance, in the chart labels that you see in Figure 21.5, the x-axis shows some trending arrows that allow for an extra layer of analysis. This couldn't be done with conditional formatting.

Let's take some time to review the steps that led to the chart in Figure 21.5.

Start with the data shown in Figure 21.6. Note that you have a designated cell to hold any symbols that you're going to use (C1 in this case). This cell isn't really all that important. It's just a holding cell for the symbols that you'll insert.

Now, follow these steps:

1. Click in C1 and then select Symbols ➪ Symbol on the Insert tab. The Symbol dialog box shown in Figure 21.7 opens. The idea here is to scroll through the symbols to find the one you want. Note that you can use the Font and Subset drop-down selectors at the top of the dialog box to quickly jump to a particular set of symbols (in this case, Ariel and Geometric Shapes).

   

2. Find and select your desired symbols by clicking the Insert button after each symbol. In this scenario, select the upward-pointing triangle and click Insert. Then click the downward-pointing triangle and click Insert. Close the dialog box when you're done. At this point, you have the up triangle and down triangle symbols in cell C1, as shown in Figure 21.8.
3. Click the C1 cell, go to the Formula bar, and copy the two symbols by highlighting them and pressing Ctrl+C on your keyboard.
4. Go to your data table, right-click the percentages, and then select Format Cells.

   

5. In the Format Cells dialog box, create a new custom format by pasting the up- and down-triangle symbols into the appropriate syntax parts (see Figure 21.9). In this case, any positive percentage will be preceded with the up-triangle symbol, and any negative percentage will be preceded with the down-triangle symbol.

   

6. Click OK. The symbols are now part of your number formatting.

   Figure 21.10 illustrates what your percentages look like. Change any number from positive to negative (or vice versa), and Excel automatically applies the appropriate symbol.

   

Because charts automatically adopt number formatting, a chart created from this data will show the symbols as part of the labels. Simply use this data as the source for the chart.

This is just one way to use symbols in your reporting. With this basic technique, you can insert symbols to add visual appeal to tables, PivotTables, formulas, or any other object you can think of.

Inserting a shape

You can add a shape to a worksheet by choosing Insert ➪ Illustrations ➪ Shapes. The Shapes gallery, shown in Figure 21.11, opens to show you the choices.

Shapes are organized into categories, and the category at the top displays the shapes that you've used most recently. To insert a shape into a worksheet, you can do one of the following:

• Click the shape in the Shapes gallery and then click in the worksheet. A default-sized shape is added to your worksheet.
• Click the shape and then drag in the worksheet. This allows you to create a larger or a smaller shape or a shape with different proportions than the default.

Here are a few tips to keep in mind when creating shapes:

• Every shape has a descriptive name (for example, Line Arrow). To change the name of a shape, select it, type a new name in the Name box (to the left of the formula bar), and press Enter.
• To select a specific shape on a worksheet, just click it.
• When you create a shape by dragging, hold down the Shift key to maintain the object's default proportions.
• You can control the way objects appear onscreen in the Advanced tab of the Excel Options dialog box. (Choose File ➪ Options.) This setting appears in the Display Options for This Workbook section. Normally, the All option is selected under For Objects Show. You can hide all objects by choosing Nothing (Hide Objects). Hiding objects may speed things up if your worksheet contains complex objects that take a long time to redraw.

Inserting SVG icon graphics

Excel 365 includes an icon library that offers free Scalable Vector Graphics (SVG) icons. SVG graphics can be sized and formatted without losing image quality. These icon graphics are essentially a modern set of graphic files that can be used to add spicy visual elements to your Excel dashboards and infographics.

To add an icon to your worksheet, select Insert ➪ Illustrations ➪ Icons. This activates the dialog box shown in Figure 21.12. Here you can browse by category or search for a topic. When you find the graphic you want, select it and then click Insert. You can also simply double-click the graphic. Excel will insert it into your workbook.

Inserting 3D models

3D models are just what they sound like, graphics that can be dynamically rotated to see them from all sides. To add a 3D model to your worksheet, select Insert ➪ Illustrations ➪ 3D Models.

Just as with the previously mentioned icons, you can use the Online 3D Models dialog box to browse or search for certain objects. When you find the graphic you want, select it, and then click Insert or double-click the graphic. Excel will insert the graphic into your workbook. Figure 21.13 illustrates how a 3D model can be rotated and viewed from all angles.

You'll quickly find that the stock 3D models are frankly underwhelming and irrelevant to most Excel users. However, the real benefit comes to those who regularly use their own 3D models. Excel allows the use of custom 3D model files outside of the stock options provided. You can use any of the following valid file types:

• .3mf - 3D Manufacturing Format
• .fbx - Filmbox Format
• .glb - Binary GL Transmission Format
• .obj - Object Format
• .ply - Polygon Format
• .stl – STereoLithography Format

To insert your own custom 3D model graphics, select Insert ➪ Illustrations and click the drop-down arrow next to the 3D Models command. Select the option labeled This Device. Excel opens a dialog box allowing you to browse your own device for the file you want to insert.

Formatting shapes and icons

Although icons feel and behave like standard shapes, they have different contextual tabs.

When you select a shape, the Shape Format contextual tab is available, with the following groups of commands:

• Insert Shapes  Insert new shapes; change a shape to a different shape.
• Shape Styles  Change the overall style of a shape; modify the shape's fill, outline, or effects.
• WordArt Styles  Modify the appearance of the text within a shape.
• Accessibility  Provide alternate text descriptions for the visually impaired.
• Arrange  Adjust the “stack order” of shapes, align shapes, group multiple shapes, and rotate shapes.
• Size  Change the size of a shape by typing dimensions.

When you select an icon, the Graphics Format contextual tab is available with the following groups of commands:

• Change Graphic  Replace an existing graphic with a new graphic from a file, the Microsoft icons gallery, or an online source.
• Graphics Styles  Change the overall style of a graphic; modify the graphic's fill, outline, or effects.
• Accessibility  Provide alternate text descriptions for the visually impaired.
• Arrange  Adjust the “stack order” of graphics, align graphics, group multiple graphics, and rotate graphics.
• Size  Change the size of a graphic by typing dimensions.

When you select a 3D model, the 3D Model contextual tab is available with the following groups of commands:

• Play 3D  Control the animation of the 3D model.
• Adjust  Replace the 3D model with another one or reset the 3D model to its original position.
• 3D Model Views  Quickly position the 3D model to preset angles.
• Accessibility  Provide alternate text descriptions for the visually impaired.
• Arrange  Adjust the “stack order” of 3D models, align 3D models, and group multiple 3D models.
• Size  Change the size of a 3D model by typing dimensions; pan and zoom to focus on a specific area of the 3D model.

As an alternative to the Ribbon, you can right-click the shape, icon, or 3D model and choose the Format option. This displays a task pane containing formatting options. Any changes made via the task pane appear immediately, and you can keep the Format task pane open while you work.

You could read 20 pages about formatting shapes, icons, and 3D models, but it wouldn't be a very efficient way of learning. The best way, by far, to learn about formatting shapes, icons, and 3D models is to experiment. The formatting commands are intuitive, and you can always use Undo if a command doesn't do what you expected it to do.

Enhancing Excel reports with shapes

Most of us think of Excel shapes as mildly useful objects that can be added to a worksheet if we need to show a square, some arrows, a circle, and so forth. But if you use your imagination, you can leverage Excel shapes to create stylized interfaces that can really enhance your dashboards. Here are a few examples of how Excel shapes can spice up your dashboards and reports.

Creating visually appealing containers with shapes

A peekaboo tab lets you tag a section of your dashboard with a label that looks like it's wrapping around your dashboard components. In the example illustrated in Figure 21.14, a peekaboo tab is used to label this group of components as belonging to the North region.

As you can see in Figure 21.15, there is no real magic here. It's just a set of shapes and text boxes that are cleverly arranged to give the impression that a label is wrapping around to show the region name.

Want to draw attention to handful of key metrics? Try wrapping your key metrics with a peekaboo banner. The banner shown in Figure 21.16 goes beyond boring text labels, allowing you to create the feeling that a banner is wrapping around your numbers. Again, this effect is achieved by layering a few Excel shapes so that they fall nicely on top of each other, creating a cohesive effect.

Layering shapes to save space

Here's an idea to get the most out of your dashboard real estate. You can layer pie charts with column charts to create a unique set of views (see Figure 21.17).

Each pie chart represents the percent of total revenue and a column chart showing some level of detail for the region. Simply layer your pie chart on top of a circle shape and a column chart.

Constructing your own infographic widgets with shapes

Excel offers a way to alter shapes by editing their anchor points. This opens the possibility of creating your own infographic widgets. Right-click a shape and select Edit Points. This places little points all around the shape (see Figure 21.18). You can then drag the points to reconfigure the shape.

Constructed shapes can be combined with other shapes to create interesting infographic elements that can be used in your Excel dashboards. In Figure 21.19, a newly constructed shape is combined with a standard oval and text box to create nifty infographic widgets.

Creating dynamic labels

Dynamic labeling is less a function in Excel than it is a concept. Dynamic labels are labels that change to correspond to the data you're viewing.

Figure 21.20 illustrates one example of this concept. The selected Text Box shape is linked to cell C3 (note the formula in the Formula bar). As the value in cell C3 changes, the text box displays the updated value.

Creating linked pictures

A linked picture is a special kind of shape that displays a live picture of everything in a given range. Think of a linked picture as a camera that monitors a range of cells.

To “take a picture” of a range, follow these steps:

1. Select the range.
2. Press Ctrl+C to copy the range.
3. Activate another cell.
4. Choose Home ➪ Clipboard ➪ Paste ➪ Linked Picture (see Figure 21.21).

The result is a live picture of the range you selected in step 1.

Linked pictures give you the freedom to test different layouts and chart sizes without the need to work around column widths, hidden rows, or other such nonsense. In addition, linked pictures have access to the Picture Format options. When you click on a linked picture, you can go to the Picture Format contextual tab and play around with the picture styles there.

Figure 21.22 illustrates two linked pictures displaying the contents of the ranges on the left. As those ranges change, the linked pictures on the right will update. These can be moved, resized, and even placed on a completely different sheet.

SmartArt basics

To insert SmartArt into a worksheet, choose the SmartArt command found under Insert ➪ Illustrations. Excel displays the Choose a SmartArt Graphic dialog box shown in Figure 21.23. The diagrams are arranged in categories along the left. When you find one that looks appropriate, click it for a larger view in the panel on the right, which also provides some usage tips. Then click OK to insert the graphic.

When you insert or select a SmartArt diagram, Excel displays the Text Pane, the Type Your Text Here window that guides you through entering text (see Figure 21.24).

To add an element to the SmartArt graphic, choose SmartArt Design ➪ Create Graphic ➪ Add Shape.

When working with SmartArt, keep in mind that you can move, resize, or format individually any element within the graphic. Select the element and then use the tools on the Format tab.

You can easily change the layout of a SmartArt diagram. Select the object and then choose SmartArt Design ➪ Layouts. Any text that you've entered remains intact.

After you decide on a layout, you may want to consider other styles or colors available in the SmartArt Design ➪ SmartArt Styles group.

WordArt basics

You can use WordArt to create graphical effects in text.

To insert a WordArt graphic on a worksheet, choose Insert ➪ Text ➪ WordArt and then select a style from the gallery. Excel inserts an object with the placeholder text Your text here. Replace that text with your own, resize it, and apply other formatting if you like.

When you select a WordArt image, Excel displays the Shape Format contextual tab. Use the controls to vary the look of your WordArt.

About graphics files

Graphics files come in two main categories:

• Bitmap  Bitmap images are made up of discrete dots. They usually look pretty good at their original size but often lose clarity if you increase the size. Examples of common bitmap file formats include BMP, PNG, JPEG, TIFF, and GIF.
• Vector  Vector-based images, on the other hand, are composed of points and paths that are represented by mathematical equations, so they retain their crispness regardless of their size. Examples of common vector file formats include CGM, WMF, and EPS.

When you insert a picture on a worksheet, you can modify the picture in a number of ways from the Picture Format contextual tab, which becomes available when you select a picture object. For example, you can adjust the color, contrast, and brightness. In addition, you can add borders, shadows, reflections, and so on—similar to the operations available for shapes.

In addition, you can right-click and choose Format Picture to use the controls in the Format Picture task pane.

An interesting feature is Artistic Effects. This command can apply a number of Photoshop-like effects to an image. To access this feature, select an image and choose Picture Format ➪ Adjust ➪ Artistic Effects. Each effect is somewhat customizable, so if you're not happy with the default effect, try adjusting some options.

Inserting screenshots

Excel can also capture and insert a screenshot of any program currently running on your computer (including another Excel window). To use the screenshot feature, follow these steps:

1. Make sure the window you want to use displays the content you want.
2. Choose Insert ➪ Illustrations ➪ Screenshot. You'll see a gallery that contains thumbnails of all windows open on your computer (except the current Excel window).
3. Click the image you want. Excel inserts it into your worksheet.

You can use any of the normal picture tools to work with screenshots.

Displaying a worksheet background image

If you want to use a graphics image for a worksheet's background (similar to wallpaper on the Windows Desktop), choose Page Layout ➪ Page Setup ➪ Background and select a graphics file. The selected graphics file is tiled on the worksheet.

Unfortunately, worksheet background images are for onscreen display only. These images do not appear when the worksheet is printed.