Filling Cells with a Series of Data in Excel 2010

Remember those tests in school where the teacher would make you number a piece of paper from 1 to 10? Have you ever used MS Excel to make a list and put a number in each cell by typing one number at a time, one cell at a time? What about a date?

Typing any type of series in MS Excel 2010 just takes a matter of minutes if you know what you're doing. You can easily start a series, whether it's a series of numbers, dates, or a built in series for days, weeks, months, and years.

Here's an example. In the screen shot below, we wanted to number the columns 1 through 10. We started typing, but got tired at number 5.

MS Excel gives you an AutoFill handle bar that you can use to complete this series automatically. The handle bar is located at the bottom right hand corner of a selected cell, as shown below.

To AutoFill the rest of the series, move the mouse over the handle bar. A solid black cross (+) will appear. Once that appears, you drag the handle bar over cells to add a series.

Keep in mind, if you only have one cell selected, which we do in the example above, it will AutoFill the other cells with the number 5 as well. It will NOT complete the series because a series is not established. You have to establish the series first.

To use AutoFill to complete a series:

Start a series.
Select the cells in the series.

Drag the handle bar over the cells that you want to fill in with a series of data.
The series will be filled in for you.

Things to Remember:

Drag to the right or down to increase or ascend a series of numbers, dates, etc.
Drag to the left or up for descending numbers, dates, etc.
Enter at least two numbers, etc. in the series before using AutoFill

Once the series is filled in, you will see an AutoFill options box appear just below the handle bar. You can choose rather to format – or not format – the data in the cells.

Note: Series of numbers or dates do not have to increase by only one. You can have a series that is comprised of even numbers, patterns, odd days of the month, etc.

Editing Cell Data In the old days of pencil and paper or a typewriter, making changes to anything was a hassle. You had to use that messy white out fluid. Or even worse, take an eraser to it and chance leaving a smudge mark or tearing the paper. MS Excel 2010 makes creating – and editing – spreadsheets a lot easier because correcting errors is easy and mess free.

To be able to edit data in a cell, you first need to put a cell in active mode. You can do this by selecting the cell and pressing F2 or simply clicking inside the cell. You can then type at the end of the data. Use backspace to delete data. When you've finished, simply hit enter or click in another cell to save your changes.

You can also:

Double click the cell that you want to edit.
Use the arrow keys to navigate through the data to find an insertion point.
Use backspace to delete data.
Press the Enter key to accept changes.

Find and Replace, Go To Cell Data

Let's say you want to find the number of candy bars that sold in the month of March, but you didn't want to scroll through a large worksheet to find that information. MS Excel 2010 offers you the Find and Go To features to make locating the data you need easy.

Go to the Home tab then click Find & Select in the Editing group.

Select Find. Type in the data that you want to find. Maybe it's the word 'March.'

Select 'More' to choose preferences for your search, as seen below.

You can now specify where you want to look. Then you can choose if you want to find all instances or just the next one in the worksheet.

Go To Using the Go To feature, you can ask MS Excel 2010 to go to a certain cell. This saves time over scrolling through a worksheet.

To use Go To, go to the Home tab, then select Find & Select again. Now, choose Go To.

When you select Go To, this dialogue box will appear:

Type in the name of the Column (For example, A), then the number of the row (3, for example). When you type A3 in the box, then click OK, MS Excel will take us to A3 automatically. That cell will be selected.

Locking Rows and Columns By Splitting Panes You can view two areas of a worksheet and lock rows or columns into one place by splitting panes. When you split panes, you can scroll in the two areas of the worksheet that you've locked, but the non-scrolled areas remain visible. You'll see more of what we mean in just a minute.

To lock and split panes:

To lock rows, select the row below where you want the split to appear. To lock columns, select the column to the right of where you want the split to appear. To lock both rows and columns, select the cell below and to the right of where you want the split to appear.

In this example, we're going to split a column.

First, select the column that you want to split. We're going to split L in the example below.

Then, go to the vertical split bar and click on it. The horizontal split bars and vertical split bars are shown below. You can also create both of them at the same time by clicking the View tab and then clicking split.

You'll see a gray bar, like pictured below.

As you can see in the picture below, the column is split. To remove the split, double click on the gray bar that splits your columns (or rows).

Locking Rows and Columns by Freezing Panes When you freeze panes, you select rows and columns that remain visible when you scroll in the worksheet. You freeze panes to keep row and column labels visible as you scroll through.

The screenshot below shows a worksheet before we lock and freeze panes.

Want to learn more? Take an online course in Excel 2010.

Let's say we had a lot of rows and columns of data about the bread, butter, and eggs listed above. Something like this:

Lock the row or column that you want to lock using the same steps we used when splitting panes. In this example, we're going to lock and freeze a row.

I can see that on November 11^th, I had 113 loaves of bread. But if I scroll over past that date, I might not be able to see that the numbers represent a loaves of bread anymore because I couldn't see the column that contained bread, butter, eggs, etc. To make it so that I can see the column that says bread, butter, etc. – and still see the date, I can freeze rows and columns.

To freeze rows or columns, go to the View tab and click Freeze Panes in the Window group.

In this example, we want to freeze the first column.

When we do this, a line appears after the first column.

Now, we can scroll over to the right, and we can still see the first column.

To unfreeze, follow the same steps, but select Unfreeze Panes.

Spell Check

As you work on a worksheet or after you complete it, naturally you'll want to check it for typos and misspelled words. MS Excel 2010 will help you check your worksheets for typos and misspelled words using a feature called Spell Check. Now, keep in mind, Spell Check should never be used as a substitute for proofreading it yourself because there are some mistakes that it won't catch, but it is an excellent helper and shortens editing time.

To use Spell Check, click on the Review tab then Spelling in the Proofing group. If any errors appear in your worksheet, you'll see this dialogue box:

As you can see in the above example, Spell Check caught a misspelled word and highlighted its first choice as a replacement. It just so happens the first suggestion is the correct one. To accept the changes, we click on Change. If we wanted the word "Example" spelled "Exxymple," then we would have clicked on Ignore Once. To change all instances of the misspelling (or ignore all instances) select Ignore All or to correct all instances where the word was misspelled, we'd select Change All.

At the bottom of the dialogue box pictured below, you'll see a button entitled Options. If you click on that, this dialogue box will open:

This window allows you to pick a dictionary language and to also add a word to the MS Excel 2010 dictionary. Using the made-up word "Bredad" as an example, let's say bredad was a product name that we were using in a worksheet. We wouldn't want MS Excel to constantly point that out as a spelling error. If that is the case, we would click the Custom Dictionaries button, shown above.

Now, click the Edit Word List button.

We then typed in bredad, then click Add to add it to our custom dictionary so that it's recognized by Excel. Click OK when you're finished.

AutoCorrect

Let's go back to the Excel Options dialogue box.

Click on the AutoCorrect Options button (as pictured above) and you'll see a new dialogue box.

This dialogue box will allow you to program Spell Check and Excel to automatically correct some errors for you as you type.

Track Changes Have you ever edited something, then realized you liked it better the way it was? Or have you ever edited something someone else wrote and wished there were a way to communicate your thoughts without typing in their document or worksheet? If either of these situations has ever applied to you, then learning how to use Track Changes and Comments in Excel 2010 will appeal to you.

Track Changes gives you the ability to edit or change data in the worksheet and make note of those changes. MS Excel 2010 will make note of the changes by highlighting the outside of the cell with a different color.

In the upper left hand corner of the cell, an inverted triangle will also appear. Clicking on that will tell you exactly what changes have been made so you know what you've added, what you've deleted, and what formatting changes you have made. This also helps when you have several people changing and editing a workbook.

To turn on Track Changes in MS Excel 2010, go to the Review tab and click Track changes in the Changes group, then select Highlight Changes.

Put a check mark in the box beside Track Changes While Editing…

Then, you can select when, who, and where at in the worksheet that you want to track changes.

The highlight changes on screen box should be checked, unless you want the changes listed on a new sheet.

Click OK.

Now, when you or anyone else makes changes or edits the workbook, these changes will be noted.

Each user who makes changes to a workbook will be assigned a color by MS Excel 2010. For example, Mary's changes may show as a cell highlighted in red, Joe's in blue, etc. These are assigned by default. You cannot assign colors to users.

When you click on the triangle in the upper left hand corner of the cell, the user's initials will appear. This is another way to let you know who made what changes.

Accept or Reject Changes Whenever you or someone else makes a change to a worksheet using Track Changes, you can then decide whether to accept or reject the change.

To do this, go to the Review tab, click Track Changes, then select Accept/Reject Changes.

This dialogue box will appear:

You can select when the changes were made that you want to accept or reject, who's changes, and where at in the worksheet that you want to review changes.

Click OK when you've selected which changes.

A new dialogue box will appear:

It shows who made the change, what change was made, then gives you the option to accept the change, reject it, accept all changes made in the worksheet, or reject all of them.

Comments You can also add comments in individual cells in a worksheet to question the data or provide some other input. MS Excel 2010 will make note that a comment was inserted into the worksheet by placing a red triangle in the upper right hand corner of the cell where it was added. The comment will appear in a pop up box near that cell, as in the example below: