Manipulating Data I (PC)

Undo, Redo, & Repeat

Undo

Sometimes we do things we wish we didn't. But in Excel we can undo them (usually)! As with most things in Excel, there is more than one way to do it.

1. Click on the icon on the Quick Access Toolbar. You can also click the arrow next to the icon and select to Undo many items at once. (If this is not what your Quick Access Toolbar looks like, instructions for changing it are here.)

 

2. Press CTRL+Z to Undo the most recent action.

Some actions cannot be undone (e.g. Save). When there is nothing to undo or it can't be undone, the undo button in the Quick Access Toolbar becomes gray or you'll hear a ding or beep when pressing CTRL+Z.

 

Redo

Redo pertains to actions you undid, but wish you hadn't, not to actions that you did and would like to do again (this is Repeat and logically the two are very much related).

1. Click on the icon on the Quick Access Toolbar (which it is blue, because otherwise, there isn't anything that you can Redo). As with Undo you can click on the dropdown arrow and Redo many items at once. (Follow this to change the Quick Access Toolbar)

2. Press CTRL+Y to Redo the most recent Undo (note that CTRL+Y also Repeats, see next section).

 

Repeat

Repeat allows you to repeat an action you just did. Repeat only repeats that most recent action once. If you want to repeat the same action multiple times, you might be better off creating a Macro.

The Repeat icon is not on the Quick Access Toolbar by default, but you can click the link or scroll down to the Customize Quick Access Toolbar section to learn how to add it.

1. Once inserted in the Quick Access Toolbar, click the Repeat icon where you want to repeat your last action.

2. Press CTRL+Y to Repeat the most recent action once. This action works for both Repeat and Redo because of the relationship with Undo. If you undo something, you can't repeat that action, but you can redo it. If you haven't undone anything, then there would be nothing to redo, but you could repeat the previous action.

Be aware that not all actions can be repeated.

 

Customize Quick Access Toolbar

The quickest way to customize the Quick Access Toolbar is to click on the dropdown arrow to the right of all of the icons on the toolbar.

 

Click on More Commands

 

The column of items to the left contains everything that you could put in the Quick Access Toolbar. Select the item you would like and click the Add>> button. You can only add one item at a time. You can also remove items from the Quick Access Toolbar by selecting the item you would like to remove from the column in the right and clicking <<Remove

You can also edit the Quick Access Toolbar by clicking Options under the File Tab. In the new dialogue box click Quick Access Toolbar in the left column.

 

Find & Replace

Find

Sometimes it is useful to know how to find something quickly in a file. For example, let's say you are looking at the data file of the Census information from the 5 boroughs of New York for 2000 and 2010 displayed below and you wanted to look at data about Queens. You could scroll down the worksheet until you see it, or you could dearch for 'Queens' using Find.

 

Press CTRL+F on the keyboard. This opens a dialogue box where you can type in the term or number that you are searching for. In this case, we would type in 'Queens'.

 

This dialogue box can also be reached by clicking on Find & Select in the Editing group on Ribbon and selecting Find from the dropdown menu.

 

In the Find and Replace dialogue box you can click on the Options>> button to modify the search results.

Let’s say, for example, you are interested in finding all of the instances in which the Total Population did not change between 2000 and 2010. To do this, you need to search for zeros in column G (which represents the change in total population between 2000 and 2010 in terms of number, instead of percent). If you simply type 0 into the Find box, you will find every single cell that contains a zero.

 

Because you want to search in a specific column (G), you should select the entire column by clicking on the letter 'G' before opening the Find dialogue box. When you click on the Options>> button you can select the check box for Match entire cell contents to search for cells that contain '0' and nothing else.  You would also select By Column from the Search dropdown menu.  Then select Find Next or Find All

 

Find Next will find the next instance of 0 in column G. You could continue clicking Find Next to find and note all census tracts in which the total population did not change from 2000 to 2010.

Find All will find all of the instances of the search item. When you select Find All a menu shows up below the dialogue box that shows all instances of the item you are searching for. You can scroll to the items in column G and click on the specific cell to go to that cell in the worksheet.

\

 

Replace

Replace works just like Find except that you can choose to replace an item with something else (including a blank).

Select the Replace tab in the Find and Replace dialogue box. You can also reach this by selecting Find & Select in the Editing group in the Ribbon and then selecting Replace.

Under the Replace tab, in addition to the Find what box, there is a Replace with box in which you can enter the item (or nothing) that you want to replace the item you are searching for.

For example, let's say you are interested in calculating the average population change for each borough, but you don't want to include instances where there were no changes. In which case you could do the same search for zeros in column G as before, but Replace it with a blank. The average function in Excel will skip over blanks when calculating the average (more on that here). Select Find Next to find the first item you will be replacing.

 

Then select Replace.

 

Replace All will replace all of the found items, which can be useful, but sometimes it will also replace things you weren't anticipating. In this case it would replace zeros found in cells in all of the columns, not just G, which would be problematic. If you select the entire column G, it will only replace items in that column.

 

<< Back to Inserting & Deleting                 Continue to Manipulating Data II >>

 

Return to Table of Contents

 

Questions, comments, concerns?
Send an email to the Empirical Reasoning Lab
Or drop in during the Lab's open hours.