Manipulating Data I (Mac)

Undo, Redo, & Repeat


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 Command+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 Command+Z.



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 Command+Y to Redo the most recent Undo (note that Command+Y also Repeats, see next section).



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. Click the Repeat icon where you want to repeat your last action.

2. Press Command+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 CTRL + click on the toolbar. This dropdown menu will appear and you can change icons shown under Customize Toolbars and Menus.

Within the Customize Toolbars and Menus window, under the Commands section, you can scroll through different icons on the left and drag them to where you would like them to appear on the Toolbar.


Find & Replace


Sometimes it is useful to know how to find something quickly in a file. For example, let's say you are looking the data file of the Census information from the 5 boroughs of New York for 2000 and 2010 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.

1. 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 found in the upper-right-hand corner of the screen:


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 what box, you will find every single cell that contains a zero.

Because you want to search in a specific column (G), you would select column G before opening the Find dialogue box, then select By Column from the Search dropdown menu. You can select the check box for Find entire cell contents to search for cells that contain '0' and nothing else.  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. 



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 clicking on the little arrow next to the magnifying glass next to the search bar in the upper-right-hand corner of the screen 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.


And then hit 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.