This module introduces some time saving tools to make basic worksheet creation even easier. The AutoFill feature can help you quickly repeat formulas or do things like fill in the days of the week or months of the year. The AutoComplete feature can help you to quickly write functions. This module will also look at sorting and filtering your data to efficiently find the data you need. Finally, this module will introduce Sparklines – a new feature in Excel 2010 that allows you to create cell-sized graphs that can be helpful in showing trends.
AutoFill is a feature that quickly creates copies of a cell based on that cell’s contents. If the cell contains a formula, the formula is repeated with relative references. If the cell contains a date, AutoFill creates a list with that date as the starting point.
To use AutoFill, simply select the AutoFill handle and drag to fill the contents of consecutive rows or columns. The AutoFill handle is present for the active cell. It is a small square at the bottom right corner of the cell.
You can only fill in one direction at a time.
Use the following procedure to copy a formula using the AutoFill handle. This example uses the formula in the “# Left to reorder” column in the Inventory sample worksheet.
- Click on the cell with the formula you want to copy. Excel displays a handle around the cell.
- Drag the handle at the bottom right corner of the cell to the end of the range of cells where you want to copy the formula.
- Release the mouse button at the end of the range. Excel displays a menu to help determine you AutoFill options. For this example, we want to Copy Cells.
Use the following procedure to create a list using AutoFill. This example creates new columns in the Budget worksheet to cover the second six months.
- Create a new column heading with the text “July” in cell J4.
- Click on that cell to make it active. Excel displays a handle around the cell.
- Drag the handle across the columns. Excel displays a screen tip showing what AutoFill will place in those cells.
- Release the mouse button at the end of the range. Excel displays a menu to help determine you AutoFill options. For this example, you can choose either Fill Series or Fill Months.
AutoComplete is an Excel feature that helps you save time. You can begin typing the name of a function that you want to use in a cell. Excel displays a list of functions that start with those letters. Then you can simply select the appropriate function from the drop down list. This helps save time for the functions you use frequently. It also helps ensure accuracy for your arguments in the function.
Use the following procedure to use the AutoComplete feature.
- Begin typing the SUM function. As soon as you type the Equals sign and the letter S, Excel displays a possible list of matching functions.
- To select the SUM Function from the list, double-click on the SUM function.
- Excel enters the function, but you must still enter the arguments. You can simply click on multiple cells, or click and drag to select a cell range. You can also type in the cell references.
- Enter the final parenthesis mark to end the function.
- Press ENTER to enter the function in the cell.
Sparklines are tiny charts that are actually in the background of a cell. Sparklines can be used to visually display trends that might be harder to spot just from the data alone. Sparklines provide valuable information at just a glance.
Use the following procedure to create a sparkline. This example shows a sparkline for the “heat” category on the budget.
- Click on the cell where you want to create the sparkline.
- Click the Insert tab.
- Click the type of Sparkline you want to include from the Sparklines group. For this example, line or bar chart work best.