Generate a Number Series

The same number is repeated for every row (Figure 1-2).

By entering a second number, you can get a sequence (Figure 1-3).

But you do not need to go through that hassle. There are a couple of neat tricks to generate the series. Simply type the first number and hold the Ctrl key down as you fill to generate the sequence.

An alternative method is to use the magic square to the right. Select the square in addition to the one containing the number and fill down (Figure 1-4).

Additional Series Options

You can access additional series options by dragging the fill handle away and then back with the right button depressed. On releasing the right button, a menu appears (Figure 1-5). Click Series.

The Series window (Figure 1-6) provides some brilliant options such as to fill along rows or down columns, if you want to step values and at what value to stop.

In this example, I have set it to step by 2 and to stop at number 15.

The list is created with minimum fuss (Figure 1-7).

This is just one example of what is available. But let us look at a far more realistic scenario.

We are tasked with creating a list of dates from 3 March 2020 to 30 October 2022, and we want every other week (3 March 2020 is a Tuesday).

1. 1.

   Type 03/03/2020 into the first cell and open the Series window.

    
2. 2.

   It should automatically detect that you want to use date values. Ensure this is selected and note the options available for date units.

    
3. 3.

   Enter 14 for the Step value and 30/10/2022 for the Stop value. The completed Series window is shown in Figure 1-8.

    

The list is generated (Figure 1-9). This is much simpler than typing two dates and dragging down cells until you reach 30 October 2022.

In this scenario, the list stops at 25 October 2022 because that is the final Tuesday in the series.

The Incredible Flash Fill

Flash Fill is a tool that arrived with Excel 2013, and the day I first used it, I could not sleep that night. Along with the more important Power Query (Chapter 5), these tools make easy what was once a frustrating task.

Let us look at a couple of examples of what Flash Fill can do and how. These examples just give an insight, and you should further explore what else it can do.

For the first example, we have people’s first names in column A and their last names in column B. In column C, we want to combine the two together.

Type the full name of the first person and press Enter. Start typing the name of the second person, and Flash Fill appears offering to complete the rest for you (Figure 1-10). Press Enter to confirm and fill in every full name.

So easy to combine hundreds or thousands of names and without any formula.

For a second example, we have the codes in Figure 1-11, and we want to extract the letters from between the two hyphens (-). They also need to be displayed in uppercase.

This would be a complicated formula, but with Flash Fill it is simple.

As easy as that, we have the data we want for further analysis (Figure 1-12).

Find and Replace in the Entire Workbook

Let us begin with a huge time saver, being able to replace, format, or remove values from an entire workbook with a few clicks of a button.

We have three worksheets: North East, North West, and South (of course, it could be many more), and we need to make some changes to data across all these sheets.

For this first example, we want to change the name of a product from “Supreme Pizza” to “Mega Pizza.” We need to change every instance of this name and for all worksheets.

1. 1.

   Open the Find and Replace window by using the Ctrl + H keyboard shortcut or clicking Home ➤ Find & Select ➤ Replace.

    
2. 2.

   Type “Supreme Pizza” in the Find what box and “Mega Pizza” in the Replace with box.

    
3. 3.

   Click the Options button to expand the dialog window.

    
4. 4.

   Change the Within setting from Sheet to Workbook. The completed steps are shown in Figure 1-24.

    

All instances of “Supreme Pizza” have been replaced. A message is shown confirming the number of replacements made (Figure 1-25). If this is more than expected, you can undo the operation and refine your search criteria.

Edit Your Formulas Fast

The Find and Replace tool is set to look in formulas by default. This setting can be extremely useful for editing many formulas quickly.

They are using sheet references. $C$2:$C$21 refers to the sales values on that sheet.

The ranges on these sheets are now being formatted as tables, and because of these we want to quickly update all formulas to use the table references instead, as this is more efficient.

Each table is named as NorthEast, NorthWest, and South (spaces cannot be used in table names). A sample of the NorthEast table is shown in Figure 1-26.

Change Cell Formatting

Yes, it is true. Find and Replace can even be used to locate, replace, or remove based on the cell formatting.

The formulas on the North East, North West, and South worksheets all have a specific formatting applied to them. This is a good idea as it makes the cells containing formulas instantly recognizable to users.

If the formatting of these cells needs to change, Find and Replace makes this a simple task.

1. 1.

   Open the Find and Replace window.

    
2. 2.

   Click the Options button to make the formatting options available.

    
3. 3.

   Next to the Find what box, click the Format button arrow, select Choose Format From Cell (Figure 1-28), and click one of the formula cells.

    

Remove Values

Find and Replace, despite its name, is not only great at finding and replacing values and formatting. But it is also very useful at finding and removing data.

Figure 1-30 shows the first ten rows of sales data with total rows inserted into the range. For a more efficient analysis, we will remove these total rows.

1. 1.

   Open the Find and Replace window.

    
2. 2.

   Type “Total” into the Find what box.

    
3. 3.

   Select Sheet from the Within list.

    
4. 4.

   Click the Match entire cell contents box. We want to be sure that the found cells only contain the word “Total”.

    
5. 5.

   Click Find All. A list of all the found cells including information such as the cell address is shown. The completed steps are shown in Figure 1-31.

    

1. 6.

   Ensure the area with the list of found cells is active and press Ctrl + A to select all the cells (Figure 1-32).

    
2. 7.

   Click Close.

    

1. 8.

   Click Home ➤ Delete ➤ Delete Sheet Rows to remove the rows that were found (Figure 1-33).

    

Remove Asterisks from a Range

There are two main wildcard characters that can be used in your search criteria. These are the question mark (?) and the asterisk (*).

These wildcard characters can be used in place of characters you are unsure of. The question mark can be used in place of a single character. For example, A?an would find both Adam and Alan. And the asterisk can be used in place of any number of characters. For example, L*n would find both London and Linton.

If the asterisk and question mark are wildcards, how would you look for those characters specifically if needed?

Let us look at the scenario shown in Figure 1-34. A data import has positioned asterisks around the names in a list, and we need to remove them.

In this scenario, we can use the tilde (~) character before the asterisk in the Find what box (Figure 1-35). Leave the Replace with box empty to replace them with nothing and click Replace All.

The tilde informs the Find and Replace tool that the following character should be used as the search criteria and not as a wildcard.

Replace Line Breaks Easily

Line breaks are another type of nasty character that you can find yourself removing from Excel spreadsheets, especially when you are getting data from external sources. Once again, though, this is easy with Find and Replace.

In Figure 1-36, line breaks have been used to separate the names.

1. 1.

   Select the range of names and open the Find and Replace window.

    
2. 2.

   In the Find what box, press Ctrl + J on the keyboard. This shortcut inserts a line break for the search criteria (it may not be visible).

    
3. 3.

   In the Replace with box, type a comma and a space (, ) (Figure 1-37).

    

Delete Every Nth Row Quickly

At times we need to be inventive, and if we cannot search directly for the text we need, a helper column can be created.

In this example, we have a list of sales data with total rows inserted into the range which we want to remove. The total rows are labeled with the region name so we do not have consistent text that we can use as search criteria, but they do occur in every fifth row.

To get around this, we will create a column with a series of characters and use a unique character for every fifth row (Figure 1-38).

1. 1.

   In column C, type the characters into the first five cells. You can use any characters you want; just ensure that the one in the fifth row is unique.

    
2. 2.

   Select these five cells and fill the list to the bottom of the range to generate the series of characters. You can do this quickly by double-clicking the fill handle.

    

1. 3.

   In the Find and Replace window, type the unique character in the Find what box. Click Find All to list the cells containing that character.

    
2. 4.

   Press Ctrl + A to select all the cells (Figure 1-39).

    

Remove Blank Rows

The problem of blank rows is an unfortunate tale that all Excel users experience at some point. The good news is that the shining knight of Go To Special will help rid them from our spreadsheets.

Figure 1-40 shows a snapshot of some data we have received. We need to remove all the blank rows that appear after each period.

1. 1.

   Select the range to search for blanks. In this example, I will select column A as that is sufficient to identify a blank row.

    
2. 2.

   Press F5 to open the Go To window and click Special, or click Home ➤ Find & Select ➤ Go To Special to open the Go To Special window.

    
3. 3.

   Select Blanks (Figure 1-41) and click OK.

    

Fill Blank Cells with 0

In addition to blank rows to remove, there is the other common issue of blank cells.

Figure 1-42 shows a sample of data with blank cells scattered within the range. We want to fill those cells with a value. In this example, we will fill them with 0.

1. 1.

   Select the range containing the blanks to fill. In this example, I will select columns B:E.

    
2. 2.

   Click Home ➤ Find & Select ➤ Go To Special, select Blanks, and click OK.

    
3. 3.

   All the blank cells in the range are selected. The first one is the active cell (Figure 1-43).

    

1. 4.

   Type 0. This will appear in the active cell only. Press Ctrl + Enter to apply to all the other selected cells (Figure 1-44).

    

Fill Blank Cells with the Cell Value Above

Exporting reports from other systems into Excel often leaves us with data that is awkward to analyze further. Take the example in Figure 1-45 which has blank cells under the labels in the first two columns.

We will fill those cells with the value from the cell above.

1. 1.

   Select the range containing the blanks to fill. In this example, I will select columns A:B.

    
2. 2.

   Click Home ➤ Find & Select ➤ Go To Special, select Blanks, and click OK.

    
3. 3.

   The blank cells are selected. Type = and click the cell above the active cell. In Figure 1-46, that is cell B2.

    
4. 4.

   Press Ctrl + Enter.

    

All the blank cells are populated with the content from the cell above (Figure 1-47).

Format All Cells Containing Formulas

When creating models and reports in Excel, it can be awkward to remember which cells contain input values or which contain formulas.

Doing something simple such as formatting these cells differently will give them that distinction so that it is no longer a headache in the future.

If you have many formulas on a spreadsheet, this may sound a tedious task to find and select them all to format. Let us get Go To Special to do it for us.

1. 1.

   Open the Go To Special window.

    
2. 2.

   Click the Formulas option. You can be more specific and select the formulas that return numbers, text, logicals, or errors, if needed. We will select all (Figure 1-48).

    
3. 3.

   With the formulas selected, they can now be formatted how you want.

    

Compare Two Columns by Identifying Row Differences

When auditing spreadsheets, you may need to highlight inconsistencies between values in different columns. Go To Special has an option to quickly identify these differences.

Figure 1-49 shows a list of invoices and the amount paid which we want to compare for this example.

1. 1.

   Select the range of cells to compare. In this example, this is B2:C6.

    
2. 2.

   Open the Go To Special window by pressing F5 followed by Alt + S, or click Home ➤ Find & Select ➤ Go To Special.

    
3. 3.

   Click the Row differences option (Figure 1-50) and click OK.

    

With the cells containing a different value to its adjacent column selected, we can format the cells so that they are easily identified (Figure 1-51).

Convert Text to Number

Text to Columns is one of the fastest no-nonsense ways to convert data. Many users do not realize this because its name implies that it will split data into columns. But you do not have to.

In this example, we have a classic scenario of converting numbers stored as text to numbers.

1. 1.

   Select the range of text you want to convert to numbers.

    
2. 2.

   Click Data ➤ Text to Columns to open the Text to Columns wizard.

    
3. 3.

   Leave the first step as Delimited and click Next.

    
4. 4.

   Remove all delimiter options in step 2. We are not interested in splitting columns. Click Next.

    
5. 5.

   Ensure General is selected as the format (Figure 1-52). This will convert numbers and dates if it recognizes them. Click Finish.

    

Converting Date Formats

Excel can sometimes have trouble recognizing dates. This could be that they have come from a country with a different format to the regional settings of your Excel. For example, I am based in the UK, so dates formatted as MM/DD/YYYY are not recognized as a date.

Figure 1-53 shows dates in the MM/DD/YYYY formats. Two of the dates are stored as text, and two have been stored as dates. Excel thinks it understands two of them, but the dates are wrong as they do not follow the expected DD/MM/YYYY format.

Text to Columns will take care of this.

1. 1.

   Select the range of dates and click Data ➤ Text to Columns.

    
2. 2.

   Leave the first step as delimited, and ensure no delimiters are used in the second step.

    
3. 3.

   On step 3, click the Date option and select MDY from the format list (Figure 1-54). Click Finish.

    

The dates are now recognized and stored correctly (Figure 1-55).

Text to Columns can be used to convert from any date format. This could be dates stored as text or dates using a YYYYMMDD format.

It is one of my favorite secrets about the feature. And when I use it to resurrect thousands of unrecognized dates in a few seconds, it always impresses.

Converting International Number Formats

It is also a quick way of handling international number formats. I live in the UK, so the comma (,) is used as a thousand separator and the period (.) as a decimal separator.

Not all countries use the same characters so there can be confusion in Excel when working with data from other countries. Figure 1-56 shows numbers that use a comma (,) as the decimal separator and the period (.) as the thousand separator. My version of Excel does not recognize these as numbers, though it did recognize the 9.

Thankfully, Text to Columns can fix this fast.

1. 1.

   Select the range of numbers to convert and click Data ➤ Text to Columns.

    
2. 2.

   Leave the first step as delimited, and ensure no delimiters are used in the second step.

    
3. 3.

   In step 3, click the Advanced button.

    
4. 4.

   Change the Decimal separator and the Thousands separator to the ones used by the data (Figure 1-57).

    

The numbers are successfully converted (Figure 1-58).

Convert Values with a Trailing Minus Sign

Let us look at how Text to Columns can easily handle the scenario of trailing minus signs as shown in Figure 1-59.

1. 1.

   Select the range you need to convert and click Data ➤ Text to Columns.

    
2. 2.

   Leave the first step as delimited, and ensure no delimiters are used in the second step.

    
3. 3.

   In step 3, click the Advanced button and check that the Trailing minus for negative numbers box is checked (Figure 1-60). By default, it should be so this is an often unnecessary check.

    

The text values have been successfully converted to negative numbers (Figure 1-61).

Convert Positive Values to Negative

Multiplying the values by –1 converts them to negative. The same technique converts negative values to positive.

These operations in Paste Special are extremely useful. We can perform fast mathematical operations without having to use a cell formula and then copy the results.

Remove Formulas from a Cell

Pasting values only to remove the formulas from a cell is probably the most common use of Paste Special.

Instead of achieving this through the Paste Special options, let us look at one of my favorite shortcuts – the right-click wiggle.

1. 1.

   Select the range containing the formulas you want to replace with values only.

    
2. 2.

   Position your cursor on the edge of the selected range, right-click and drag away from the selection, then back again and release the mouse.

    
3. 3.

   A menu appears with some useful paste options (Figure 1-63). Click Copy Here as Values Only.

    

Repeat Column Widths

This is a question I get a lot on my beginner’s classes in Excel. How can we make each column the same width quickly?

Figure 1-64 shows four columns, and the “Qtr3” column is clearly wider than the other three. Also, how do we know the other three are the same width?

Let us repeat the width of the “Qtr1” column to the other three to be sure they are all the same.

Pasting with Charts

Paste Special can also be used to quickly copy formatting between charts. In this example, we want to repeat the formatting from the “Income” chart on the left to the “Expenses” chart on the right (Figure 1-65).

1. 1.

   Select the chart with the format you want to copy and press Ctrl + C.

    
2. 2.

   Click the chart you want to paste the formatting to.

    
3. 3.

   Click Home ➤ Paste arrow ➤ Paste Special to open the window shown in Figure 1-66.

    

Job done! The two charts have the same format (Figure 1-67).