Excel 2013/2016: Get Your Hands Dirty

By Sam Akrasi

This book is not for beginners. It does not teach you how to move around worksheets; neither does it teach you how to copy the contents of one cell to the other. The book assumes that you have worked with Excel for at least six months. We start by introducing you to the Ribbon and how to customize the user interface. You learn how to automate worksheet operations with macros. We take you through creating custom formats, data validation, worksheet outlines, data consolidation, error checking, etc. We look at intermediate and advanced functions such as Indirect, Match, Find, etc. and show you how to combine these functions to solve real-world problems. We discuss business models and apply statistical and financial functions. The rest of the topics we treat include using table formulas, creating range names, using names for formulas, Date and Time functions, error checking, workbook/worksheet protection, etc. We look at the Lookup functions (Vlookup, Hlookup, etc.) and the functions that can be used in lieu of the lookup functions to perform more complex lookups. Every chapter of the book contains exercises to reinforce your learning experience.

We believe that practice makes perfect, so at the end of the book, we provide you with over 120 hands-on exercises (step-by-step answers provided). We guarantee that after going through all the exercises in the book, you will be on your way to becoming an Excel expert.

• Language: English
• Publisher: Xlibris AU
• Release date: Aug 27, 2019
• ISBN: 9781543407501

Sam Akrasi

SAM AKRASI (aka Sam Osei-Tutu) is a Mining Engineer by profession and a data aficionado by nature. Sam has been involved in spreadsheet applications for over 30 years, beginning with the now-extinct products such as Symphony and Lotus 1-2-3. His training as a Mining Engineer and love for data have naturally led to his various assignment of processing large amounts of data in the resources industry and other similar industries. Sam has earned a number of Microsoft certifications. Beginning in 1998, when he earned his first certification as a MS Access developer, Sam has maintained his interest in Microsoft data processing tools. He currently holds certifications in SQL Server, Microsoft Office (Excel) Specialist Expert (MOSE) and is currently on the last leg of his MCSE (Business Intelligence) certification. Sam is also a Microsoft Certified Trainer and currently doubles as a consultant and freelance trainer in Excel and the BI tools.

Book preview

2.  Using Custom Number Formats

Numbers in cells can be displayed in a variety of formats. In addition to the many built-in number formats, you can also create custom formats.

2.1  About Number Formatting

The key thing to notice about number format is that they are for display only. The actual number remains intact, and any formula that uses the formatted number uses the actual number.

Note: An exception to this rule occurs if you specify the Set Precision as Displayed option on the Advanced tab in the Excel Options dialog box. If that option is in effect, formulas use the values that are displayed in the cells as a result of a number format applied to the cells. In general, using this option is not a clever idea because it changes the underlying values in your worksheet.

Excel automatically applies a built-in number format to cells based on the following criteria:

• If a number contains a slash (/), it may be converted to a date format or a fraction format

• If a number contains a hyphen (-), it may be converted to a date format

• If a number contains a colon (:), it may be converted to a date format or if followed by a space and the letter A or P (upper or lower), it may be converted to a time format

• If a number contains the letter E (upper or lower), it may be converted to scientific notation (e.g. 3.0E3). If the number doesn’t fit into the column width, it may also be converted to this format.

2.1.1  Formatting numbers by using the Ribbon

The Number group contains some buttons that apply various formats to the active cell. In addition, the Number Format drop-down control provides 11 common number formats (Figure 1.5).

Fig%202.1.jpg

Figure 2.1

2.1.2  Using shortcut keys to format numbers

Table 2.1

2.1.3  Using the Format Cell dialog box to format numbers

To access the Format Cell dialog box, you can use any of the following:

• Click the dialog box launcher at the bottom right of the Home Number group

• Choose Home 25624.png Number 52481.png Number Format More Number Format

• Press Ctrl + 1

2.2  Creating a Custom Number Format

Custom number formats can be used to format any cell in the workbook. Figure 2.2 shows the Custom category in the Number tab of the Format Cells dialog box.

Fig%202.2.jpg

Figure 2.2

A custom number format consists of a format string made up of four-part codes separated by semicolons. The following is a simple number format code:

0.000

The code consists of placeholders and a decimal point; it tells Excel to display the value with three digits to the right of the decimal point.

2.2.1  Components of a number format string

A custom number format can have up to four sections which enable you to specify different format codes for positive numbers, negative numbers, zero values, and text. These codes must be separated by semicolons. The codes are arranged in the following order:

Positive format; Negative format; Zero format; Text format

You don’t have to use all four sections. Table 2.2 below explains how Excel applies these codes to format cells:

Table 2.2

Table 2.3

The following is an example of custom number format that specifies a different format for each of these types:

[Green]General;[Red]General;[Black]General;[Blue]General

2.2.2  Custom number format codes

The following table lists the formatting codes available for custom formats, along with brief descriptions

Table 2.4

Note: The default number format is General. If you prefer a different default format for your workbook, you have two choices: Preformat the cells (the whole sheet, for example) with the number format of your choice., or change the number format for the Normal style. Change the Normal style by displaying the Style gallery. Right-click the Normal style icon (Home Cell Style group) and choose Modify to display the Style dialog box. In the Style dialog box, click the Format button and then choose the new number format that you want to use for the Normal style.

The following table lists the codes that are used for creating custom formats for Dates and Times

Table 2.5