Data Manipulation Basics

Worksheets vs. Workbooks

An Excel file is called a workbook and will hold as many worksheets as available memory will allow (new file default is 1 worksheet). Worksheets can be independent of each other, in that the data in each worksheet is separate from the other worksheets, or they can be linked, which means that the data in one worksheet is used in some way in the other worksheets.

Having multiple worksheets within a workbook allows you to store different data sets together. For example, you might store each year's budget in one workbook with a worksheet for each year (these would be independent). You'll have all the data in one file if you want to review changes over the years.

Or you may be interested in analyzing information from different data sets. For example say you are interested in how unemployment rates affect wages, you may have worksheets set up like this, where analysis allows you to combine the relevant parts of each data set for analysis (these could be linked worksheets):

Or you may be collecting data from individuals and each worksheet could represent a participant in the study (each identified by a subject ID number):

You might also use one worksheet for data and another worksheet for graphs, especially if you will be using statistical software such as SPSS or STATA to analyze the data. Having graphs in the worksheet with the data can affect the importability of the data into statistical software programs, but you can eliminate those problems by importing the data worksheet and creating graphs in a seperate worksheet:

Saving a Workbook

There is more than one way to save a workbook.

• Click the save icon on the quick access toolbar.

• Press 'Command+S' on the keyboard.
• Go to the File tab and click on Save.

A worksheet consists of a 2 dimensional grid with columns and rows which intersect to create cells.

Columns are marked with letters. Once the first 26 columns have been labeled (A-Z), letters are doubled and then tripled to allow for 16,384 columns (up to XFD).

Rows are marked with numbers and there are over a million rows (1,048,576 to be exact). If you are attempting to work with a single data set that pushes these limits, my sympathies!

A cell is referenced by its column letter and row number.

Naming a Worksheet

There is more than one way to name/rename a worksheet.

• Double click on the worksheet tab and type in the new name.

• Go to the dropdown menu Format on the top and select Sheet. On the side menu, click on Rename.

• Hold down Control and click on the worksheet tab. Select Rename from the drop down menu.

Adding a New Worksheet

There are many ways to add a new worksheet.

• The tab to the right of all of the other tabs has a 'new worksheet icon' on it. Simply clicking on this tab will create a new worksheet.

• Go to Insert in the Main Menu Bar and select Sheet, in the side menu, click on Blank Sheet.

• Hold down Control and click on the worksheet tab. Select Insert Sheet from the drop down menu.

• Click on the New Window icon under Layout.

Deleting a Worksheet

Go to Edit in the Main Menu Bar and select Delete Sheet

If you’ve typed anything in the worksheet, this error message will come up.

Once the sheet is deleted it is gone forever (undo is NOT an option), so always be certain before deleting that it is what you want to do.

Move a Worksheet within a Workbook

There are multiple ways to move a worksheet within a workbook:

• Click on the worksheet tab and, holding the mouse button down, drag it to where you would like the worksheet to be. A small black arrow will appear showing you where you will be placing the sheet. All information in the worksheet is preserved during this process.

• Go to Edit in the Main Menu Bar and select Move or Copy Sheet. A dialogue box will open.
• Hold down Control, click on the worksheet tab and select Move or Copy from the drop down menu. This will open the same dialogue box as in option 2.

Creating a Copy of a Worksheet within a Workbook

There are multiple ways to create a copy of a worksheet within a workbook:

• Hold down Control, click on the worksheet tab, select Move or Copy and select the worksheet directly behindwhere you want to place the copy. Check the Create a Copy box. The copy will have the same name as the original followed by a number in parenthesis, which you can rename as discussed above.

• Click on Edit in the main menu bar and select Move or Copy Sheet. This will open the same dialogue box and will refer to the worksheet you are currently in.

Move or Copy a Worksheet to a Different Workbook

After opening the dialogue box for moving a worksheet within the same workbook (using one of the methods discussed above), open the dropdown menu to select the workbook you would like to move the worksheet to. The workbook must be open to appear in the menu. You can also create a new workbook in the dialogue box. 

To leave a copy in the current worksheet check the Create a copy check box, otherwise the worksheet will be removed from the current workbook and moved to the chosen one.

Changing the Worksheet Tab Color

There are multiple ways to change a worksheet tab color:

• Hold down Control, click on the sheet tab and go to Tab Color and select the color you would like. Note that the colors are very pale when you are working in the worksheet, but become brighter if you are in a different worksheet.

• Click Format on the main menu bar, scroll over the Sheet tab and select Tab Color.

The Ribbon is the panel at the top of the document. There are nine tabs, and each contain a different set of features that will help you in editing and developing your spreadsheet.

The features in each tab are categorized into related groupings (hereafter referred to as a group), such as Edit, Font, Cells, all found in the Home tab.

The Ribbon can be customized if there are certain features you would prefer to have in the Ribbon over others. This will be discussed later in the tutorial.

Entering Data

To enter data into a cell, simply type it in. When you type into the cell, the information will also be visible in the formula bar.

Use the arrow keys to move from cell to cell. Also, pressing ENTER will move to the cell below and SHIFT+ENTER will move to the cell above. Pressing TAB will move to the right cell and SHIFT+TAB will move to the left cell.    

If your mouse has a scroll wheel (the "third" button) you can use it to scroll up and down the spreadsheet (but not change which cell you are typing in). When you hit the SCROLL LOCK key (if your keyboard has one) you can use the arrows to scroll up, down, left, or right (but not change which cell you were typing in). 

Selecting Cells

There are many ways to select cells in a worksheet.

• Use the mouse to select a range of data cells. Click on the cell in one corner of the range and holding down the mouse key, drag the mouse until all cells in the range are highlighted.
• Select the cell in one corner of the range and while holding down SHIFT, either use the mouse to click on the cell in the opposite corner to mark the range, OR use the arrow keys to highlight the cells. 

Selecting Non-adjacent Cells

To select cells that are not next to each other, click on the first desired cell (or range of cells), and then while holding down Command, click on the other cell (or range of cells). As long as you hold the Command key down you can continue selecting non-adjacent cells. However, not all functions can be performed on multiple cells (e.g. Paste).

Selecting Rows or Columns

To select an entire row, click on the number of the row with the mouse. This will select all cells in the entire row (all the way to XFD). Holding down the mouse key you can also highlight multiple rows by scrolling down the numbers.

Selecting columns works in the exact same way except that you replace numbers with letters.

As with cells, you can highlight non-adjacent rows by pressing Command and clicking on the numbers of the rows/columns that you want to highlight.

Selecting the Entire Worksheet

Press Command+A and all of the cells in the worksheet will be highlighted. You can also click on the top left corner of the worksheet (directly above the 1 and directly to the left of the A). 

Changing the size of cells

Select the rows, columns or range of cells that you want to change the size of (note that changing the size of a range of cells will automatically change the size of the entire row(s) and/or column(s), not just the selected range). Go to Format in the Cells category under the Home tab.

When you click on Row Height (or Column Width) a dialogue box opens with the current row height (or width) where you can type in the new height (or width).

All of the selected rows (or columns) will change. 

You can also change the row height (or column width) with the mouse, but you must select either columns OR rows, NOT a range of cells. After selecting the columns (or rows) you can scroll the mouse over the column letters (row numbers) and the cursor will change to look approximately like this when it scrolls over the borders between columns (or rows):

Holding down the mouse (or clicker) you can drag the column (or row) to be the width that you want. You can also double click on the double arrow cursor and the columns will automatically fit to the size of the contents in the cells. All selected columns (or rows) will be changed.

AutoFit Column Width is also an option under Format in the Cell category under the Home Tab and will do the same thing.

Making Numbers Visible

Sometimes in Excel you'll get this:

This happens when the size of the number is wider than the width of the cell. You can format it by using the methods described above and then

You will be able to see all of your numbers!

Font

In the Font group in the Ribbon you can easily change the font type, the font size, the font color, the cell color (background color), and the font "texture" (bold, italics, underline).

There are a few more font formatting options that you can access by clicking on a specific cell or a group of selected cells and go to Format in the Menu and select Cells.... The Format Cells dialogue box that opens has a number of tabs. In the Font tab, there are additional font formatting options such as changing the underline type or striking through information in the cells. Text can also be changed to superscript or subscript. The Border tab and Fill tab also give you more formatting options than the Border and Fill links in the Font group in the Ribbon. 

Alignment

Alignment is both a group in the Ribbon and a tab in the Format Cells dialogue box.

Alignment allows you to align text (and numbers) in cells to the left, center, or to the right of the cell (horizontal alignment). It also allows you to align text towards the bottom, top, or center of the cell (vertical alignment). The different allignment options can be very useful for formatting data for readability. Also in Alignment is Wrap Text. Say you have a column header that is very long. You could increase the width of the column to see it in its entirety (see above) OR you could wrap the text so that it fit in the width of the column by making the row larger. 

Here we have 3 headers, but only the final one can be read completely.

If we highlight the cells and select Wrap Text...

You get:

You can alter the column width so that words are not cut off. You can also set the horizontal and vertical alignments to your preference. In the Alignment group you can also change the orientation of the writing by clicking text orientation button. The dropdown arrow will give you orientation options. You can also change the indentation of the text by clicking on the indentation buttons just below the orientation button. Also, notice that the text was vertically centered in the following example. Selected Alignment items are highlighted with a darker grey.

Finally Merge & Center will allow you to merge selected cells and center the context in the newly created larger cell.

Number

Number is both a group in the Ribbon and a tab in the Format Cells dialogue box.

Number allows you to format cells to different data types (general, date, time, number). By clicking on the arrow next to the Number Format bar (with default option "General") you can find many number formatting options, as well as Custom, which will take you to the Format Cells dialogue box.

In Number you can also adjust the number of decimal points, add monetary signs, change to a percent (which multiplies the information in a cell by 100 and adds %), or add commas (changing 1000 to 1,000).

You can change the monetary symbol by clicking the arrow next to it to open a menu for a few options and the option to open the Format Cells dialogue box for even more options.

Copying & Cutting

Select the data (cell, cell range, row(s), or column(s)) that you would like to copy or cut (for help selecting, click here). You can copy nonadjacent cells, but you cannot cut them. You will get this error message when you try to cut nonadjacent cells.

This will also happen sometimes if you are selecting multiple non-adjacent rows or columns and unintentionally click on a cell instead of the row number or column letter. You will have to restart the selecting process if this happens.

When you copy information it will remain in the original location after it is pasted, whereas if you cut the information it will be removed from the original location after you paste it. Cut will not remove the information if you do not paste (to delete information scroll down). 

Once you've selected the relevant data, there are 3 ways that you could Copy or Cut:

• CTRL + click to open this menu and select Copy or Cut.

• Press Command+C to Copy and Command+X to Cut.
• Go to Edit in the Main Menu Bar and select copy in the dropdown menu. If you want to Copy as Picture, hold the Shift button on the keyboard and click the Edit menu.

Paste

Now that we've covered the ins and outs of cutting and copying... we must paste! As with Cut and Copy there is more than one way to Paste.

• Press Control on the Keyboard while clicking on the cell(s) into which you would like paste, and dropdown menu will appear. You will see the Paste options are far more extensive than Cut and Copy. By scrolling the mouse over Paste Special you get even more options.

Click on the Paste icon in the Ribbon in the Edit group.  There is an arrow under the icon that will open the options also found in the menu when you right click on the cell will transpose these data:

To look like this when you select the Transpose option.

Additionally, you can click on Paste Special to open this menu:

• You can also Paste by typing Command+V. This pasting option will conform to the data type that you are pasting. For example, a number will remain a number, a formula will remain a formula, and a date will remain a date. 

Paste as Values

Sometimes you might want to paste a column full of numbers generated from an underlying formula. If you try to paste directly, Excel will paste the formulas and not the values, which in many cases means you will get a bunch of zeroes: 

In this example, instead of having cell E12 contain the number 4, it contains 0 because Excel is trying to add up C12 and D12 (which are empty cells). In order to paste the original column of values, select the Paste Special option under Edit: 

Select "Values" and you will get this after clicking okay:

Now you have the number values rather than the underlying formulas. 

Inserting Rows or Columns

Inserting rows and columns is slightly different than inserting a cell or cell range. So we'll start with rows and columns. The instructions and examples that follow are for columns, however it is basically the same for rows. Replace column with row and right with under.

There are multiple ways to carry out the following function.

• Select the column to the right of where you want to insert a new column. (Go here to remind yourself how to select the entire column.)

Press Control on the keyboard while clicking with the mouse, and select Insert from the dropdown menu that appears.

The new column will be inserted:

• Select the column to the right of where you want to insert a new column. Then click on Insert in the Cells group in the Ribbon. It will automatically add a column because you have selected a column. Insert also has a dropdown menu in which you can select to insert cells or a worksheet in addition to a column.

You can also insert more than one column at a time. For example, if you want to insert 3 adjacent columns here.

Highlight three columns starting with the column immediately to the right of where you want the inserted columns to be.

Then either CTRL+ click on the mouse and select Insert or click on Insert in the Cells group in the Ribbon.

Three blank columns appear maintaining the format of the selected columns.

You can also insert non-adjacent columns (for selecting nonadjacent columns go here). It can get a little tricky predicting where the new columns will be inserted. Columns will always appear to the left of the selected columns. For example, with these columns selected

you get these columns inserted:

If you had anticipated having an inserted column every other column, you didn't get it. But you'll notice that there is an inserted column to the left of the 'Less than High School' column, to the left of the 'Some College' column, and to the left of the 'Advanced Degree' column, which were the three selected columns.

Remember that the process for inserting rows is the same; simply replace rows with columnsand right with under (or left with above).

Inserting Cells and Cell Ranges

Essentially, the process for inserting cells is the same as the process for inserting rows or columns. Say you wanted to insert a range of cells between the 1976 and 1977 data in the example below but you didn't want to affect the data to the right. Inserting new rows would add new rows to that data as well. Instead, you can insert a cell range.

Select the cell range the size that you would like to insert (in this case we are inserting a 4 x 9 cell range). Start the selection immediately below where you want the cells inserted because we are inserting a range of cells instead of rows.

As with inserting rows and columns you can either CTRL + click the mouse and select Insert or you can click on Insert in the Cells group in the Ribbon. If you click on Insert in the Cells group, it will automatically shift the cells down. If you right click the mouse and select Insert, you'll get this message:

In this example, we want to shift the cells down, however, there are situations in which you will want to shift the cells to the right. By opening this menu you can specify which you want, in case the default action when you click Insert in the Cells group is not the one you want. You can also reach this menu by clicking the arrow under Insert in the Cells group and selecting Insert Cells. 

The default when you click Insert in the Cells group is not always to shift downwards. The default direction is based on your selection.

When new cells, columns, or rows are inserted, references (e.g. formulas relating to the cells) will adjust accordingly (see formulas for more on this).

Deleting Cells and Cell Ranges

Say you decided you didn't need to have those extra cells between the Bronx and Brooklyn data after all, but you couldn't delete the whole rows because you still have that data set to the right. You select the cells you want to delete.

There are multiple ways to do this.

• CTRL + click on the mouse and select Delete. This will give you a similar menu as when you inserted except it will say Delete and it will specify opposite directions for moving cells (move them up instead of down or left instead of right).

• You can also click on Delete in the Cells group in the Ribbon with the same caveats as mentioned when inserting (the default direction may not be the direction you wanted), or clicking on the dropdown menu under Delete and selecting Delete Cells to open the same menu in the image above.

Deleting Rows and Columns

Deleting works in very much the same was as inserting except that you click on Delete instead of Insert. 

Say you wanted to delete the selected rows.

*Image Missing*

There are multiple ways to do this.

• CTRL +  click on the mouse and select Delete. This will give you a similar menu as when you inserted except it will say Delete and it will specify opposite directions for moving cells (move them up instead of down or left instead of right).

• You can also click on Delete in the Cells group under the Home tab with the same caveats as mentioned when inserting (the default direction may not be the direction you wanted), or clicking on the dropdown menu under Delete and selecting Delete Cells to open the same menu in the image above.

Delete Content Without Deleting Entire Row

There are multiple ways to do this.

• Press DELETE or BACKSPACE.
• CTRL + click the mouse and select Clear Contents. This will remove content but not the actual rows.
• Select Clear in the Editing group under the Home tab. This will automatically provide a dropdown menu.

• You can also delete the contents of a cell while still entering the information (before pressing ENTER) by pressing ESC.

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.

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

• 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

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).

• 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)
• Press Command+Y to Redo the most recent Undo (note that Command+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.

• Click the Repeat icon where you want to repeat your last action.
• Press Command+Y to Repeat the most recent action once. This action works for both Repeat and Redobecause 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

Find

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.

• 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

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.

Sort

Sorting can be extremely useful when working with data sets. A basic Sort will sort information in columns from smallest to biggest or biggest to smallest (or in the case of text from A to Z or Z to A). As an example, let's assume you are interested in the Census Tracts that showed the largest population percent change between 2000 and 2010. Since the file is organized by Census Tract it is difficult to tell which areas showed the largest percent of growth.

We can sort the Percent Change column (H) from biggest to smallest.

Sorting a Single Column

Click on a cell in the column we want to sort (H) under the headers (headers or headings are essentially the titles in the columns). Under the Data tab select the Decending option in the Sort group in the Ribbon.

You can also sort using the Sort Dialogue Box, found under Data in the Main Drop Down Menu:

Sorted data will look like this:

The cells with '-' were created since the 2000 census and so there is no way to measure the population change in that particular area. You could do a Find & Replace if you would prefer to change those (go to the Manipulating Data I page to find out how). When we scroll down we find the areas that demonstrated the largest amunt of growth between 2000 and 2010. You'll also notice that the boroughs are intermixed:

Data can also be sorted using the Sort dialogue box which you can get to by clicking on the arrow next to Sort in the Sort & Filter group under the Data tab and select Custom Sort.

Using the dialogue box you can specify if your data does or does not have headers. You can specify the column under Column, the sorting criterion under Sort On, and the sorting order under Order. Notice that the to-be-sorted data is selected- this happens automatically IF you have selected a cell in the dataset. You can also highlight the range that you want to sort.

Sorting Multiple Columns

You can sort multiple columns simultaneously. For example, say you wanted to look at the largest percent change as before but you wanted it sorted by borough, so that you could see the largest percent change in the Bronx, and then scroll down to Brooklyn, and so forth. In the Sort dialogue box fill in Sort by with Column A (borough names), Sort On with Values, and Order with Largest to Smallest. Then click on the '+' button and fill in the second row: Then by with Column H, Sort On with Values, and Order with Largest to Smallest.

Then select OK. In the example, we've scrolled down so that you can see where the Bronx ends and Brooklyn begins. You'll notice that the since the percent change is sorted from largest to smallest, we are looking at the largest decrease at the end of the Bronx data and the largest increase at the beginning of the Brooklyn data.

Some things to note about Sort

• Make sure all of the items being sorted are of the same data type. If you have numbers stored as text and numbers stored as numbers (see Formatting Cells for more about data types) then the numbers stored as numbers will be sorted before the numbers stored as text.
• Also be sure to remove any spaces preceeding information in the cells. Sometimes data sets will have 'leading spaces' in which there is a space preceeding the number in the cell. This can occur if the original data file was not an Excel spreadsheet.
• Be careful when sorting items that include formulas (more on that soon) because the return values could change.
• Keep column headers in one row (otherwise, you can't sort by the full column header). In our current example data, we were sorting by column names rather than headers. That is because the headers covered 3 rows. If the header is in a single row, then you will select the header title of the column instead of the column letter.
• Also note that any hidden columns (or rows) will not move until you sort, thereby changing your data set. More on hiding and unhiding below.

Auto Fill & Series Fill

One of the great features of Excel is the Fill option. Instead of re-typing the same data or formula, you can let Excel copy it into the cells (AutoFill). Or instead of typing the numbers 1-100, you can set Excel to fill them in (Series Fill).

Auto Fill

Let's say you wanted to reformat the example data so that the terms referring to the 5 boroughs were represented as numbers. Since the Bronx is first, you give it number 1. But instead of typing the number 1 for all the data points for the Bronx (and continuing with all the other boroughs), you can use Auto Fill. Type the number 1 in the first two cells.

Select the two cells. Move the cursor over the bottom right corner of the selection. It will turn into a small black plus sign. When it does, click and hold down the mouse key and drag it down to cover all of the cells that you want to have the number 1 in. You'll notice that the selection border turns gray and that there is a small box with a 1 in it below and to the right of the plus sign. This tells you what is being input into the cell.

When you release the mouse key, all of the selected cells will have a 1.

Why did you have to enter the 1 in 2 cells?  Read on to find out!

Series Fill

Say you wanted to count the number Census tracks in the Bronx and you don't completely trust your subtraction skills (remember, there is data in row 9 so you can't simply subtract 9 from the final row number of the Bronx data). But if you used a Series fill, then Excel would tell you how many Census tracks there are for the Bronx by counting each one. Type the number 1 in I9.

Click on the cell (I9) and move the cursor over the bottom right corner of the cell. When the cursor turns into a small black plus sign, click down the mouse key and drag it down until the end of the Bronx data.

Another way that you can do a Series Fill is by going to Fill in the Editing group in the Home tab. You’ll first need to select all of the cells that you want filled in and type the first number you want the series to start with in I9. Then click on Fill. Select Series.

In the dialogue box that comes up you can change the parameters of the series. For example, if you wanted to increase it by 2 numbers instead of one, you would change the Step Value from 1 to 2. You can also set the Stop value or alter the Type (note that you can get to AutoFill here by selecting it under Type).

Hiding & Unhiding

There are times when you want to keep information in the worksheet, but you do not necessarily want to be looking at it. In which case, you can hide it. And anything hidden will at some point need to be unhidden (otherwise, you could just delete it).

Hiding Rows or Columns (or worksheets)

Select the row or column that you would like to hide. CTRL+ click the mouse and select Hide at the bottom of the list.

The selected row(s) or column(s) will disappear. Notice that not only does the information in the column or row disappear, but the row column letter/ row number disappears too. In this case, rows 14, 15, 16, 17 and 18 are hidden. This is how you can tell if there are hidden rows or columns in the dataset (notice the blue numbers and line).

You can also hide rows or columns from the Format button in the Cells group on the Home tab. Select Hide & Unhide and then select if you want to hide rows, columns, or even the entire worksheet! To hide an entire worksheet, CTRL+click on the worksheet name and select 'Hide.'

Unhide

You may want to revisit hidden information again. Also, sort will not include hidden rows or columns, so if you want to preserve the information you should unhide rows or columns before sorting.

To unhide a row or column that is not the first row (row 1) or column (column A) select the surround rows or columns. So if you are interested in unhiding rows 14, 15, 16, 17 and 18, select rows 13 and 19. CTRL+ click and scroll down to select Unhide.

The hidden rows or columns (in this case rows) will reappear.  As before you can also unhide columns or rows from the Format button in the Cells group on the Home tab. Select Hide & Unhide and then select if you want to unhide rows, columns, or the entire worksheet (Again, to unhide a worksheet, you have to CTRL+click on one of the visible worksheet tabs and select 'Unhide').

When you select to unhide a worksheet, you will get a dialogue box listing the hidden worksheets for you to pick the one that you want to unhide.

If you have hidden the first row (row 1) or column (column A) you cannot select the surround rows or columns (because what comes before row 1 or column A?). One way you can unhide them is to select all of the columns/rows. Then unhide the rows/columns from the Format button in the Cells group on the Home tab and selecting Hide & Unhide.  Note that this will unhide every row and column. 

To unhide ONLY the first row or column, type A1 into the Name Box next to the Formula bar. Then from the Format button in the Cells group on the Home tab, select Unhide Columns (or Unhide Rows). The missing first column (or row) will return.