Advanced Excel

Advanced Microsoft Excel



Contents


  • Toolbars
  • Toolbar, Menu
  • Toolbar, Standard
  • Toolbar, Formatting
  • Toolbar, Title Bar
  • Toolbar, Task Bar
  • Toolbar, Status Bar
  • Toolbar, selecting a toolbar
  • Excel Formulas and Functions
  • AutoSum Function
  • Formula, Entering one into a Cell
  • Function, Entering one into a Cell
  • Formula, copying
  • Formula, moving
  • AutoCalculate Function
  • Error: Fixing the #### Error
  • Error: Fixing the #DIV/0! Error
  • Error: Fixing the #NAME? Error
  • Error: Fixing the #VALUE? Error
  • Error: Recognizing the #REF! Error
  • Error: Recognizing Circular References
  • Excel Worksheets
  • Styles, Applying to Numeric Data
  • Bold, applying to a cell
  • Italic, applying to a cell
  • Underline, a cell
  • Alignment of cells
  • Wrapping Text within a Cell
  • Merge and Center on a Cell
  • Borders
  • Font Settings
  • Font Color (Also see Color of Cells)
  • Color of Cells (Also see Font Color)
  • Text Orientation
  • Row Height
  • Row Height for Multiple Rows
  • Column Width
  • Column Width for Multiple Columns
  • Row Freezing
  • Column Freezing
  • AutoFormat
  • Formatting, Conditional
  • Formatting, copying Conditional Formatting
  • Comments, Viewing Cell Comments
  • Comments, Adding Cell Comments
  • Chart, Creating
  • Chart Descriptions
  • Chart Title, Editing
  • Charting an Additional Line on an Existing Chart
  • Clip Art
  • Hyperlinks
  • Email Addresses
  • Headings and Footers
  • Headers and Footers, previewing
  • Margins
  • Margins, previewing
  • Print Area
  • Print previewing
  • Printing Worksheets
  • Suggestions for Using Excel for Practice


Getting started


Turning on PC


Toolbars


Toolbar, Menu


The Menu toolbar is at the top of the screen with a descriptive word on each button.


Toolbar, Standard


The Standard toolbar has icons and is below the Menu toolbar.


Toolbar, Formatting


The Formatting toolbar shows formatting information such as font and type size and is below the Standard toolbar.


Toolbar, Title Bar


The title bar is across the top of a document.


Toolbar, Task Bar


The task bar is across the bottom of the screen. It has the Start button at the far left.


Toolbar, Status Bar


The status bar is across the bottom of the screen, just above the task bar.


Toolbar, selecting a toolbar


To view and select toolbars, click View on the Menu toolbar; click Toolbars; click the toolbars you want to see.
Those that are already in use are shown with a checkmark to the left of them.


Excel Formulas and Functions


AutoSum Function
  • Click an empty cell below a column with numbers where the column total is to be placed.
  • Click the AutoSum button on the standard toolbar. The AutoSum button looks like a Greek sigma or a modified letter "E".
  • The most likely range of numbers to be summed are enclosed in a dotted line.
  • Click Enter.


Formula, Entering one into a Cell
  • Click an empty cell below a column with numbers where the column total is to be placed.
  • Enter an equal (=) sign.
  • Following the equal sign, enter the addresses of the cells above that are to be summed. Put a plus sign (+) between each cell address. No spaces are needed in a formula.
  • Click Enter.
  • Note that a column was used as an example here, but this method works for rows, also.

Function, Entering one into a Cell


  • Click an empty cell to the right of a row of cells. This is where the result will be displayed.
  • Click the Paste Function button on the standard toolbar. The Paste Function looks like lower case "fx".
  • Double-click the AVERAGE option. Excel makes an assumption about the cells you wish to have included in the calculation. If you wish to select different cells, Excel gives you that option.
  • Click OK.

Formula, copying


  • Click the cell that contains the formula you wish to copy.
  • Click the copy button on the standard toolbar. The button looks like two pages, side by side.
  • Select the cells where you want the formula copied.
  • Press enter.
  • Or
  • Click the cell that contains the formula you wish to copy.
  • Press Ctrl + C.
  • Select the cell or cells to which you wish to copy the formula.
  • Press Ctrl + V.

Formula, moving


  • Click the cell that contains the formula you wish to move.
  • Click the cut button on the standard toolbar. The button looks like a pair of scissors.
  • Select the cells where you want the formula copied.
  • Press enter.
  • Or
  • Click the cell that contains the formula you wish to copy.
  • Press Ctrl + X.
  • Click the cell or cells to which you wish to copy the formula.
  • Press Ctrl + V.

AutoCalculate Function


  • Select the cells you want to AutoCalculate.
  • Right-click the status bar. The status bar is just above the task bar at the bottom of the screen.
  • Click the calculation you want.
  • The result will be shown in a box on the status bar.

Error: Fixing the #### Error


  • (This error indicates the result field for a calculation is not large enough.)
  • Click the AutoSum button on the standard toolbar.
  • Press Enter. The ### signs will appear if the size of the result cell is not large enough to accommodate the result.
  • To fix this problem, click on the right column border of the column where the result is to be placed, and drag it to the right to increase the column width.

Error: Fixing the #DIV/0! Error


  • (This error indicates that an attempt was made to divide a number by zero.)
  • Click the AutoSum button on the standard toolbar. The AutoSum button is a Greek Sigma and looks like a modified letter "E".
  • Press the F2 function key.
  • Reenter the formula so that the dividend (the number used to divide by) is not zero.
  • Press Enter.

Error: Fixing the #NAME? Error


  • (This error indicates the formula contains and incorrectly spelled cell or function name.)
  • Click on the cell with the error.
  • Press the F2 function key.
  • Reenter the formula with correct spellings.
  • Press Enter.

Error: Fixing the #VALUE? Error


  • (This error indicates a cell used in the formula contains nonnumeric data or cell or function names that cannot be used in the calculation.)
  • Look at the formula to determine where the problem is.
  • Correct the values or names used in the calculation.

Error: Recognizing the #REF! Error


  • (This error indicates the formula contains reference to an invalid cell or one that has been deleted.)
  • Review the formula to find the cell that has been deleted or is a bad reference.

Error: Recognizing Circular References


  • (This error indicates a cell that is to contain the result is also used in the calculation. Sometimes this is desirable. Excel provides an alert message so that you can make sure you want the circular reference.)
  • Check the cell references in the formula to ensure they are the ones you want to use.

Excel Worksheets


Styles, Applying to Numeric Data
  • (e.g.: currency, commas and decimal points, etc.)
  • Select the cells to which a style is to be applied.
  • Click the desired style button on the formatting toolbar.
  • Or
  • To see additional styles, click Format on the formatting toolbar.
  • Click Cells.
  • Click the desired option.

Bold, applying to a cell


  • Select the cells to be modified.
  • Click the Bold, button on the formatting toolbar.

Italic, applying to a cell


  • Select the cells to be modified.
  • Click the Italic, button on the formatting toolbar.

Underline, a cell


  • Select the cells to be modified.
  • Click the Underline button on the formatting toolbar.

Alignment of cells


  • Select the cells to be changed.
  • Click the Center, Align Right, or Align Left buttons on the formatting toolbar.

Wrapping Text within a Cell


  • Select the cells to be wrapped.
  • Click Format on the formatting toolbar.
  • Click Cells.
  • Click the Alignment tab.
  • Select Wrap Text.
  • Click OK.

Merge and Center on a Cell


  • Select the cells you wish to center.
  • Click the Merge and Center button in the formatting toolbar. It looks like a cell with the letter "a" in the center and left and right arrows.

Borders


  • Select the cells to have the border modified.
  • Click the down arrow next to the Borders button.
  • Click the type of border you wish to apply.

Font Settings


  • Select the cells for which you wish to change the font.
  • Click the down arrow to the right of the font box on the formatting toolbar.
  • Click the font you want to use.
  • Click the down arrow to the right of the font size box on the formatting toolbar.
  • Click the desired font size.

Font Color (Also see Color of Cells)


  • Click on the down arrow to the right of the font color button on the formatting toolbar.
  • Click the desired font color.

Color of Cells (Also see Font Color)


  • Select the cells to be colored.
  • Click the down arrow next to the font color button on the formatting toolbar.
  • Click the desired shading or color.

Text Orientation


  • This option allows for text to be slanted in a cell.
  • Select the cells to be changed.
  • Click Format on the formatting toolbar.
  • Click Cells.
  • Click the Alignment tab.
  • Click the desired options in the Orientation area.
  • Click OK.

Row Height


  • Place the cursor over the bottom edge of the row header for the row you want to change.
  • Click, hold the mouse button, and drag the row edge to the desired size.
  • Release the mouse button.

Row Height for Multiple Rows


  • Place the cursor on the first row header.
  • To select the rows, click the mouse button, hold the mouse button, and drag the cursor past all of the row headers you want to synchronize.
  • Release the mouse button.
  • Change the row height for one of the rows. This will change all of the rows to the same row height.

Column Width


  • Place the cursor over the side of the column header for the row you want to change. The cursor changes to a two-headed arrow.
  • Click, hold the mouse button, and drag the column edge to the desired size.
  • Release the mouse button.

Column Width for Multiple Columns


  • Place the cursor on the first column header. The cursor changes to a two-headed arrow.
  • To select the columns, click the mouse button, hold the mouse button, and drag the cursor past all of the column headers you want to synchronize.
  • Release the mouse button.
  • Change the column width for one of the columns. This will change all of the columns to the same width.

Row Freezing


  • (This is helpful to keep the row headings in place when you scroll the worksheet.)
  • Click the row below the one in which you want to freeze.
  • Click Window.
  • Click Freeze Panes.

Column Freezing


  • (This is helpful to keep the column headings in place when you scroll the worksheet.)
  • Click the column to the right of the one in which you want to freeze.
  • Click Window.
  • Click Freeze Panes.

AutoFormat


  • (With this method, Excel uses predefined formats to format.)
  • Select the cells you want to AutoFormat.
  • Click the Format button on the formatting toolbar.
  • Click Autoformat.
  • Click the table format you want to use.
  • Check the sample to confirm your choice.
  • Click OK.

Formatting, Conditional


  • (This can be set up with three conditions. For example, a condition could be set to change the background of a cell to red whenever the amount in it exceeds $100,000.)
  • Select the cells you wish to have conditional formatting.
  • Click the Format button on the formatting toolbar.
  • Click Conditional Formatting.
  • Click the down arrow to select a desired cell value or formula condition.
  • Click the list to select the type of condition.
  • Enter the value of the condition (for example, $100,000.)
  • Click the Format button to preview the selected format when the condition is met.
  • Click the options you want to set (for example, the color of the cells).
  • Click OK.

Formatting, copying Conditional Formatting


  • Click the cell from which you want to copy conditional formatting.
  • Click the Format Painter button on the formatting toolbar. This button looks like a paint brush.
  • Drag the cursor over the cells you want the format copied into.

Comments, Viewing Cell Comments


  • Move the cursor to the comment marker in a cell. The marker is a red triangle in the upper right corner or the cell.
  • The comments will show.

Comments, Adding Cell Comments


  • Select the cell you want to add comments to.
  • Click the Insert button on the menu toolbar.
  • Click Comment.
  • Enter the text into the comment area.
  • Click the worksheet area to accept the comment.

Chart, Creating


  • Select the cells you wish to place on a chart. These cells are called a series.
  • Click the Chart Wizard icon on the standard toolbar. The icon looks like a three-dimensional vertical bar chart with a blue, yellow, and red bar.
  • Click the general type of chart you desire on the column at the left.
  • Click the specific chart subtype from those that are displayed. Note that there are two general types of line charts: stacked (the values of the second and subsequent series of cells to be graphed are added to the values of the cells in the first series) and line charts that are not stacked where the cell values in the second and subsequent series are plotted on the chart without being added to the first series.
  • Click Next.
  • Click Rows or Columns to choose which data to base the chart on.
  • Click Next.
  • Enter the titles for the chart.
  • Click Next.
  • Click where you wish to place the chart. (For example, As New Sheet).
  • Click Finish.

Chart Descriptions


  • To change a text description on the chart, click anywhere on the graph. This will change the menu toolbar to have a Chart menu button.
  • Click the Chart button on the menu toolbar.
  • Click Source Data.
  • Click the Series tab.
  • In this window you can add and change the name of the line(s), called a series, on the chart.

Chart Title, Editing


  • Click View on the menu toolbar.
  • Click Toolbars.
  • Click Chart.
  • Click an object on the chart. That object will be displayed in the Chart Objects area of the Chart toolbar.
  • Enter the new Chart Title.
  • Press Enter.

Charting an Additional Line on an Existing Chart


  • Click Chart on the menu toolbar.
  • Click Add Data. An add data window pops up.
  • Select the cells containing the values you want to plot on the new line to be added to the existing chart. As you select a cell, its information is converted to a location and placed in the box in the Add Chart window.
  • When you have selected all of the cells you desire, click OK in the Add Chart window and follow the directions. Your new series will be placed on the existing chart.

Clip Art


  • Click a cell where you want to place clip art.
  • Click the Insert button on the menu toolbar.
  • Click Picture.
  • Click Clip Art.
  • Click the scrollbar on the Microsoft Clip Gallery dialog box the view available clip art.
  • Double-click the clip art you select to be inserted.

Hyperlinks


  • Click a cell where you would like to place the hyperlink.
  • Click the Insert button on the menu toolbar.
  • Click Hyperlink.
  • Enter the path of the file or the Uniform Resource Location (URL) of the Web page in the Link to file or URL text box.
  • Click OK.

Email Addresses


  • (Clicking an email address brings up the default email client, ready to send a message.)
  • Click a cell where you would like to place the hyperlink.
  • Click the Insert button on the menu toolbar.
  • Click Hyperlink.
  • Enter mailto: followed by the desired email address. Do not put in any spaces.

Headings and Footers


  • Click the File button on the menu toolbar.
  • Click Page Setup.
  • Click the Header/Footer tab.
  • Click the desired header options.
  • Click the desired footer options.
  • Click OK.

Headers and Footers, previewing


  • Click the File button on the menu toolbar.
  • Click Print Preview.
  • Or
  • Click the Print Preview button on the standard toolbar. The Print Preview button looks like a page with a magnifying glass over the right edge.

Margins


  • Click the File button on the menu toolbar.
  • Click Page Setup.
  • Click the Margins tab.
  • Click the arrows for Top, Left, Right, and Bottom boxes to set the margins.
  • Click the arrows to set the Header and Footer margins.
  • Click OK.

Margins, previewing


  • Click the File button on the menu toolbar.
  • Click Print Preview.
  • Or
  • Click the Print Preview button on the standard toolbar. The Print Preview button looks like a page with a magnifying glass over the right edge.

Print Area


  • Select the cells you wish to print.
  • Click the File button on the menu toolbar.
  • Click Print Area.
  • Click Set Print Area.

Print previewing


  • Click the File button on the menu toolbar.
  • Click Print Preview.
  • Or
  • Click the Print Preview button on the standard toolbar. The Print Preview button looks like a page with a magnifying glass over the right edge.

Printing Worksheets


  • Click the File button on the menu toolbar.
  • Click Print.
  • Click the desired options and number of copies to be printed.
  • Click OK.
  • Or
  • Click the Print button on the standard toolbar. This uses default print options.

Suggestions for Using Excel for Practice


  • Home Inventory Listing for Insurance Purposes.
  • List of Names and Addresses for mailing bills and letters.
  • List of Names and Addresses for mailing holiday cards.
  • List of Birthdays and other important Dates.
  • List of important email addresses
  • List of important items.