Creating a Spreadsheet from Scratch

The ins and outs of data entry

Here are a few simple guidelines (a kind of data-entry etiquette, if you will) to keep in mind when you create a spreadsheet in Sheet1 of your new blank workbook:

• Whenever you can, organize your information in tables of data that use adjacent (neighboring) columns and rows. Start the tables in the upper-left corner of the worksheet and work your way down the sheet, rather than across the sheet, whenever possible. When it’s practical, separate each table by no more than a single column or row.
• When you set up these tables, don’t skip columns and rows just to “space out” the information. In Chapter 3, you see how to place as much white space as you want between information in adjacent columns and rows by widening columns, heightening rows, and changing the alignment.
• Reserve a single column at the left edge of the table for the table’s row headings.
• Reserve a single row at the top of the table for the table’s column headings.
• If your table requires a title, put the title in the row above the column headings. Put the title in the same column as the row headings. You can get information on how to center this title across the columns of the entire table in Chapter 3.

In Chapter 1, I make a big deal about how big each of the worksheets in a workbook is. You may wonder why I’m now on your case about not using that space to spread out the data that you enter into it. After all, given all the real estate that comes with each Excel worksheet, you’d think conserving space would be one of the last things you’d have to worry about.

You’d be 100 percent correct … except for one little, itty-bitty thing: Space conservation in the worksheet equals memory conservation. You see, while a table of data grows and expands into columns and rows in new areas of the worksheet, Excel decides that it had better reserve a certain amount of computer memory and hold it open just in case you go crazy and fill that area with cell entries. Therefore, if you skip columns and rows that you really don’t need to skip (just to cut down on all that cluttered data), you end up wasting computer memory that could store more information in the worksheet.

You must remember this …

Now you know: The amount of computer memory available to Excel determines the ultimate size of the spreadsheet you can build, not the total number of cells in the worksheets of your workbook. Should you run out of memory, you effectively run out of space — no matter how many columns and rows are still available. To maximize the information you can get into a single worksheet, always adopt the “covered wagon” approach to worksheet design by keeping your data close together.

The telltale signs of text

A text entry is simply an entry that Excel can’t pigeonhole as either a formula or value. This makes text the catchall category of Excel data types. As a practical rule, most text entries (also known as labels) are a combination of letters and punctuation or letters and numbers. Text is used mostly for titles, headings, and notes in the worksheet.

You can tell right away whether Excel has accepted a cell entry as text because text entries automatically align at the left edge of their cells. If the text entry is wider than the cell can display, the data spills into the neighboring cell or cells on the right, as long as those cells remain blank.

If, sometime later, you enter information in a cell that contains spillover text from a cell to its left, Excel cuts off the spillover of the long text entry (see Figure 2-3). Not to worry: Excel doesn’t actually lop these characters off the cell entry — it simply shaves the display to make room for the new entry. To redisplay the seemingly missing portion of the long text entry, you have to widen the column that contains the cell where the text is entered. (To find out how to do this, skip ahead to Chapter 3.)

How Excel evaluates its values

Values are the building blocks of most of the formulas that you create in Excel. As such, values come in two flavors: numbers that represent quantities (14 stores or $140,000 dollars) and numbers that represent dates (July 30, 2005) or times (2 p.m.).

You can tell whether Excel has accepted your entry as a value because values automatically align at the right edge of their cells. If the value that you enter is wider than the column containing the cell can display, Excel automatically converts the value to (of all things) scientific notation, as in 1.2E+10 when you enter 12 billion in a standard width cell (that’s 12 plus nine zeros). To restore a value that’s been converted into that weird scientific notation stuff to a regular number, simply widen the column for that cell. (Read how in Chapter 3.)

Tapping on the old ten-key

You can make the Fixed Decimal feature work even better when entering numeric data on a physical keyboard that has a separate ten-key numeric keypad. All you do is select the block of cells where you want to enter numbers (see “Entries all around the block,” later in this chapter) and then press Num Lock so that you can enter all the data for this cell selection from the numeric keypad (à la ten-key adding machine).

Using this approach, all you have to do to enter the range of values in each cell is type the number’s digits and press Enter on the numeric keypad. Excel inserts the decimal point in the proper place while it moves the cell pointer down to the next cell. Even better, when you finish entering the last value in a column, pressing Enter automatically moves the cell pointer to the cell at the top of the next column in the selection.

Look at Figures 2-4 and 2-5 to see how you can make the ten-key method work for you. In Figure 2-4, the Fixed Decimal feature is turned on (using the default of two decimal places), and the block of cells from B3 through D9 is selected. You also see that six entries have already been made in cells B3 through B8 and a seventh, 30834.63, is about to be completed in cell B9. To make this entry when the Fixed Decimal feature is on, you simply type 3083463 from the numeric keypad.

In Figure 2-5, check out what happens when you press Enter (on either the regular keyboard or the numeric keypad). Not only does Excel automatically add the decimal point to the value in cell B9, but it also moves the cell pointer up and over to cell C3 where you can continue entering the values for this column.

Fabricating those fabulous formulas!

As entries go in Excel, formulas are the real workhorses of the worksheet. If you set up a formula properly, it computes the correct answer when you enter the formula into a cell. From then on, the formula stays up to date, recalculating the results whenever you change any of the values that the formula uses.

You let Excel know that you’re about to enter a formula (rather than some text or a value) in the current cell by starting the formula with the equal sign (=). Most simple formulas follow the equal sign with a built-in function, such as SUM or AVERAGE. (See the section “Inserting a function into a formula with the Insert Function button,” later in this chapter, for more information on using functions in formulas.) Other simple formulas use a series of values or cell references that contain values separated by one or more of the following mathematical operators:

• + (plus sign) for addition
• – (minus sign or hyphen) for subtraction
• * (asterisk) for multiplication
• / (slash) for division
• ^ (caret) for raising a number to an exponential power

For example, to create a formula in cell C2 that multiplies a value entered in cell A2 by a value in cell B2, enter the following formula in cell C2: =A2*B2.

To enter this formula in cell C2, follow these steps:

1. Select cell C2.
2. Type the entire formula =A2*B2 in the cell.
3. Press Enter.

Or

1. Select cell C2.
2. Type = (equal sign).

3. Select cell A2 in the worksheet by using the mouse or the keyboard.

   This action places the cell reference A2 in the formula in the cell (as shown in Figure 2-6).

4. Type * (Shift+8 on the top row of the keyboard).

   The asterisk is used for multiplication rather than the × symbol you used in school.

5. Select cell B2 in the worksheet with the mouse, keyboard, or by tapping it on the screen (when using a touchscreen device).

   This action places the cell reference B2 in the formula (as shown in Figure 2-7).

6. Click the Enter button on the Formula bar to complete the formula entry while keeping the cell pointer in cell C2.

   Excel displays the calculated answer in cell C2 and the formula =A2*B2 in the Formula bar (as shown in Figure 2-8).

When you finish entering the formula =A2*B2 in cell C2 of the worksheet, Excel displays the calculated result, depending on the values currently entered in cells A2 and B2. The major strength of the electronic spreadsheet is the capability of formulas to change their calculated results automatically to match changes in the cells referenced by the formulas.

Now comes the fun part: After creating a formula like the preceding one that refers to the values in certain cells (rather than containing those values itself), you can change the values in those cells, and Excel automatically recalculates the formula, using these new values and displaying the updated answer in the worksheet! Using the example shown in Figure 2-8, suppose that you change the value in cell B2 from 100 to 50. The moment that you complete this change in cell B2, Excel recalculates the formula and displays the new answer, 1000, in cell C2.

If you want it, just point it out

The method of selecting the cells you use in a formula, rather than typing their cell references, is pointing. On most devices on which you're running Excel 2019, pointing is quicker than typing and certainly reduces the risk that you might mistype a cell reference. When you type a cell reference, you can easily type the wrong column letter or row number and not realize your mistake by looking at the calculated result returned in the cell. But when you directly select the cell that you want to use in a formula (by clicking or tapping it or even using the arrow keys to move the cell cursor to it), you have less chance of entering the wrong cell reference.

On a small touchscreen device with a tiny viewing area such as a small tablet or even smartphone, sliding to scroll to the proper column and row and then tapping the cell to select and add its reference to a new formula may be even more challenging than typing the formula’s cell references on the device’s virtual keyboard. This is when I recommend typing instead of pointing for creating new formulas. Just be aware that when you type the first letter of your cell’s column reference into a formula, Excel automatically displays a list of all the built-in functions whose names start with that letter. This list immediately disappears as soon as you type the second letter of the column (if the cell has one) or the first digit of its row number. Also, be sure to double-check that the cell references you type into the formula refer to the cells you really want to use.

Altering the natural order of operations

Many formulas that you create perform more than one mathematical operation. Excel performs each operation, moving from left to right, according to a strict pecking order (the natural order of arithmetic operations). In this order, multiplication and division pull more weight than addition and subtraction and, therefore, perform first, even if these operations don’t come first in the formula (when reading from left to right).

Consider the series of operations in the following formula:

=A2+B2*C2

If cell A2 contains the number 5, B2 contains the number 10, and C2 contains the number 2, Excel evaluates the following formula:

=5+10*2

In this formula, Excel multiplies 10 times 2 to equal 20 and then adds this result to 5 to produce the result 25.

If you want Excel to perform the addition between the values in cells A2 and B2 before the program multiplies the result by the value in cell C2, enclose the addition operation in parentheses as follows:

=(A2+B2)*C2

The parentheses around the addition tell Excel that you want this operation performed before the multiplication. If cell A2 contains the number 5, B2 contains the number 10, and C2 contains the number 2, Excel adds 5 and 10 to equal 15 and then multiplies this result by 2 to produce the result 30.

In fancier formulas, you may need to add more than one set of parentheses, one within another (like the wooden Russian dolls that nest within each other) to indicate the order in which you want the calculations to take place. When nesting parentheses, Excel first performs the calculation contained in the most inside pair of parentheses and then uses that result in further calculations as the program works its way outward. For example, consider the following formula:

=(A4+(B4–C4))*D4

Excel first subtracts the value in cell C4 from the value in cell B4, adds the difference to the value in cell A4, and then finally multiplies that sum by the value in D4.

Without the additions of the two sets of nested parentheses, left to its own devices, Excel would first multiply the value in cell C4 by that in D4, add the value in A4 to that in B4, and then perform the subtraction.

Don't worry too much when nesting parentheses in a formula if you don’t pair them properly so that you have a right parenthesis for every left parenthesis in the formula. If you do not include a right parenthesis for every left one, Excel displays an alert dialog box that suggests the correction needed to balance the pairs. If you agree with the program’s suggested correction, you simply click the Yes button. However, be sure that you only use parentheses: ( ). Excel balks at the use of brackets — [ ] — or braces — { } — in a formula by giving you an Error alert box.

Formula flub-ups

Under certain circumstances, even the best formulas can appear to have freaked out after you get them in your worksheet. You can tell right away that a formula’s gone haywire because instead of the nice calculated value you expected to see in the cell, you get a strange, incomprehensible message in all uppercase letters beginning with the number sign (#) and ending with an exclamation point (!) or, in one case, a question mark (?). This weirdness, in the parlance of spreadsheets, is as an error value. Its purpose is to let you know that some element — either in the formula itself or in a cell referred to by the formula — is preventing Excel from returning the anticipated calculated value.

When one of your formulas returns one of these error values, an alert indicator (in the form of an exclamation point in a diamond) appears to the left of the cell when it contains the cell pointer, and the upper-left corner of the cell contains a tiny green triangle. When you position the mouse pointer on this alert indicator, Excel displays a brief description of the formula error and adds a drop-down button to the immediate right of its box. When you click this button, a pop-up menu appears with a number of related options. To access online help on this formula error, including suggestions on how to get rid of the error, click the Help on This Error item on this pop-up menu.

The worst thing about error values is that they can contaminate other formulas in the worksheet. If a formula returns an error value to a cell and a second formula in another cell refers to the value calculated by the first formula, the second formula returns the same error value, and so on down the line.

After an error value shows up in a cell, you have to discover what caused the error and edit the formula in the worksheet. In Table 2-1, I list some error values that you might run into in a worksheet and then explain the most common causes.

You really AutoCorrect that for me

The AutoCorrect feature is a godsend for those of us who tend to make the same stupid typos over and over. With AutoCorrect, you can alert Excel 2019 (along with the other Office 2019 apps you use, such as Word and PowerPoint) to your own particular typing gaffes and tell the program how it should automatically fix them for you.

When you first install Excel, the AutoCorrect feature already knows to automatically correct two initial capital letters in an entry (by lowercasing the second capital letter), to capitalize the name of the days of the week, and to replace a set of predefined text entries and typos with particular substitute text.

You can add to the list of text replacements at any time when using Excel. These text replacements can be of two types: typos that you routinely make along with the correct spelling, and abbreviations or acronyms that you type all the time along with their full forms.

To add to the replacements, follow these steps:

1. Click File ⇒ Options ⇒ Proofing or press Alt+FTP and then click the AutoCorrect Options button or press Alt+A.

   Excel opens the AutoCorrect dialog box shown in Figure 2-9.

2. On the AutoCorrect tab in this dialog box, enter the typo or abbreviation in the Replace text box.
3. Enter the correction or full form in the With text box.
4. Click the Add button or press Enter to add the new typo or abbreviation to the AutoCorrect list.
5. Click the OK button to close the AutoCorrect dialog box.

Cell editing etiquette

Despite the help of AutoCorrect, some mistakes are bound to get you. How you correct them really depends upon whether you notice before or after completing the cell entry.

• If you catch the mistake before you complete an entry, you can delete it by pressing your Backspace key until you remove all the incorrect characters from the cell. Then you can retype the rest of the entry or the formula before you complete the entry in the cell.
• If you don’t discover the mistake until after you’ve completed the cell entry, you have a choice of replacing the whole thing or editing just the mistakes.
• When dealing with short entries, you’ll probably want to take the replacement route. To replace a cell entry, position the cell pointer in that cell, type your replacement entry, and then click the Enter button or press Enter.
• When the error in an entry is relatively easy to fix and the entry is on the long side, you’ll probably want to edit the cell entry rather than replace it. To edit the entry in the cell, simply double-click or double-tap the cell or select the cell and then press F2.
• Doing either one reactivates the Formula bar and the Enter and Cancel buttons once again and placing the insertion point in the cell entry in the worksheet. (If you double-click or double-tap, the insertion point positions itself wherever you click; press F2, and the insertion point positions itself after the last character in the entry.)
• Notice also that the mode indicator changes to Edit. While in this mode, you can use the mouse or the arrow keys to position the insertion point at the place in the cell entry that needs fixing.

In Table 2-2, I list the keystrokes that you can use to reposition the insertion point in the cell entry and delete unwanted characters. If you want to insert new characters at the insertion point, simply start typing. If you want to delete existing characters at the insertion point while you type new ones, press the Insert key on your keyboard to switch from the normal insert mode to overtype mode. To return to normal insert mode, press Insert a second time. When you finish making corrections to the cell entry, you must complete the edits by pressing Enter before Excel updates the contents of the cell.

While Excel is in Edit mode, you must re-enter the edited cell contents by either clicking the Enter button on the Formula bar or pressing the Enter key or pressing Tab or the Shift+Tab combination. You can use the arrow keys as a way to complete an entry only when the program is in Enter mode. When the program is in Edit mode, the arrow keys move the insertion point only through the entry that you’re editing, not to a new cell.

I’m just not complete without you

AutoComplete in Excel 2019 is a sometimes helpful feature that you need to be aware as you enter new data in a worksheet. In an attempt to cut down on your typing load, our friendly software engineers at Microsoft came up with the AutoComplete feature.

AutoComplete tries to act like a mind reader, anticipating what you might want to enter next based on existing entries made in the same worksheet column. This feature comes into play only when you’re entering a column of text entries. (It does not come into play when entering values or formulas or when entering a row of text entries.)

When entering a column of text entries, AutoComplete looks at the kinds of text entries that you make in that column and automatically enables you to make duplicates of them in subsequent rows as soon as you type sufficient letters uniquely matching an earlier entry. Excel does this by completing the letters in new cell entry (which are selected shown by gray shading) to make it match an earlier entry. You can then accept this suggestion and complete the duplicate entry (by pressing Enter or an arrow key or clicking the Enter button) or override it by continuing to type the missing letters of a different entry that starts with the same letter(s).

For example, suppose that I enter Capital Investments (one of the many investment firms that our company uses) in cell A2 and then move the cell pointer down to cell A3 in the row below and press C (lowercase or uppercase, it doesn’t matter). AutoComplete immediately fills in the remainder of the familiar entry — apital Investments — in this cell after the C. To enter the suggested duplicate in this cell, all I have to do is press Enter or the down or right arrow key or click the Enter button on the Formula bar.

Now this is great if I happen to need Capital Investments as the row heading in both cells A2 and A3. Anticipating that I might be typing a different entry that just happens to start with the same letter as the one above, AutoComplete automatically selects everything after the first letter in the suggested duplicated entry it filled in (from apital on, in this example) indicated by gray-shading these letters. This enables me to replace the suggested duplicate text supplied by AutoComplete just by continuing to type nonmatching letters.

If you override a duplicate supplied by AutoComplete in a column by typing one of your own (as in the example of the Capital Investments entry automatically corrected to Cook Investments in cell A3), you effectively shut down its capability to supply any more suggested duplicates for that particular letter (in this case, C). For instance, in my example, after changing Capital Investments to Cook Investments in cell A3, AutoComplete doesn’t do anything if I then just type C in cell A4. AutoComplete doesn’t kick in again until I type a new entry that begins with either Ca or Co in rows below in column A.

If you find that the AutoComplete feature is really making it hard for you to enter a series of cell entries that all start with the same or similar letters but are otherwise not alike, you can turn off the AutoComplete feature. Select File ⇒ Options ⇒ Advanced or press Alt+FTA to open the Advanced tab of the Excel Options dialog box. Then, select the Enable AutoComplete for Cell Values check box in the Editing Options section to remove its check mark before clicking OK.

Fill ’er up with AutoFill

Many of the worksheets that you create with Excel require the entry of a series of sequential dates or numbers. For example, a worksheet may require you to title the columns with the 12 months, from January through December, or to number the rows from 1 to 100.

Excel’s AutoFill feature makes short work of this kind of repetitive task. All you have to enter is the initial value in that series. In most cases, AutoFill is smart enough to figure out how to fill out the series for you when you drag the fill handle to the right (to take the series across columns to the right) or down (to extend the series to the rows below).

The AutoFill (or fill) handle looks like this — + — and appears only when you position the mouse on the lower-right corner of the active cell (or the last cell, when you’ve selected a block of cells). If you drag a cell selection with the white-cross mouse pointer rather than the AutoFill handle, Excel simply extends the cell selection to those cells you drag through (see Chapter 3). If you drag a cell selection with the arrowhead pointer, Excel moves the cell selection (see Chapter 4).

On a touchscreen device, you make the Excel fill handle appear by tapping the active cell’s lower selection handle (indicated by a circle) and then tapping the AutoFill item on the mini-toolbar that then appears. You can then fill a series by dragging this fill handle with your finger or a stylus.

When creating a series with the fill handle, you can drag in only one direction at a time. For example, you can fill the series or copy the entry to the range to the left or right of the cell that contains the initial values, or you can fill the series or copy to the range above or below the cell containing the initial values. You can’t, however, fill or copy the series to two directions at the same time (such as down and to the right by dragging the fill handle diagonally).

As you drag the fill handle, the program keeps you informed of whatever entry will be entered into the last cell selected in the range by displaying that entry next to the mouse pointer (a kind of AutoFill tips, if you will). After extending the range with the fill handle, Excel either creates a series in all of the cells that you select or copies the entire range with the initial value. To the right of the last entry in the filled or copied series, Excel also displays a drop-down button that contains a shortcut menu of options. You can use this shortcut menu to override Excel’s default filling or copying. For example, when you use the fill handle, Excel copies an initial value (such as 10) into every cell in the range. But, if you wanted a sequential series (such as 10, 11, 12, and so on), you do this by selecting the Fill Series command on the AutoFill Options shortcut menu.

In Figures 2-10 and 2-11, I illustrate how to use AutoFill to enter a row of months, starting with January in cell B2 and ending with June in cell G2. To do this, you simply enter Jan in cell B2 and then position the mouse pointer (or your finger or stylus) on the fill handle in the lower-right corner of this cell before you drag through to cell G2 on the right (as shown in Figure 2-10). When you release the mouse button or remove your finger or stylus from the touchscreen, Excel fills in the names of the rest of the months (Feb through Jun) in the selected cells (as shown in Figure 2-11). Excel keeps the cells with the series of months selected, giving you another chance to modify the series. (If you went too far, you can drag the fill handle to the left to cut back on the list of months; if you didn’t go far enough, you can drag it to the right to extend the list of months farther.)

Also, you can use the options on the AutoFill Options drop-down menu shown in Figure 2-11. To display this menu, you click the drop-down button that appears on the fill handle (to the right of Jun) to override the series created by default. To have Excel copy Jan into each of the selected cells, select Copy Cells on this menu. To have the program fill the selected cells with the formatting used in cell B2 (in this case, the cell has had bold applied to it — see Chapter 3 for details on formatting cells), you select Fill Formatting Only on this menu. To have Excel fill in the series of months in the selected cells without copying the formatting used in cell B2, you select the Fill Without Formatting command from this shortcut menu.

See Table 2-3 to see some of the different initial values that AutoFill can use and the types of series that Excel can create from them.

Creating custom lists for AutoFill

In addition to varying the increment in a series created with AutoFill, you can also create your own custom series. For example, say your company has offices in the following locations and you get tired of typing the sequence in each new spreadsheet that requires them:

• New York
• Chicago
• Atlanta
• New Orleans
• San Francisco
• Los Angeles

After creating a custom list with these locations, you can enter the entire sequence of cities simply by entering New York in the first cell and then dragging the Fill handle to the blank cells where the rest of the cities should appear.

To create this kind of custom series, follow these steps:

1. Click File ⇒ Options ⇒ Advanced or press Alt+FTA and then scroll down and click the Edit Custom Lists button in the General section to open the Custom Lists dialog box (as shown in Figure 2-12).

   If you’ve already gone to the time and trouble of typing the custom list in a range of cells, go to Step 2. If you haven’t yet typed the series in an open worksheet, go to Step 4.

2. Click in the Import List from Cells text box and then select the range of cells in the worksheet containing the custom list (see Chapter 3 for details).

   As soon as you start selecting the cells in the worksheet by dragging your mouse or Touch pointer, Excel automatically collapses the Options dialog box to the minimum to get out of the way. The moment you release the mouse button or remove your finger or stylus from the screen, Excel automatically restores the Options dialog box to its normal size.

3. Click the Import button to copy this list into the List Entries list box.

   Skip to Step 6.

4. Select the List Entries list box and then type each entry (in the desired order), being sure to press Enter after typing each one.

   When all the entries in the custom list appear in the List Entries list box in the order you want them, proceed to Step 5.

5. Click the Add button to add the list of entries to the Custom Lists list box.

   Finish creating all the custom lists you need, using the preceding steps. When you’re done, move to Step 6.

6. Click OK twice, the first time to close the Custom Lists dialog box and the second to close the Excel Options dialog box and return to the current worksheet in the active workbook.

After adding a custom list to Excel, from then on you need only enter the first entry in a cell and then use the fill handle to extend it to the cells below or to the right.

If you don’t even want to bother with typing the first entry, use the AutoCorrect feature — refer to the section “You really AutoCorrect that for me,” earlier in this chapter — to create an entry that fills in as soon as you type your favorite acronym for it (such as ny for New York).

Fill it in a flash

Excel 2019’s Flash Fill feature gives you the ability to take a part of the data entered into one column of a worksheet table and enter just that data in a new table column using only a few keystrokes. The series of entries appears in the new column, literally in a flash (thus its name, Flash Fill). The second Excel 2019 detects a pattern in your initial data entries, the rest of the entries in that series immediately appears in blank cells in rows below that you can then enter with a single keystroke. And the beauty is that all this happens without the need for you to construct or copy any kind of formula.

The best way to understand Flash Fill is to see it in action. In Figure 2-13, you see a new data table consisting of four columns. The cells in the first column of this table contain the full names of clients (first, middle, and last), all together in one entry. The second, third, and fourth columns need to have just the first, middle, and surnames, respectively, entered into them (so that particular parts of the clients’ names can be used in the greetings of form e-mails and letters as in, “Hello Keith,” or “Dear Mr. Harper,”).

Rather than manually enter the first, middle, or last names in the respective columns (or attempt to copy the entire client name from column A and then edit out the parts not needed in First Name, Middle Name, and Last Name columns), you can use Flash Fill to quickly and effectively do the job. And here’s how you do it:

1. Type Keith in cell B2 and complete the entry with the ↓ or Enter key.

   When you complete this entry with the down-arrow key or Enter key on your keyboard, Excel moves the cell pointer to cell B3 where you only have to type the first letter of the next name for Flash Fill to get the picture.

2. In Cell B3, only type J, the first letter of the second client’s first name.

   Flash Fill immediately does an AutoFill-type maneuver by suggesting the rest of the second client’s first name, Jonas, as the text to enter in this cell. At the same time, Flash Fill suggests entering all the remaining first names from the full names in column A in column B (see Figure 2-13).

3. Complete the entry of Jonas in cell B3 by pressing the Enter key or an arrow key.

   The moment you complete the data entry in cell B3, the First Name column’s done: Excel enters all the other first names in column B at the same time (take that, Barry Allen)!

To complete this example name table by entering the middle and last names in columns C and D, respectively, you simply repeat these steps in those columns. You enter the first middle name, Austen, from cell A2 in cell C2 and then type W in cell C3. Complete the entry in cell C3 and the middle name entries in that column are done. Likewise, you enter the first last name, Harper, from cell A2 in cell D2 and then type S in cell D3. Complete the entry in cell D3, and the last name entries for column D are done, completing the entire data table.

By my count, completing the data entry in this Client Name table required me to make a total of 26 keystrokes, 20 of which were for typing in the first, middle, and last name of the first client along with the initial letters of the first, middle, and last name of the second client and the other six to complete these entries. If Column A of this Client Name table contains the full names of hundreds or even thousands of clients, these 26 keystrokes are insignificant compared to the number that would be required to manually enter their first, middle, and last names in its separate First Name, Middle Name, and Last Name columns or even to edit down copies of the full names in each of them.

Keep in mind that Flash Fill works perfectly at extracting parts of longer data entries in a column provided that all the entries follow the same pattern and use the same type of separators (spaces, commas, dashes, and the like). For example, in Figure 2-13, there’s an anomaly in the full name entries in cell A9 where only the middle initial with a period is entered instead of the full middle name. In this case, Flash Fill simply enters M in cell C9 and you have to manually edit its entry to add the necessary period. Also, remember that Flash Fill’s usefulness isn’t restricted to all-text entries as in my example Client Name table. It can also parse entries that mix text and numbers such as part numbers (AJ-1234, RW-8007, and so forth).

Inserting special symbols

Excel makes it easy to enter special symbols, such as foreign currency indicators, and special characters, such as the trademark and copyright symbols, into your cell entries. To add a special symbol or character to a cell entry you’re making or editing, select Insert ⇒ Symbol on the Ribbon or press Alt+NU to open the Symbol dialog box.

The Symbol dialog box contains two tabs: Symbols and Special Characters. To insert a mathematical or foreign currency symbol on the Symbols tab, select its symbol in the list box and then click the Insert button. (You can also do this by double-clicking or double-tapping the symbol.) To insert characters, such as foreign language or accented characters from other character sets, click the Subset drop-down button followed by the name of the set in the drop-down list and the desired characters in the list box. You can often insert commonly used currency and mathematical symbols, such as the pound or plus-or-minus symbol, by selecting them directly from the Recently Used Symbols section at the bottom of this tab, provided that other symbols that you’ve inserted of late haven’t replaced them.

To insert special characters, such as the registered trademark, paragraph symbol, and so forth, you click the Special Characters tab of the Symbol dialog box followed by the symbol in the scrolling list and the Insert button. (You can insert one of these special characters by double-clicking or double-tapping it also.)

When you finish inserting special symbols and characters, close the Symbol dialog box by pressing Esc or clicking the Close button with an X in its upper-right corner.

Entries all around the block

When you want to enter a table of information in a new worksheet, you can simplify the job of entering the data if you select all the empty cells in which you want to make entries before you begin entering any information. Just position the cell pointer in the first cell of what is to become the data table and then select all the cells in the subsequent columns and rows. (For information on the ways to select a range of cells, see Chapter 3.) After you select the block of cells, you can begin entering the first entry.

When you select a block of cells (also known as a range) before you enter information, Excel restricts data entry to that range as follows:

• The program automatically advances the cell pointer to the next cell in the range when you click the Enter button on the Formula bar or press the Enter key to complete each cell entry.
• In a cell range that contains several different rows and columns, Excel advances the cell pointer down each row of the column while you make your entries. When the cell pointer reaches the cell in the last row of the column, the cell pointer advances to the first selected row in the next column to the right. If the cell range uses only one row, Excel advances the cell pointer from left to right across the row.
• When you finish entering information in the last cell in the selected range, Excel positions the cell pointer in the first cell of the now-completed data table. To deselect the cell range, select a single cell in the worksheet (inside or outside the selected range — it doesn’t matter) or press one of the arrow keys.

Be sure that you don’t press one of the arrow keys to complete a cell entry within a preselected cell range instead of clicking the Enter button or pressing Enter. Pressing an arrow key deselects the range of cells when Excel moves the cell pointer. To move the cell pointer around a cell range without deselecting the range, try these methods:

• Press Enter to advance to the next cell down each row and then across each column in the range. Press Shift+Enter to move up to the previous cell.
• Press Tab to advance to the next cell in the column on the right and then down each row of the range. Press Shift+Tab to move left to the previous cell.
• Press Ctrl+. (period) to move from one corner of the range to another.

Data entry express

You can save a lot of time and energy when you want the same entry (text, value, or formula) to appear in many cells of the worksheet; you can enter the information in all the cells in one operation. You first select the cell ranges to hold the information. (Excel lets you select more than one cell range for this kind of thing — see Chapter 3 for details.) Then you construct the entry on the Formula bar and press Ctrl+Enter to put the entry into all the selected ranges.

The key to making this operation a success on a physical keyboard is to hold the Ctrl key down while you press Enter so that Excel inserts the entry on the Formula bar into all the selected cells. If you forget to hold Ctrl and you just press Enter, Excel places the entry in the first cell only of the selected cell range. On the Windows virtual keyboard, you just tap Ctrl followed by Enter in succession.

You can also speed up data entry in a list that includes formulas by making sure that the Extend Data Range Formats and Formulas check box is selected in the Editing Options section of the Advanced tab in the Excel Options dialog box (click File ⇒ Options ⇒ Advanced or press Alt+FTA). When this check box is selected, Excel automatically formats new data that you type in the last row of a list to match that of like data in earlier rows and copies down formulas that appear in the preceding rows. Note, however, that for this feature to kick in, you must manually enter the formulas and format the data entries in at least three rows preceding the new row.

Inserting a function into a formula with the Insert Function button

Although you can enter a function by typing it directly in a cell, Excel provides an Insert Function command button on the Formula bar you can use to select any of Excel’s functions. When you select this button, Excel opens the Insert Function dialog box (shown in Figure 2-14) where you can select the function you want to use. After you select your function, Excel opens the Function Arguments dialog box. In this dialog box, you can specify the function arguments. The real boon comes when you’re starting to use an unfamiliar function or one that’s kind of complex (some of these puppies can be hairy). You can get loads of help in completing the argument text boxes in the Function Arguments dialog box by clicking the Help on This Function link in the lower-left corner.

The Insert Function dialog box contains three boxes: a Search for a Function text box, an Or Select a Category drop-down list box, and a Select a Function list box. When you open the Insert Function dialog box, Excel automatically selects Most Recently Used as the category in the Select a Category drop-down list box and displays the functions you usually use in the Select a Function list box.

If your function isn’t among the most recently used, you must then select the appropriate category of your function in the Select a Category drop-down list box. If you don’t know the category, you must search for the function by typing a description of its purpose in the Search for a Function text box and then press Enter or click the Go button. For example, to locate all the Excel functions that total values, you enter total in the Search for a Function list box and click the Go button. Excel then displays its list of recommended functions for calculating totals in the Select a Function list box. You can peruse the recommended functions by selecting each one. While you select each function in this list, the Insert Function dialog box shows you the required arguments followed by a description, at the bottom of the dialog box, of what the function does.

After you locate and select the function that you want to use, click the OK button to insert the function into the current cell and open the Function Arguments dialog box. This dialog box displays the required arguments for the function along with any that are optional. For example, suppose that you select the SUM function (the crown jewel of the Most Recently Used function category) in the Select a Function list box and then select OK. As soon as you do, the program inserts

SUM()

in the current cell and on the Formula bar (following the equal sign), and the Function Arguments dialog box showing the SUM arguments appears on the screen (as shown in Figure 2-15). This is where you add the arguments for the SUM function.

As shown in Figure 2-15, you can sum up to 255 numbers in the Function Arguments dialog box. What's not obvious, however (there’s always some trick, huh?), is that these numbers don’t have to be in single cells. In fact, most of the time you’ll be selecting a whole slew of numbers in nearby cells (in a multiple cell selection — that range thing) that you want to total.

To select your first number argument in the dialog box, you select the cell (or block of cells) in the worksheet while the insertion point is in the Number1 text box. Excel then displays the cell address (or range address) in the Number1 text box while, at the same time, showing the value in the cell (or values, if you select a bunch of cells) in the box to the right. Excel displays the total near the bottom of the Function Arguments dialog box after the words Formula result=.

When selecting cells, you can minimize this arguments dialog box to just the contents of the Number1 text box by dragging the cell pointer through the cells to sum in the worksheet. After you minimize the arguments dialog box while selecting the cells for the first argument, you can then expand it again by releasing the mouse button.

You can also reduce the dialog box to the Number1 argument text box by clicking the Minimize Dialog Box button on the right of the text box, selecting the cells, and then clicking the Maximize Dialog Box button (the only button displayed on the far right) or by pressing the Esc key. Instead of minimizing the dialog box, you can also temporarily move it out of the way by clicking on any part and then dragging the dialog box to its new destination on the screen.

If you’re adding more than one cell (or a bunch of cells) in a worksheet, press the Tab key or click the Number2 text box to move the insertion point to that text box. (Excel responds by extending the argument list with a Number3 text box.) Here is where you specify the second cell (or cell range) to add to the one now showing in the Number1 text box. After you select the cell or second cell range, the program displays the cell address(es), the numbers in the cell(s) to the right, and the running total near the bottom of the Function Arguments dialog box after Formula result= (as shown in Figure 2-15). You can minimize the entire Function Arguments dialog box down to just the contents of the argument text box you’re dealing with (Number2, Number3, and so on) by clicking its particular Minimize Dialog Box button if the dialog box obscures the cells that you need to select.

When you finish pointing out the cells or bunch of cells to sum, click the OK button to close the Function Arguments dialog box and put the SUM function in the current cell.

Editing a function with the Insert Function button

You can also use the Insert Function button to edit formulas that contain functions right from the Formula bar. Select the cell with the formula and function to edit before you select the Insert Function button (the one sporting the fx that appears immediately in front of the current cell entry on the Formula bar).

As soon as you select the Insert Function button, Excel opens the Function Arguments dialog box where you can edit its arguments. To edit just the arguments of a function, select the cell references in the appropriate argument’s text box (marked Number1, Number2, Number3, and so on) and then make whatever changes are required to the cell addresses or select a new range of cells.

Excel automatically adds any cell or cell range that you highlight in the worksheet to the current argument. If you want to replace the current argument, you need to highlight it and remove its cell addresses by pressing the Delete key before you highlight the new cell or cell range to use as the argument. (Remember that you can always minimize this dialog box or move it to a new location if it obscures the cells you need to select.)

When you finish editing the function, press Enter or click the OK button in the Function Arguments dialog box to put it away and update the formula in the worksheet.

I’d be totally lost without AutoSum

Before leaving this fascinating discussion on entering functions, I want you to get to the AutoSum tool in the Editing group on the Home tab of the Ribbon. Look for the Greek sigma (Σ) symbol. This little tool is worth its weight in gold. In addition to entering the SUM, AVERAGE, COUNT, MAX, or MIN functions, it also selects the most likely range of cells in the current column or row that you want to use as the function's argument and then automatically enters them as the function’s argument. Nine times out of ten, Excel selects (with the marquee or moving dotted line) the correct cell range to total, average, count, and so forth. For that tenth case, you can manually correct the range by simply dragging the cell pointer through the block of cells to sum.

Simply select the AutoSum button followed by Sum on the drop-down menu on the Home tab when you want to insert the SUM function into the current cell. The quicker method to select this function is to press Alt+= (the Alt key plus the equal to symbol on the top row).

If you want to use the AutoSum button to insert another function, such as AVERAGE, COUNT, MAX, or MIN, you need to click its drop-down button and select the name of the desired function on its pop-up menu (click Count Numbers on the menu to insert the COUNT function). If you select the More Functions command on this menu, Excel opens the Insert Function dialog box as though you had clicked the fx button on the Formula bar.

In Figure 2-16, check out how to use AutoSum to total the sales of Jack Sprat Diet Centers in row 3. Position the cell pointer in cell E3 where the first-quarter total is to appear and then select Sum on the AutoSum drop-down menu (or press Alt+=). Excel inserts SUM (equal sign and all) onto the Formula bar; places a marquee around cells B3, C3, and D3; and uses the cell range B3:D3 as the argument of the SUM function.

Now look at the worksheet after you insert the function in cell E3 (see Figure 2-17). The calculated total appears in cell E3 while the following SUM function formula appears in the Formula bar:

=SUM(B3:D3)

After entering the function to total the sales of Jack Sprat Diet Centers, you can copy this formula to total sales for the rest of the companies by dragging the fill handle down column E until the cell range E3:E11 is highlighted (as shown in Figure 2-17).

Look at Figure 2-18 to see how you can use AutoSum to total the January sales for all the Mother Goose Enterprises in column B. Position the cell pointer in cell B12 where you want the total to appear. Select Sum on the AutoSum's drop-down menu, and Excel places the marquee around cells B3 through B11 and correctly enters the cell range B3:B11 as the argument of the SUM function.

In Figure 2-19, you see the worksheet after inserting the function in cell B12 and using the AutoFill feature to copy the formula to cells C12 and E12 to the right. (To use AutoFill, drag the fill handle through the cells to the right until you reach cell E12. Release the mouse button or remove your finger or stylus from the touchscreen.)

Sums via Quick Analysis Totals

For those of you who don't have the time or patience for adding totals to your worksheet tables with AutoSum and AutoFill, Excel 2019’s Totals feature on the Quick Analysis tool is just the thing. The Quick Analysis tool offers a bevy of features for doing anything from adding conditional formatting (see Chapter 3), charts (see Chapter 10), pivot tables (see Chapter 9), and sparklines (see Chapter 10) to your worksheet tables. And it turns out Quick Analysis is also a whiz at adding running totals and sums to the rows and columns of your new worksheet tables.

To use the Quick Analysis tool, all you have to do is select the worksheet table’s cells (see Chapter 3 for details) and then click the Quick Analysis tool that automatically appears in the lower-right corner of the last selected cell. When you do, a palette of options (from Formatting to Sparklines) appears right beneath the tool.

To add totals to your selected table data, simply click the Totals button. You can then use your mouse or Touch pointer to have Live Preview show you totals in a new row at the bottom by highlighting Running Total or in a new column on the right by highlighting Sum (shown in Figure 2-20). To actually add the SUM formulas with the totals to a new row or column, you simply click the Running Total or Sum button.

To add the running totals to the sample worksheet table shown in Figure 2-20, you simply select the table of data, A2 through D11, and click the Quick Analysis tool followed by the Totals and Running Total buttons. Add a column of quarterly running totals down the rows in the cell range E3:E11 by selecting the Quick Analysis tool again and then selecting Totals followed by the Sum option (displaying the Sigma on a shaded column) that is to the immediate right of the Running option. Finally, enter a Qtr1 Total heading at the top of the column in cell E2, and you’re done!

If you have trouble selecting the Quick Analysis tool to open its palette for any reason, simply select the cells to be calculated and then press Ctrl+Q or right-click the cell selection and click the Quick Analysis item on its context menu.

Changing the default file location

Whenever you open the Save As dialog box to save a new workbook file, Excel 2019 automatically selects the folder listed in the Default Local File Location text box on the Save tab of the Excel Options dialog box (File ⇒ Options ⇒ Save or Alt+FTS).

When you first start using Excel, the default folder is the Documents on your OneDrive. For example, the directory path of the default folder where Excel 2019 automatically saves new workbook files on my computer is

C:\Users\gharv\OneDrive\Documents

The very generic Documents folder may not be the place on your OneDrive where you want all the new workbooks you create automatically saved. To change the default file location to another folder on your computer, follow these steps:

1. Click File ⇒ Options ⇒ Save or press Alt+FTS to open the Save tab of the Excel Options dialog box.

   The Default Local File Location text box displays the directory path to the current default folder.

2. Click in the Default Local File Location text box.

   To edit part of the path (such as the Documents folder name after your username or remove OneDrive to designate a local drive), insert the mouse pointer at that place in the path to set the insertion point.

3. Edit the existing path or replace it with the path to another folder in which you want all future workbooks to save to automatically.
4. Click OK to close the Excel Options dialog box.

The difference between the XLSX and XLS file formats

Excel 2019 supports the use of the XML-based file format first introduced in Excel 2007 (which Microsoft officially calls the Microsoft Office Open XML format). This default file format is touted as being more efficient in saving data, resulting in smaller file size and offering superior integration with external data sources (especially when these resources are web-based ones supporting XML files). This XML-based file format carries the filename extension .xlsx and is the file format in which Excel automatically saves any new workbook you create.

The only problem with this newfangled XML-based file format is that it can't be opened by earlier Excel versions (before Excel 2007). Therefore, if someone who needs to work with the workbook you’ve just created isn’t using Excel 2007 or hasn’t yet upgraded to Excel 2019, you need to save the new workbook in the earlier file format used in Excel versions 97 through 2003 with the old .xls filename extension. To do this, click the Save As Type drop-down button in the Save As dialog box and then click Excel 97-2003 Workbook (*.xls) in the drop-down menu.

Filename extensions, such as .xlsx and .xls, do not appear as part of the filename (even though they are appended) in the File Name text box in the Excel Save As dialog box unless you've specifically changed Windows’ folder options to show them. To make this change in Windows 10, open the File Explorer and then deselect the File Name Extensions check box on the View tab in the dialog box.

Excel 2019 also supports a special binary file format called Excel Binary Workbook that carries the .xlsb filename extension. Select this binary format for huge spreadsheets that you create that have to be compatible with earlier versions of Excel.