Fill cells by using Flash Fill

If you have inherited workbooks from someone else or if you have imported data from external data sources, you have probably come across data that was either structured or formatted (or both) in such a way that it was either difficult to read or difficult to work with. It could be mainframe data that arrives as all-uppercase letters, dates that appear in non-date formats, phone numbers that don’t have dashes or parentheses, or fields that combine multiple pieces of data (such as first names and last names).

One way to tackle such data is to reenter it by hand in the structure or format you prefer or require. That works for a few records, but it gets tedious and time-consuming for dozens of records, and it becomes unworkable for hundreds or thousands of records.

Fortunately, creating your preferred data from existing data can often be done with only minimal work thanks to an Excel feature called Flash Fill. Given a column of original data, if you use the first cell in the next column to enter the corrected data (which could be data extracted from the original cell or the same data formatted in a different way), select the second cell, then run the Flash Fill command, Excel “recognizes” what you’re doing and automatically fills in the rest of the column with the corrected data.

Let’s look at a few examples:

• Column A contains a list of all-uppercase company names. In column B, I use cell B2 to type the title-case version of the text in cell A2 and then select cell B3. After I run the Flash Fill command, Excel recognizes the pattern and fills in the rest of the column with title-case versions of all the other cells in column A.

  

• Column A contains a list of full names, where each cell contains the first name, middle initial, and last name. In cells B2, C2, and D2, I typed the first name, middle initial, and last name, respectively, from cell A2. When I select cell B3 and run Flash Fill, Excel recognizes the pattern and fills in the rest of the first names; similarly, selecting C3 and running Flash Fill adds the middle initials, and selecting D3 and running Flash Fill adds the last names.

  

• Column A contains a list of phone numbers without any parentheses or dashes. In cell B2, I typed the phone number from cell A2 and added the parentheses and dash. When I select cell B3 and run Flash Fill, Excel recognizes the pattern and fills the rest of the column with the formatted phone numbers.

  

To fill cells by using Flash Fill

• ➜   Type the first value you want in the first cell of the new column, select the second cell in the new column, and then on the Data tab, in the Data Tools group, click Flash Fill. Alternatively, on the Home tab, in the Editing group, click Fill, then click Flash Fill.

  Tip

  You can also run the Flash Fill command by pressing Ctrl+E.

Or,

• ➜   Type the first value you want in the first cell of the new column. Starting in the second cell of the new column, drag the Fill handle through all the cells in the new column that you want to fill, click Auto Fill Options, and then click Flash Fill.

Fill cells by using advanced Fill Series options

Worksheets often use text series (such as January, February, March; or Sunday, Monday, Tuesday) and numeric series (such as 1, 3, 5; or 2021, 2020, 2019). Instead of entering these series manually, you can create them automatically by using the Auto Fill feature. That is, you enter and select the first couple of values in the series, drag the fill handle over the range you want to fill, and then click Fill Series in the AutoFill Options list.

Instead of using the fill handle to create a series, you can use the Series command to gain more control over the process. By using the Series command, you can specify a direction for the fill (rows or columns); a step value (the value by which each item in the series is changed to produce the next item); a stop value (the value at which Excel should terminate the series); whether you want the series to extend the trend of the initial values; the date units (such as day or month) for a date series; and the series type, which can be one of the following four values:

• Linear This option finds the next series value by adding the step value to (or subtracting the step value from) the preceding value in the series.

• Growth This option finds the next series value by multiplying the preceding value by the step value.

• Date This option creates a series of dates based on the option you select in the Date Unit group (Day, Weekday, Month, or Year).

• AutoFill This option works much like the fill handle. You can use it to extend a numeric pattern or a text series (for example, Qtr1, Qtr2, Qtr3).

To fill cells by using the Fill Series command

1. Enter the starting value in the first cell you want to use for the series. If you want to create a series out of a particular pattern (such as 2, 4, 6, and so on), fill in enough cells to define the pattern.

2. Select the entire range you want to fill.

3. On the Home tab, in the Editing group, click Fill, then click Series to open the Series dialog box.

   

4. Do either of the following to create the series, starting from the active cell:

   • Click Rows to create the series in rows.

   • Click Columns to create the series in columns.

5. In the Type group, click the type of series you want.

6. Do any of the following:

   • If you selected the Date type, click an option in the Date unit group.

   • If you selected the Linear or Growth type and want to extend a series trend, select the Trend check box.

   • If you selected a Linear, Growth, or Date series type, enter a number in the Step value box. This number is what Excel uses to generate the next value in the series.

   • To place a limit on the series, enter a number in the Stop value box.

7. Click OK to fill in the series and return to the worksheet.

Create custom number formats

One of the best ways to improve the readability of your worksheets is to display your data in a format that is logical, consistent, and straightforward. Formatting currency amounts with leading dollar signs, percentages with trailing percent signs, and large numbers with commas are a few of the ways you can improve your spreadsheet style. However, you can use Excel to go beyond these built-in formats to create custom number and date formats with which you can display your worksheet values exactly as you want them to be seen.

Excel’s built-in numeric formats give you a great deal of control over how your numbers are displayed, but they have their limitations. For example, there is no built-in format you can use to display a different currency symbol, such as the Euro symbol (€), or to display temperatures using, say, the degree symbol (°).

To overcome these limitations, you need to create your own custom numeric formats. You can do this either by editing an existing format or by entering your own format from scratch. The formatting syntax and symbols are explained in detail later in this section.

Every Excel numeric format, whether built-in or customized, has the following syntax:

positive format;negative format;zero format;text format

The four parts, separated by semicolons, determine how various numbers are presented. The first part defines how a positive number is displayed, the second part defines how a negative number is displayed, the third part defines how zero is displayed, and the fourth part defines how text is displayed. If you leave out one or more of these parts, numbers are controlled as shown in the following table.

The following table lists the special symbols you use to define each of these parts.

Although the built-in date and time formats of Excel are fine for most purposes, you might need to create your own custom formats. For example, you might want to display the day of the week (for example, “Friday”). Custom date and time formats generally are simpler to create than custom numeric formats. There are fewer formatting symbols, and you usually don’t need to specify different formats for different conditions. The following table lists the date and time formatting symbols.

The best way to become familiar with custom formats is to try your own experiments. Excel stores each format that you try. If you find that your list of custom formats is getting a bit unwieldy or that it’s cluttered with unused formats, you can delete those formats.

To open the Format Cells dialog box, do one of the following:

• ➜   On the Home tab, in the Cells group, click Format, then click Format Cells.

• ➜   Right-click the cell or range, then click Format Cells.

• ➜   Press Ctrl+1.

To create and apply a custom number format

1. Select the cell or range of cells you want the new format to apply to.

2. Open the Format Cells dialog box.

3. On the Number tab, in the Category list, click Custom.

4. To base the custom number format on an existing format, click the base format in the Type list.

5. Edit or enter the symbols that define the number format.

   

6. When you are done, click OK to return to the worksheet.

To delete custom number formats

1. Display the Number tab of the Format Cells dialog box.

2. In the Category list, click Custom.

3. In the Type list, click the format you want to remove.

   Tip

   You can delete only custom formats; you can’t delete built-in formats.

4. Click Delete to remove the format from the list.

5. Click OK to close the Format Cells dialog box and return to the worksheet.

Configure data validation

Formulas are only as good as the data they’re given. For basic data entry errors (for example, entering the wrong date or transposing a number’s digits), there’s not much you can do other than exhort yourself or the people who use your worksheets to enter data carefully. Fortunately, you have a bit more control when it comes to preventing the entry of improper data such as data that is the wrong type (for example, entering text in a cell that requires a number) or data that falls outside of an allowable range (for example, entering 200 in a cell that requires a number between 1 and 100).

You can prevent these kinds of improper entries, to a certain extent, by adding comments that describe what is allowable inside a particular cell. However, this requires other people to both read and act on the comment text. You can also use custom numeric formatting to “format” a cell with an error message if the wrong type of data is entered. This is useful, but it works only for certain kinds of input errors.

The best solution for preventing data entry errors is to use the data-validation feature of Excel. With data validation, you create rules that specify exactly what kind of data can be entered and in what range that data can fall. You can also specify pop-up input messages that appear when a cell is selected and error messages that appear when data is entered improperly.

You configure data-validation rules on the Settings tab of the Data Validation dialog box. The following validation types are available:

• Any Value Allows any value in the range (that is, it removes any previously applied validation rule). If you’re removing an existing rule, be sure to also clear the input message, if any.

• Whole Number Allows only whole numbers (integers). You use the Data list to select a comparison operator (such as Between, Equal To, or Less Than) and then enter the specific criteria. For example, if you click the Between option, you must enter Minimum and Maximum values.

• Decimal Allows decimal numbers or whole numbers. You use the Data list to select a comparison operator and then enter the specific numeric criteria.

• List Allows only values specified in a list. You specify the allowable values in the Source box on the Settings tab of the Data Validation dialog box, either by specifying a range on the same sheet or a range name on any sheet that contains the list of allowable values (preceding the range or range name with an equal sign) or by entering the allowable values directly into the Source box (separated by commas). You have the option of allowing the user to select from the allowable values by using a list.

• Date Allows only dates. (If the user includes a time value, the entry is invalid.) You use the Data list to select a comparison operator and then enter the specific date criteria (such as a Start date and an End date).

• Time Allows only times. (If the user includes a date value, the entry is invalid.) You use the Data list to select a comparison operator and then enter the specific time criteria (such as a Start time and an End time).

• Text Length Allows only alphanumeric strings of a specified length. You use the Data list to select a comparison operator and then enter the specific length criteria (such as Minimum and Maximum lengths).

• Custom You can use this option to enter a formula that specifies the validation criteria. You can either enter the formula directly in the Formula box on the Settings tab of the Data Validation dialog box (again preceding the formula with an equal sign) or enter a reference to a cell that contains the formula. For example, if you’re restricting cell A2 and you want to be sure the entered value is not the same as what’s in cell A1, you would enter the formula =A2<>A1.

  

To configure data validation for a cell or range

1. Select the cell or range to which you want to apply the data-validation rule.

2. On the Data tab, in the Data Tools group, click Data Validation to open the Data Validation dialog box.

3. On the Settings tab, in the Allow list, click one of the validation types.

4. Enter the validation criteria you require.

5. To allow blank entries, either in the cell itself or in other cells specified as part of the validation settings, leave the Ignore blank check box selected. If you clear this check box, Excel treats blank entries as zero and applies the validation rule accordingly.

6. If the range had an existing validation rule that also applied to other cells, you can apply the new rule to those other cells by selecting the Apply these changes to all other cells with the same settings check box.

7. If you want a message to appear when the user selects the restricted cell or any cell within the restricted range, on the Input Message tab, do the following:

   Verify that the Show input message when cell is selected check box is selected.

   In the Title box, enter a title for the message.

   In the Input message box, enter the message that you want Excel to display. For example, you could use the message to give the user information about the type and range of allowable values.

   

8. If you want a dialog box to appear when the user enters invalid data, click the Error Alert tab, then do the following:

   Select the Show error alert after invalid data is entered check box.

   In the Style list, click the error style you want: Stop, Warning, or Information.

   In the Title box, enter a title for the message.

   In the Error message box, enter the message that you want Excel to display.

   Important

   Only the Stop style prevents users from entering invalid data.

   

9. Click OK to apply the data-validation rule.

Group and ungroup data

You can control a worksheet range display by grouping the data based on the worksheet formulas and data. Grouping the data creates a worksheet outline, which you can use to “collapse” sections of the sheet to display only summary cells, or to “expand” hidden sections to show the underlying detail.

Not all worksheets can be grouped, so you need to make sure your worksheet is a candidate for outlining. First, the worksheet must contain formulas that reference cells or ranges directly adjacent to the formula cell. Worksheets with SUM functions that subtotal cells above or to the left are particularly good candidates for outlining.

Second, there must be a consistent pattern to the direction of the formula references. For example, a worksheet with formulas that always reference cells above or to the left can be outlined. Excel will not outline a worksheet with, say, SUM functions that reference ranges above and below a formula cell.

If your worksheet meets these criteria, then you can use Excel’s Auto Outline command to automatically group the worksheet data. Otherwise, you can group data manually.

When Excel creates an outline, it divides your worksheet into a hierarchy of levels. These levels range from the worksheet detail (the lowest level) to the grand totals (the highest level). Excel outlines can handle up to eight levels of data. In the Budget worksheet shown below, for example, Excel created three levels for both the column and the row data:

• In the columns, the monthly figures are the details, so they’re the lowest level (level 3). The quarterly totals are the first summary data, so they’re the next level (level 2). Finally, the grand totals (not shown) are the highest level (level 1).

• In the rows, the individual sales and expense items are the details (level 3). The sales and expenses subtotals are the next level (level 2). The Gross Profit row is the highest level (level 1).

To help you work with your outlines, Excel adds the following tools to your worksheet:

• Level bars—These bars indicate the data included in the current level. Click a bar to hide the rows or columns marked by a bar.

• Collapse symbol—Click this symbol to hide (or collapse) the rows or columns marked by the attached level bar.

• Expand symbol—When you collapse a level, the collapse symbol changes to an expand symbol (+). Click this symbol to display (or expand) the hidden rows or columns.

• Level symbols—These symbols tell you which level each level bar is on. Click a level symbol to display all the detail data for that level.

  Tip

  To toggle the outline symbols on and off, press Ctrl+8.

To group a worksheet using Auto Outline

• ➜   On the Data tab, in the Outline group, click Group, then click Auto Outline.

To group data manually

• ➜   Select the rows or columns you want to group, then on the Data tab, in the Outline group, click Group, then click Group.

Or,

1. Select a cell in each row or column you want to group.

2. On the Data tab, in the Outline group, click Group, then click Group. Excel asks whether you want to group rows or columns.

3. Select either Rows or Columns, as appropriate for your data, then click OK.

   

To remove an outline from a worksheet

• ➜   On the Data tab, in the Outline group, click Ungroup, then click Clear Outline.

To ungroup data

• ➜   Select the rows or columns you want to ungroup, then on the Data tab, in the Outline group, click Ungroup, then click Ungroup.

Or,

1. Select a cell in each row or column you want to ungroup.

2. On the Data tab, in the Outline group, click Ungroup, then click Ungroup. Excel asks whether you want to ungroup rows or columns.

3. Select either Rows or Columns, as appropriate for your data, then click OK.

Calculate data by inserting subtotals and totals

Although you can use formulas and worksheet functions to summarize your data in various ways—including sums, averages, counts, maximums, and minimums—if you’re in a hurry, or if you just need a quick summary of your data, you can get Excel to do the work for you. The secret here is a feature called automatic subtotals, which are formulas that Excel adds to a worksheet automatically.

Excel sets up automatic subtotals based on data groupings in a selected field. For example, if you ask for subtotals based on the Customer field, Excel runs down the Customer column and creates a new subtotal each time the name changes. To get useful summaries, you should sort the range on the field containing the data groupings you’re interested in.

Note that in the phrase automatic subtotals, the word subtotals is misleading because it implies that you can only summarize your data with totals. However, you can also count the values (all the values or just the numeric values), calculate the average of the values, determine the maximum or minimum value, and calculate the product of the values. For statistical analysis, you can also calculate the standard deviation and variance, both of a sample and of a population.

To insert subtotals and totals

1. Select a cell within the range you want to subtotal.

2. On the Data tab, in the Outline group, click Subtotal to open the Subtotal dialog box.

3. Use the At each change in list to select the column you want to use to group the subtotals.

4. In the Use function list, select Sum.

5. In the Add subtotal to list, select the check box for the column you want to summarize.

   

6. Click OK. Excel calculates the subtotals and adds them to the range and also adds a Grand Total row to the bottom of the range. Excel also adds outline symbols to the range.

To change the summary calculation

• ➜   In step 4 from the previous section, in the Use function list, select the function you want to use, such as Count, Average, Max, or Min.

Remove duplicate records

You can make your Excel data more accurate for analysis by removing duplicate records. Duplicate records throw off calculations by including the same data two or more times. To prevent this, you should delete duplicate records. Rather than looking for duplicates manually, you can use the Remove Duplicates command, which quickly finds and removes duplicates in even the largest ranges or tables.

Before you use the Remove Duplicates command, you must decide what defines a duplicate record in your data. You have two choices:

• Two records are duplicates if, for every column in the range or table, the records contain identical values.

• Two records are duplicates if, for only certain columns in the range or table, the records contain identical values.

To remove duplicate records

1. Click a cell inside the range or table.

2. On the Data tab, in the Data Tools group, click Remove Duplicates to open the Remove Duplicates dialog box.

3. If your range doesn’t have column headers, clear the My data has headers check box.

4. Select the check box beside each field that you want Excel to check for duplicate values.

   Tip

   If your range or table has many fields, you may want Excel to use only one or two of those fields to look for duplicate records. Rather than clearing all the other check boxes manually, first click Unselect All in the Remove Duplicates dialog box to clear all the check boxes. You can then click to select just the check boxes you want Excel to use.

   

   Important

   Excel does not give you a chance to confirm the deletion of the duplicate records, so be sure you want to do this before proceeding.

5. Click OK. Excel deletes any duplicate records that it finds and then displays a dialog box telling you the number of duplicate records that it deleted.

   

6. Click OK.

Create custom conditional formatting rules

Many Excel worksheets contain hundreds of data values. You can make sense of large sets of data by creating formulas, applying functions, and performing data analysis. However, there are times when you don’t want to analyze a worksheet per se. Instead, all you want are answers to simple questions such as the following:

• Which cell values are less than 0?

• What are the top 10 values?

• Which cell values are above average, and which are below average?

These simple questions aren’t easy to answer just by glancing at the worksheet, and the more numbers you’re dealing with, the harder it gets. To help you “eyeball” your worksheets and answer these and similar questions, Excel lets you apply conditional formatting to the cells. This is a special format that Excel applies only to cells that satisfy some condition, which Excel calls a rule. For example, you could apply formatting to show all the negative values in a red font, or you could apply formatting to highlight the top 10 values.

You can apply five types of conditional formatting rules:

• Highlight cells rules A highlight cell rule applies a format to cells that meet specified criteria. You have seven choices: Greater Than, Less Than, Between, Equal To, Text That Contains, A Date Occurring, and Duplicate Values. In each case, you use a dialog box to specify the condition and the formatting that you want applied to cells that match the condition.

  

• Top/bottom rules A top/bottom rule applies a format to cells that rank in the top or bottom (for numerical items, the highest or lowest) values in a range. You can select the top or bottom either as an absolute value (for example, the top 10 items) or as a percentage (for example, the bottom 25%). You can also format cells that are above or below the average. In each case, you use a dialog box to set up the specifics of the rule. For the Top 10 Items, Top 10%, Bottom 10 Items, and Bottom 10% rules, you use the dialog box to specify the condition and the formatting you want applied to cells that match the condition. For the Above Average and Below Average rules, you use the dialog box to specify the formatting only.

  

• Data bars If you’re interested in the relationship between similar values in a worksheet, you need some way to visualize the relative values in a range, and that’s where data bars are useful. Data bars are colored, horizontal bars that appear “behind” the values in a range. (They’re like a bar chart.) Their key feature is that the length of the data bar that appears in each cell is related to the value in that cell: the larger the value, the longer the data bar. The cell with the highest value has the longest data bar, and the data bars that appear in the other cells have lengths that reflect their values. Excel configures its default data bars with the longest data bar based on the highest value in the range and the shortest data bar based on the lowest value in the range. You can also set up data bars based on a specific range of values (for example, the values 0 and 100 for test scores) or as a percentage of the largest value.

  

• Color scales The color scales in Excel are useful if you want to get a “big picture” view of your data that shows, for example, the overall distribution of the values and whether there are any outliers (values that are much higher or lower than all or most of the other values). The color scales are also helpful if you want to make value judgments about your data. For example, high sales and low numbers of product defects are “good,” whereas low margins and high employee turnover rates are “bad.” A color scale is similar to a data bar in that it compares the relative values of cells in a range. Instead of bars in each cell, though, you see cell shading, where the shading color reflects the cell’s value. For example, the lowest values might be shaded red; higher values might be shaded light red, then orange, yellow, and lime green; and finally the highest values could be shaded deep green.

  

• Icon sets You use icon sets to visualize the relative values of cells in a range. Excel adds an icon to each cell in the range, and that icon tells you something about the cell’s value relative to the rest of the range. For example, the highest values might get an up arrow, the lowest values a down arrow, and the values in between a horizontal arrow. Icon sets take advantage of symbols that people have strong associations with.

  For example, a check mark means something is good or finished or acceptable, whereas an X means something is bad or unfinished or unacceptable. A green circle is positive, whereas a red circle is negative (similar to traffic lights).

To create a custom conditional formatting rule

1. Select the range to which you want the custom conditional formatting applied.

2. On the Conditional Formatting menu, click New Rule to open the New Formatting Rule dialog box.

3. In the Select a Rule Type group, click the type of rule you want to apply.

   

4. Select the rule type’s conditions and formatting.

5. Click OK to close the dialog box and return to the workbook.

Create conditional formatting rules that use formulas

Excel comes with another conditional formatting component that makes this feature even more powerful: you can apply conditional formatting based on the results of a formula. In particular, you can set up a logical formula as the conditional formatting criterion. If that formula returns TRUE, Excel applies the formatting to the cells; if the formula returns FALSE, instead, Excel doesn’t apply the formatting.

When comparing worksheet values in a conditional formatting rule’s logical formula, you generally set up the expression to compare the value you seek with a specific value from the range, and then use a mixed-reference format for that specific value so that Excel can compare all the values in the range to the target value. For example, suppose you have a list of percentage increases in the range D5:D13 and a “target” percentage in cell D2. You want to apply a format only on those entries where the percentage increase is greater than or equal to the target value. Here’s the logical formula to use:

=$D5 >= $D$2

The mixed-reference format $D5 tells Excel to keep the column (D) fixed while varying the row number (in this example, 5 through 13), and in each case compare the resulting cell value with the value in $D$2.

To create a custom conditional formatting rule based on a formula

1. Select the range to which you want the custom conditional formatting applied.

2. In the New Formatting Rule dialog box, in the Select a Rule Type list, click Use a formula to determine which cells to format.

3. In the Format values where this formula is true box, enter your logical formula.

   

4. Click Format to open the Format Cells dialog box.

5. On the Number, Font, Border, and Fill tabs, specify the formatting you want Excel to apply when the formula evaluates to TRUE, then click OK.

6. In the New Formatting Rule dialog box, click OK.

Manage conditional formatting rules

Conditional formatting rules are extremely useful and powerful tools, so you might find that you use them frequently. As you use them, however, your need to manage those rules will increase. For example, you’ll often need to edit existing rules to update the conditions or change the formatting. Similarly, if you’ve applied two or more rules to the same range, you should know how to change the order that Excel uses to apply those rules. Finally, you also need to know how to delete existing rules that you no longer require.

To edit a conditional formatting rule

1. Click any cell in the range that has the conditional formatting applied.

2. On the Conditional Formatting menu, click Manage Rules to open the Conditional Formatting Rules Manager dialog box.

3. Click the rule you want to modify, then click Edit Rule to open the Edit Formatting Rule dialog box.

4. Make your changes to the rule type, rule conditions, or rule formatting, then click OK.

5. In the Edit Formatting Rule dialog box, click OK.

6. In the Conditional Formatting Rules Manager dialog box, click OK.

To change the order in which conditional formatting rules are applied

1. Click any cell in the range that has the conditional formatting applied.

2. Open the Conditional Formatting Rules Manager dialog box.

3. Click a rule, then click either the up arrow or the down arrow until the rule is in the position you prefer.

4. Repeat for the other rules you want to move, then click OK.

To delete a conditional formatting rule

1. Click any cell in the range that has the conditional formatting applied.

2. Open the Conditional Formatting Rules Manager dialog box.

3. Click the rule you want to remove, then click Delete Rule. Excel removes the rule.

4. Click OK to close the dialog box and return to the worksheet.

   Tip

   If you want to delete multiple rules, a quicker method is to click any cell in the range that has the rules applied, click the Home tab, click Conditional Formatting in the Styles group, click Clear Rules, and then click Clear Rules From Selected Cells. If you want to delete every rule in the current worksheet, click Clear Rules From Entire Sheet.