Excel Advanced Skills2

Microsoft Excel Description of 25 Advanced Skills

AutoFormat

Select the range of cells.

Open the Format Menu and select Auto-Format.

In the Table format box, choose a format.

Nested Functions

	Select the cell in which to enter the functions.
	Click the Paste Function button and enter the first function.
	Enter the next function(s).
	Confirm the dialog box.

Use the If function.

	Select the cell where the formula will be entered.
	Click on the Paste Function button and select the desired function.
	Include the arguments that the function requires and confirm.

Cell references.

	In a cell, enter the formula that contains the desired cell reference.
	Make the cell reference relative or absolute.
	Press the enter key to confirm the cell.

Insert a Picture

	Select the cell where the picture will be inserted.
	From the Insert Menu, select picture.
	Choose where to insert the picture from, Clip Art or from File.
	Open the folder containing pictures.
	Select the desired picture and click on Insert.

Page Titles

	Open the file menu and select Page Setup.
	Select the Sheet Tab.
	In the Print Titles area, select the Text Box, Rows to repeat at the top.
	Specify the rows that are to be printed at the top of each page and confirm the dialog box.

Edit a Chart Title

	Click on the Chart Title to select it.
	Click inside the Selection Border.
	Edit the text of the chart title.
	Click on the format Chart Title button of the Chart Toolbar to edit the format.
	Click outside the selection border to confirm.

Edit a Chart Legend

	Click on the Chart Legend
	Open the Chart menu and select Source Data
	In the Series window, click on the desired series to edit.
	In the Name Box, enter the new name.
	Confirm the dialog box.

Edit X and Y Axes

	Click on the desired axis to edit.
	To change the format click on the format Axis button on the Chart Toolbar and make the changes.
	Confirm the dialog box.

Set Print Area

	Select the range of cells to print.
	Open the File Menu and select Print Area.
	Choose Set Print Area.

Rotate Text in a Chart

	Click on the desired Axis or Title to edit it.
	Click on the Format Axis or Format Chart title button on the Chart Toolbar.
	Click on the Alignment Tab.
	Click on the marker that shows the Text angle desired.
	Confirm the dialog box.

Multi-Sheet Formulas

	Begin the formula with an (=) equal sign.
	To enter a cell reference from another sheet, go to that sheet and click on the cell to be used in the Formula.
	Without switching sheets, continue the Formula by entering the next operator.
	Return to the first sheet and continue the formula as usual.
	Press the enter key when the formula is finished.

Display formulas

	Open the Tools menu and select Options.
	Select the View Tab.
	Click the formula's check box and confirm the dialog box.

Hide Row/Columns

	Select the desired Row or Columns to be hidden.
	Open the shortcut menu by clicking the right mouse button.
	Select Hide.

Link files

	Switch to the file to link from.
	Copy the desired cells to the clipboard.
	Switch to the file to link to.
	Open the edit menu and Select Paste Special.
	Click on the Paste Link button.

Run a Macro

	Select the sheet where the macro will be run.
	Open the tools menu.
	Select Macro and then Macros.
	Select the name of the Macro and click on the Run button.

Record a Macro

	Open the Tools menu.
	Select Macro and then Record New Macro.
	Type the name of the Macro and confirm the dialog box.
	Perform the actions to record.
	Click on the Stop Macro Button of the Stop Recording Toolbar when done.

Data Validation

	Select the desired cells.
	Open the Data Menu and Select Validation.
	Select the settings tab and enter the type of data and the conditions of validation.
	Select the Input Message Tab and enter the title and the Input Message.
	Select the Error Alert tab and enter the style, title, and Error Message.
	Confirm the Dialog Box.

Create a Database

	Enter the data in a table with labels at the top of each column.
	Make sure there are no blank rows in the list of data.
	Leave a blank row or column between the Data Base Table and other data on the worksheet.

Sort data

	Select one of the cells in the database table.
	Open the Data Menu and select Sort.
	In the Sort By Area select the desired field name and sort order.
	Confirm the dialog box.

Filter a Database

	Select one of the cells in the Database Table.
	Open the Data Menu, select Filter and then Autofilter.
	Select the filter criteria and confirm.

Add Records

	Select one of the cells in the Database Table.
	Open the Data Menu and Select Form.
	Click on the New button and fill in the fields.
	When all records have been added, close the dialog box.

Use a Custom List.

	Select the cell where the list will be started.
	Enter the first item into the cell.
	Drag the fill handle to the end of the list.

Create a Custom List.

	Enter the list data on the worksheet in a range of cells.
	Select the range of cells.
	Open the Tools menu and select Options.
	Under the Customs Lists tab, click on the Import button and confirm.

Prevent access to PivotTable detail

	When you double-click a cell in the data area of a PivotTable, Microsoft Excel displays a list of the source data summarized by that cell. Use this procedure to turn off access to this source detail:
	1 Click a cell in the PivotTable.

2 Click Options (PivotTable menu, PivotTable toolbar).

3 Under Data source options, clear the Enable drilldown check box.

To Specify which cells can be changed after a worksheet is protected:

To unlock cells so that they can be changed, the worksheet must not be protected.

1 Select the cell range you want to unlock.

2 On the Format menu, click Cells, and then click the Protection tab.

3 Clear the Locked check box.

Note After you protect the worksheet, the cells that you unlocked in this procedure are the only cells that can be changed.

Tip To move between unlocked cells on a protected worksheet, click an unlocked cell, and then press TAB.