Learn programming VBA with excel quickly


Télécharger Learn programming VBA with excel quickly

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


   

Outline for Excel tutorial

1)    Entering and editing data

a)    Basic entry

i)   Text ii) Date iii) Values

b)    Selecting cells

c)  Editing data 

d)    Moving data

i)   Click and drag ii) Copy and paste

e)    Formatting cells

i)   Font ii) Text alignment iii) Number/text format iv) Borders and patterns

f)   Inserting and deleting rows and columns

i)   Inserting cells, rows, or columns ii) Deleting cells, rows, or columns

g)    Changing row or column size

h)    Transposing rows and columns

i)   Sorting data

j)   Importing data

i)   Importing a data file ii) Text to columns

k)  Saving

l)   Printing

i)   Page setup

ii)  Print area 

2)    Creating and correcting formulas

a)    Using functions

b)    Nesting functions

c)  Writing functions

d)    Formula errors

i)   Finding and correcting formula errors ii) Common errors

3)    Charts

a)    Creating a chart

b)    Editing the chart display

i)   Font ii) Color iii) Scale iv) Gridlines v) Legend

vi) Titles

c)  Editing data in a chart

i)   Changing a value ii) Adding a value iii) Adding error bars

d)    Printing

e)    Copying and pasting


Introduction

Entering and Editing Data

In order to get started, Microsoft Excel needs to be opened.  

1.  In the lower left corner of the desktop click on the Start button.  

2.  Then point to Programs or All Programs to display the programs menu.

3.  Click on Microsoft Excel.

When Excel is open, observe the components available in the main window.  If the window does not fill the screen, select the maximize button in the top right corner.  Study the picture below of a typical Excel Window.

 

When Excel starts, it creates a new blank workbook.  Inside the workbook are worksheets. Each sheet name is displayed on a sheet tab at the bottom of the workbook.  A new workbook opens with three worksheets.  If necessary, you can add additional worksheets to a maximum of 255.  The worksheet is organized into a rectangular grid containing columns (vertical) and rows (horizontal).  A column letter above the grid, called the column heading, identifies each column.  A row number on the left side of the grid, called the row heading, identifies each row.  The intersection of each column and row is a cell.  A cell is the basic until of a worksheet into which you enter data.  A cell is referred to by its unique address, or cell reference, which is the coordinates of the intersection of a column and a row.  To identify a cell, specify the column letter first, followed by the row number.  One cell on the worksheet, designated the active cell, is the one into which you can enter data.  The active cell is identified in three ways.  First, a heavy border surrounds the cell; second, the active cell reference displays immediately above column A in the Name Box; and third, the column heading and row heading are highlighted so it is easy to see which cell is active.  The horizontal and vertical lines on the worksheet itself are called gridlines. Gridlines make it easier to see and identify each cell in the worksheet.  If desired, you can turn the gridlines off so they do not display on the worksheet, but it is recommended that you leave them on.  Often the mouse pointer displays as a block plus sign whenever it is located in

Introduction

a cell on the worksheet.  Another common shape of the mouse pointer is the block arrow.  The mouse pointer turns into the block arrow whenever you move it outside the worksheet or when you drag cell contents between rows or columns.  Study the chart below to learn more about the functions of elements in the Excel window.

Feature

Description

Active cell

The active cell is the cell in which you are currently working.  A dark border outlining the cell identifies the active cell.

Column headings

Column headings list the columns in the worksheet.  Columns are listed alphabetically from A to IV (a total of 256 possible columns.)

Formula bar

The Formula bar displays the contents of the active cell.  As you type or edit data, the changes appear in the Formula bar.

Name box

The Name box displays the location of the currently active cell in the workbook window.

Row headings

Row headings list the rows in the worksheet.  Rows are numbered consecutively from 1 to 65,536.

Sheet tabs

Each worksheet in the workbook has a sheet tab that identifies the sheet’s name.  To move between worksheets, click the appropriate sheet tab.

Tab scrolling buttons

The tab scrolling buttons are used to move between worksheets in the workbook.

 Look at the chart below to learn how to navigate within an Excel spreadsheet.

Keystroke

Action

Arrows

Moves the active cell up, down, left, or right one cell

Enter

Moves the active cell down one cell

Tab

Moves the active cell to the right one cell

Page Up

Moves the active cell up one full screen

Page Down

Moves the active cell down one full screen

Home

Moves the active cell to column A of the current row

Ctrl + Home

Moves the active cell to cell A1

F5 (function key)

Opens the Go To dialog box in which you can enter the cell address of the cell that you want to make active

As in other Microsoft Office Programs, you can find out the function of each toolbar button by letting your mouse rest over a toolbar button. 

Basic Entry

 

1a

Basic Entry

Text

In Excel, any set of characters containing a letter, symbol, number, hyphen (as in a telephone number), or a space is considered text.  Text is used to place titles on the worksheet, such as worksheet titles, column titles, and row titles.  To enter text in a worksheet, you click the cell in which you want the text place and then type the text you want entered.  Excel automatically aligns text with the left edge of the cell, you will find out later how to change the cell alignment.

Dates

Dates are treated as separate from text in Excel because Excel includes several special functions and commands to work with dates.  To enter a date, separate the parts of the date with a slash or hyphen.  For example, the date April 1, 2003 can be entered as either “4/1/2003” or “1-Apr-2003”.  You can also enter the date as “April 1, 2003”, in which case Excel might automatically convert the text to “1-Apr-2003”.  You can change the format used by Excel to display dates by changing the cell’s format.  You will learn later about changing the date format.

Values

Values are numbers that represent a quantity of some type: the number of students in a class, exam scores, financial information, etc.  Values can be numbers such as 378 and 24.462, or negative numbers such as -324.54.  Values can also be expressed as currency ($24.25) or percent (7.5%).  As you type information into a cell, Excel determines whether the information you have entered can be treated as a value.  If so, Excel will automatically recognize the value type and right-align the value within the cell.  Not all numbers are treated as values. For example, Excel treats a telephone number or a Social Security number as a text entry (keep in mind that any entry with a hyphen will be treated as a text entry).

 

    1b                    Selecting Cells

There may be times when you will need to move the content of a group cells in order to make room for other information.  To do this, you will need to select a group of cells, which is called a cell range, or range.  Ranges can be either adjacent or nonadjacent.  An adjacent range is a single rectangular block, while a nonadjacent range is comprised of two or more separate adjacent ranges. 

 

It is important to know how to select ranges of cells so that you can move and copy the data anywhere in the worksheet or workbook.  Learn the following steps to selecting ranges of cells.

Selecting Cells

To select an adjacent range of cells

1.  Click a cell in the corner of the rectangle that comprises the adjacent range.

2.  Press and hold down the left mouse button and drag the pointer through the cells you want selected.

3.  Release the mouse button.

To select a nonadjacent range of cells:

1.  Select an adjacent range of cells.

2.  Press and hold down the Ctrl key, and then select another adjacent cell range.

3.  With the Ctrl key still pressed, continue to select other cell ranges until all of the ranges are selected.

4.  Release the mouse button and the Ctrl key.

If you need to select a large range of data, using the above methods can be frustrating and timeconsuming. For that reason, the following methods are useful when you need to select a large number of cells.

To select…

Action

A large range of cells

Click the first cell in the range, press and hold down the Shift key, and then click the last cell in the range.  All of the cells between the first and last cell are selected.

All cells on the worksheet

Click the Select All button, which is the gray rectangle in the upper-left corner of the worksheet where the row and column headings meet. 

All cells in an entire row or column

Click the row or column heading.

A range of cells containing data

Click the first cell in the range, press and hold down the Shift key, and then double-click the side of the active cell in which you want to extend the selection. Excel extends the selection up to the first empty cell.

 

    1c                    Editing Data

When you work in Excel, you might make mistakes that you want to correct or undo.  You can do this by either deleting the information in the cell and retyping the correct information, or you can edit the cell.  You can edit the cell in two different ways:  

1.  Select the cell and then click in the Formula bar to make the changes, or 

2.  Double-click the cell to open the cell in edit mode.  

Edit mode simply activates the selected cell and allows you to type directly into the cell.  Whether working in the Formula bar or in the edit mode, once you are finished editing the information simply press enter to save the corrections, or if you are working in the Formula bar, you can press the Enter button on the formula bar.  

If you make a mistake as you retype the information, you can press the Esc key or click the Cancel button on the Formula bar to cancel all changes you made while in edit mode.

Moving Data

 

1d

Moving Data

Now that you know a variety of ways to select a range of cells, you can move any range of data to another location in the worksheet or to a new worksheet.  There are several ways to do this.  The two most popular methods are the Click and Drag method and the Copy and Paste method.  With either method you must first select the range of cells you wish to move or copy.

Click and Drag

Once you have selected the range of cells you wish to move, then position the pointer over the selection border (the outer border of the cell range; always a darker, thicker line with a small box at the lower right corner) and drag the selection to a new location.  You can also use this method to copy a range of cells.  Copying a range of cells with this method is similar to moving it.  The only difference is that you must press the Ctrl key while you drag the selection to its new location.  A copy of the original data appears at the location of the pointer when you release the mouse button.  Look at the picture below of a range of cells to identify several elements needed when moving data. 

 

Copy and Paste

Copy and paste allows you to replicate the selected information and paste it wherever you wish, including other programs.  Again, you must first select a range of cells.  Next you need to select Copy from the Edit menu on the menu bar.  You can also use the Copy icon on the Standard toolbar to copy the selected information.  Then, you need to click in the cell where you wish the upper-left cell of the copy text to appear.  Finally, select the Paste command on the Edit menu on the menu bar or the Paste icon on the Standard toolbar.  Both of these commands can be accessed by right clicking and choosing them from this menu also.

In the same way you copied and pasted the selected range of cells, you can also Cut and Paste these same cells.  The only difference in the process of cutting and pasting in contrast to copying and pasting is that you will choose the Cut command instead of the Copy command.  All of the other steps are the same.


1e                    Formatting Cells 

There are many different kinds of formatting you can apply to cells in your document, and there are several ways to apply these kinds of formatting.  In this tutorial, you will learn the general ways to apply basic formatting.  There are many more options and methods of formatting available, including AutoFormat which works in much the same way as AutoFormat in Microsoft Word, so feel free to experiment on your own if you so wish.

Font

As you have most likely already learned, a font is the design applied to characters, letters, and punctuation marks.  Each font is identified by a font name.  Each fount can be displayed using one of the following styles:  regular, italic, bold, or bold italic.  Fonts can also be displayed with special effects, such as strikeout, underline, and color.  Fonts can also be rendered in different sizes.  Sizes are measured using “points.”  To change the font used in a selected cell, you can either click the appropriate buttons on the Formatting toolbar or select options in the Format Cells dialog box.

The first way you can format the font in any cells you have selected is to use the Formatting toolbar buttons.  Keep in mind that any formatting you apply will only be applied to the cell or cells

 

you have selected. Below the buttons on the formatting toolbar are identified.

The second way you can apply font formatting is by using the Format Cells dialog box.  You can access the Format Cells dialog cells two ways.  The first is by right-clicking in the selected cells and then choosing Format Cells.  The second is by selecting Format on the menu bar and then choosing cells.  With either method, it is necessary to have selected the range of cells you wish to format before going into the Format Cells dialog box.  

By looking at the picture of the Format Cells dialog box on the following page, you can see that there are multiple tabs available for a variety of different functions.  When adjusting the font formatting, you will need to use the Font tab.  This menu is very similar to the font menu in Microsoft Word and you can use it the same way, so for that reason we will not go through it any further at this time.  

However, take note that both the Formatting toolbar and Format Cells dialog box also allow you to change the font color.  

Text Alignment

You can also adjust the cell alignment using either the toolbar or the Format Cells dialog box. The toolbar buttons that adjust the cell alignment are the same ones used by every Microsoft program, including Word.  

The Format Cells dialog box, pictured below, gives you more alignment options then the formatting toolbar because it lets you set the horizontal and vertical alignment as well as orientation and text control.  Study the image below to learn the options available on the


Alignment tab.

The options available for horizontal and vertical alignment are self-

explanatory.  The options

with (Indent) at the end of

them provide an opportunity to specify an indention

amount for those specific cells.   

You can specify the indention amount in the box directly to the right of the

alignment drop down boxes. 

This command wraps text

into multiple lines in the cell.  The number of wrapped lines depends on the width of the

column and the length of the cell contents.

Merge Cell combines two or more selected cells into a single cell.

Orientation is the amount of text rotation in the selected cell.  You either type the

amount you wish to rotate

the text into the Degrees box or you can simply click

anywhere in the orientation picture (where the arrow is

pointing) to set the orientation

Click in this box to set the orientation for the cell at vertical.

Use a positive number in the

Degree box to rotate the selected text from lower left

to upper right in the cell.  Use negative degrees to rotate

text from upper left to lower right in the selected cell.  

Shrink to fit reduces the size of the font for all the

data in the selected cells so that it fits within the column. 

The size is adjusted automatically if you change the column width.  


Look at the image below for examples of the options available in the Alignment tab in the Format Cells dialog box.

Number/Text Format

The Number tab on the Format Cells dialog box allows you to format the appearance of text and values within selected cells.  There are several categories you can choose that allow you apply formatting specific for those functions.  Study the chart below to become familiar with the different categories and the specific formatting they apply to the selected text.

Category

Description

General

Default format; numbers are displayed without dollar signs, commas, or trailing decimal places

Number

Used for general display of numbers

Currency

Used for displaying monetary values; You are able to modify the symbol, decimal places, and negative number format

Accounting

Used to align currency symbols and decimal points within a column

Date, Time

Used for displaying date and time values; You are able to choose which specific date and time format you wish to use

Percentage

Used for displaying decimal values as percentages

Fraction, Scientific

Used for displaying values as fractions or in scientific notation

Text

Treats cells as text even when a number is entered in the cell; the number is displayed exactly as entered

Special

Used for displaying zip codes, phone numbers, and social security numbers

 

Borders and Patterns

Two of the remaining tabs in the Format Cells dialog box include Border and Patterns. The Borders tab allows you to add borders to selected cells in much the same way as you can add borders to tables in Microsoft Word.   You can also add borders using the Borders button on the Formatting toolbar.  Refer to the picture to the right.  You can also draw cell borders using the Draw Borders option available in the drop-down menu of the Borders button.  Choosing this option will allow you “draw” cell borders using a variation of the Tables and Borders toolbar available in Word.

The Patterns tab allows you apply backgrounds to the selected cells.  You can select a solid shade or patterns with two tones.

 

1f                        Inserting and Deleting Rows and Columns

Inserting Cells, Rows, or Columns

If you wished to insert blank cells, rows, or columns, you could always select and drag the existing portion to the new location.  However, it maybe a lot easier to insert a blank cell, row, or column instead of moving all the selected information.  

To insert a new row, you select a cell in the row or column where you want the new row or column to be placed.  In essence, select the row you want to be moved down and select the column you want moved to the right.  Another way to remember this is to consider the reference location.  Select the row number or column letter in which you want a new row to be inserted at.  It is also only necessary to choose one cell in the row or column where you want the row inserted.  

Borders button

Once selecting at least one cell in the row or column you want moved, click Insert on the menu bar, and then click Columns or Rows.  You are also able to right-click the selected cell and click Insert instead of clicking Insert on the menu bar.  If you do choose to use the right-click method, you would then select Entire column or Entire row to insert an entire column or row.

You can also insert individual cells within a row or column (rather than an entire row or column). To do this, select the range where you want the new cells place, and click Cells on the Insert menu.  You can also access this same menu by right-clicking the selected cells and choosing Insert on the shortcut menuExcel provides four options when inserting new cells:

? Shift cells right

Inserts new blank cells into the selected region, and moves the selected cells to the right.  The new cells will have the same number of rows and columns as the selected cells.

? Shift cells down

Inserts new blank cells into the selected region, and moves the selected cells down. The new cells will have the same number of rows and columns as the selected cells.

? Entire row

Inserts an entire blank row.

? Entire column

Inserts an entire blank column.

Deleting Cells, Rows, or Columns

Excel provides two ways to remove data.  One way, called clearing, simply deletes the contents of the cells.  To clear the contents of a cell, you use either the Delete key or the Clear command on the Edit menu.  Clearing the contents of a cell does not change the structure of the workbook; that is, the row is not removed from the worksheet.  Do not press the spacebar to enter a blank


Inserting and Deleting Rows and Columns

character in an attempt to clear a cell’s content.  Excel treats a blank character as text, so even though the cell appears to be empty, it is not.

The second way to remove data is to delete the cell, row, or column.  Excel provides similar options for deleting rows, columns, and cells as it does for inserting them. To delete a row, column, or cell form the worksheet, you first select the cell or range and then click Delete on the Edit menu (you can also right-click the selected rang and choose Delete on the shortcut menu). Excel provides you with the following delete options:

?Shift cells left     Deletes the selected cells and shirts cells from the right into the selected region.

?Shift cells up      Deletes the selected cells and shifts cells from the bottom up into the selected region

?Entire row           Deletes the entire row

?Entire column     Deletes the entire column

 

1g

Changing Row or Column Size

Excel provides several methods for changing the width of a column or the height of a row.  First, you can click the dividing line of the column to make the column as wide as the longest entry.  You won’t have to adjust the row height this way because Excel automatically adjusts the row height to fit your text’s font height or all the text in the cell if you have text wrapping activated.   

Secondly, you can adjust the row or column size by dragging the dividing line to whatever measurement you wish.  

Finally, you can adjust the row or column size by clicking the row or column heading or headings which you would like to adjust.  Next, click Format on the menu bar, point to either Row or Column as applicable, and then click either Height or Width.  In the Column Width or Row Height dialog box, simply enter the new column width, and the click the OK button.  You may also choose AutoFit in either the Row or Column submenu to make the column or row as wide as the longest entry of the cells in that column or row).

 

1f

Transposing Rows and Columns

Transposing rows and columns allows you to take data from a column and have it fill a row.  Look at the example below for a further description.  The process to do this is fairly simple, however it is important to understand that if you are transposing from a row to a column, data from the top row of the copy area will appear in the left column of the past area, and if you are transposing from a column to a row, the data from the left column will appear in the top row.  First you need to select the range you wish to transpose.  Next, click copy, either on the toolbar or in the Edit menu and then select the upper-left cell of the past area.  The past area must be outside the copy area.  Finally, click the arrow to the right of the Paste button on the toolbar and then click Transpose. It is also possible to access the Transpose option by selecting Paste Special from the Edit menu and then choosing the Transpose check box in the Paste Special option box.

 

This is an example of transposed data.  Column A has been transposed to Cells 1D through1I.  Also,



Column B has been transposed from Cells 2D through 2I.

Sorting Data

 

1i

Sorting Data

To rearrange entries in a list, you can sort the entries based on the data in one or more the columns. It is possible to sort by rows; however it is more common to sort by columns. What you use to order your data are called sort fields.  Another term to become familiar with is primary sort field.  The primary sort field is the first criteria that your information is sorted by.  The second or third criteria that Excel sorts your information by are called secondary fields. It is not necessary to have any secondary fields if you do not wish to have it sorted by more than one criterion.  Before you complete a sort, you need to decide whether you want to put the list in ascending or descending order.  Ascending order arranges labels alphabetically from A to Z and numbers from smallest to largest.  Descending order arranges labels in reverse alphabetical order from Z to A and numbers from largest to smallest.  In both ascending and descending order, any blank fields are placed at the bottom of the list.

Sort

Ascending

Sort

Descending

To sort data, you can use either the Sort Ascending and Sort Descending buttons on the Standard toolbar (pictured to the right), or you can use the Sort command on the Data menu on the Menu Bar.  The easiest way to sort data when there is only one sort field is to use the Sort Ascending or Sort Descending button on the Standard toolbar.  If you are sorting using more than one sort field, you should use the Sort command on the Data menu to specify the columns on which you want to sort.  You can also use this option if you are sorting just one field.  Furthermore, when sorting records using either method, you should not select the entire sort field column.  Instead, just click any cell in the column you want to use as the sort field.  If you do select the entire column, Excel will only sort the values in the selected column rather than sort the entire list.

If you use the toolbar buttons to sort one field, simply click one cell in the sort field column or row, and then click the appropriate button on the toolbar.  It’s that easy!

However, there are times when sorting by one sort field results in a tie.  A tie occurs when more than one record has the same value for a field.  This situation is where you might want to sort by more than one field and use primary and secondary fields.  As stated above, when sorting by more than one field, you will need to use the Sort menu available in the Data menu.  To use this command, first click any cell in your list, and then click Data on the menu bar.  Lastly, click Sort.  This will open the window pictured below.  Look at the image below to learn how to operate this menu.

the first row or column you want Excel to sort your list by.

Importing Data

 

1j                         Importing Data

Importing a data file

There may be times when you will need to import data from a text file into an Excel worksheet.  This process can become involved and confusing, but this tutorial will guide you through the basic text importation.  Follow the steps below:

1.  Click the cell where you want to put the data from the text file.  You can also insert the imported text into a new Excel worksheet, and if this is your desire, the location of the selected cell is irrelevant.  If you are going to insert the text into the current worksheet be sure that the worksheet has no data below or to the right of the cell you click.

2.  On the Data menu, point to Import External Data, and then click Import Data.  

3.  In the Files of type box, click Text Files.  

4.  In the Look in list, locate and double-click the text file you want to import.

5.  To specify how you want to divide the text into columns, follow the instructions in the Text Import Wizard, and then click Finish.  The Text Import Wizard is a very confusing menu.  For the most part, it is best to simply allow the wizard to choose the settings.  Always at the bottom of each step is a Data Preview of what the imported information will look like.  There is one thing you should be aware of through the three steps in this wizard.  Refer to the picture below. 

 

6.  In the Import Data dialog box, you can choose if you want the imported data inserted into the active worksheet or if you want it inserted into a new worksheet.  You are also able to adjust the properties of this data range; however, many of these options are in depth and will not be covered in this tutorial.  Refer to the picture below of how to choose the location of where to insert the imported information

 

Importing Data

Text to Columns

At times you may receive or find data that is not in any specific structure – no charts, tables, etc.  This information could be from an email, the internet, or any other possibility.  Even though it may be difficult to see how this data can be entered into an Excel sheet without cutting and pasting each specific number, there is a way to copy the entire section into one cell and then add columns. This function is called text to columns.  Follow these steps:

1.  Cut or copy the data from wherever you have found it and then paste it into Excel. You do this by placing the cursor in the specific cell and then selecting paste.

2.  One you paste the data, Excel automatically creates rows for the different lines in the original data but Excel is unable to make columns from this form.

3.  Next, select all the data you wish to divide into rows.

4.  Select Text to Columns from the Data menu on the menu bar.

5.  You will now see a window very similar to the Import Text window.  You have two choices for how to divide the text:  Delimited or Fixed width. Delimited means that some character or space divides the text where you want to separate it into columns.  Fixed width means that you will divide the text by a specific width, which is adjustable.

6.  Select Next.

7.  Depending on which option you chose, you will now one of two windows.  Both of these windows are relatively easy to understand.  Carefully read the directions and play around with a few settings to understand them better.

8.  Select Next to move to the next screen.

9.  Refer to the picture for a description on how to use this last section of the wizard.

 

Just as a general note, the fixed width option allows you to add division lines wherever you wish so if you are uncertain of which to use and do not have extensive text, the fixed width option might be the better choice.

Saving

 

1k

Saving

As with any thing you type or create on a computer, you will want to save what you are working on often.  In Excel, saving your workbook is quite simple.  First, click on File on the menu bar and then Save As.  This will open up the Save As dialog box.  This box is identical to the ones used in all Microsoft Office Programs.  

By default, Excel saves your workbook in Microsoft Excel Workbook format.  If you are creating a report that will be read by applications other than Excel (or earlier versions of Excel), you can select a different type from the Save as type list box in the Save As dialog box.  There is a variety of formats available and the chart below identifies them.  The chart below identifies only a few of the more common formats that you may use.

Format

Saves the active worksheet…

CSV (Comma delimited)

As a text file with columns separated by commas.

Formatted Text (Space delimited)

As a text file with columns separated by spaces.

Text (Tab delimited)

As a text file with columns separated by tabs.

Microsoft Excel Workbook

As in a version of Excel. Unless a specific version is chosen, it will save in the version you are using.

 

1l                         Printing

Page Setup

You can use the Page Setup dialog box to control how a worksheet is placed on a page.  You can adjust the size of the margins (the spaces between the page content and the edges of the page), center the worksheet text between the top and bottom margins (horizontally) or between the right and left margins (vertically), change the page orientation (determines if the page is wider than it is tall or taller than it is wide), and enter a header or footer.  You can open the Page Setup dialog box by selecting File and then Page Setup.

By default, Excel places a 1-inch margin about and below the report and a ¾ inch to the left and right.  Excel also aligns column A in a worksheet at the left margin and row 1 at the top margin.  Depending on how many columns and row there are in the worksheet, you might want to increase or decrease the page margins or center the worksheet between the left and right margins or between the top and bottom margins.  If you are having trouble fitting all your columns on one sheet, adjusting the margins may help.  All of this is found under the Margins tab in the Page Setup window.  Another feature found under the Margins tab, includes being able to center your spreadsheet on the page.  You have the option of centering it horizontally, vertically, or both.  Simply select the checkbox for the option you want.

If you are struggling to fit all your columns onto one or two pages, you may also find it helpful to change ht orientation of the page from portrait to landscape.  Portrait orientation (which is the default) displays the page taller than it is wide.  Landscape orientation displays the page wider than it is tall. You can do this on the Page tab in the Page Setup window.  The Page tab also contains some other useful formatting features.  You can reduce or increase the size of the worksheet on the printed page.  The default size is 100%.  You can choose to reduce or increase that amount.  You can also have Excel automatically reduce the size of the report to fit within a specified number of pages by using the Fit to commands.  

Printing

A third tab available in the Page Setup dialog box is the Header/Footer tab.  A header is text printed in the top margin of every worksheet page.  A footer is text printed at the bottom of every page.  Headers and footers can add important information to your printouts.  For example, you can create a header that displays your name and the date the report was created.  If the report covers multiple pages, you can use a footer to display the page number and the total number of pages.  Excel does try to anticipate headers and footers that you might want to include in your worksheet.  Clicking the Header or Footer list arrow displays a list of possible headers or footers (the list is the same for both).  

If you want to use a header or footer not available in the lists, you should click the Custom Header or Custom Footer button and create your own header or footer.  The Header dialog box and the Footer dialog box are similar.  Each dialog box is divided into three sections – left, center, and right.  If you want to enter information such as the filename or the day’s date into the header or footer, you can either type the text or click one of the format buttons located above the three section boxes.  It is important to know that each of the buttons in the Custom Header or Custom Footer dialog box will insert format codes so do no be concerned if the actual text you wished to appear in the header does not appear.  Select the appropriate buttons, designing your header or footer, and then select OK and preview your header or footer in the Header/Footer tab of the Page setup dialog box.  Refer to the pictures below for descriptions of the available buttons in the Header and Footer dialog box.

 

Printing

Print Area

The default action for printing in Excel is to print all parts of the active worksheet that contain text, formulas, or values, and this will not always be what you want.  If you want to print only a part of the worksheet, you can define a print area that contains the content you want to print.  To define a print area, you must first select the cells you want to print, and then select the Print Area option on the File menu.  A print area can include an adjacent range or nonadjacent ranges.  You can also hide rows or columns in the worksheet in order to print nonadjacent ranges.

You can also repeat information, such as the first row or column, by specifying which cells in the print are should be repeated on each page.  This is particularly useful in long tables which extend over many pages.  In such cases, you can have the column titles repeated for each page in the printout.  This action can be done in the Page Setup dialog box.  Study the picture below to learn how to use the Sheet tab in this dialog box.

 

Excel Tutorial                                                                                                                              Entering and Editing Data

Page 16 of 16                                                                                                                                          Case Perspective

 

Ethan Williams works at Summerfield Counseling and has been assigned the responsibility of compiling its annual spending so that it can be understood easily.  He has chosen to use Microsoft Excel to set-up a spreadsheet for this information. However, he has never used Excel before and is asking for your assistance in setting up this spreadsheet.

In this Case Perspective, you will be responsible for entering all of the data for this spreadsheet into Excel.  You will also need to format, sort, and duplicate parts of the data.  In subsequent case

perspectives, you will continue to use this worksheet by applying the concepts learned in later tutorials, so it is important to complete this perspective accurately and save your work.

It’s your turn to practice!!  Read the Case Perspective to the left.  Then recreate the documents pictured on the following sheet using the guidelines presented on this page.

•   Enter the following information into the corresponding cell.

•   A1 – Summerfield Counseling's Annual Spending

•   A3-A14 – Months of the year; one in each cell

•   B2 – Office Supplies   • B13 - 95.61

•   C2 – PR         • B14 - 66.85

•   D2 – Total      • C3 - 1002.36

•   A15 – Total    • C4 - 2315.23

•   B3 – 68.23     • C5 - 984.25

•   B4 – 85.14     • C6 - 753.98

•   B5 - 29.99      • C7 - 869.25

•   B6 - 100.14    • C8 - 1012.25

•   B7 - 78.65      • C9 - 1500.36

•   B8 - 81.64      • C10 - 980.15

•   B9 - 72.18      • C11 - 1125.65

•   B10 - 49.2      • C12 - 2003.14

•   B11- 53.9       • C13 - 762.87

•   B12 - 17.23    • C14 - 929.36

•   Format cells B3-B14 and C3-C14 to a Currency formatting with two decimal points represented (found under the number tab of the Format Cells dialog box).

•   Format A1 to have Bold text and a font size of 12 pts.

•   Format cells B2, C3, and A3-A14 to have Bold formatting.

•   Format cells A15 and D2 to have Bold and Italic formatting.

•   Set the width of column A to 11.

•   Set the widths of the columns B, C, and D to 14.

•   Copy cell A1 and paste in cell B17.

•   Copy A2:D5 and past by transposing into cell A18.

•   Copy D2 and paste into F18.

•   In cell A17 delete the word “Annual” and replace with “1st Quarter”.

•   Wrap the text in cell A19.

•   To the height of rows 18, 20, 21 to 25.50.

•   Set the width of column F to 14.

•   Set the alignment of cells B2, C2, D2, B18, C18, D18, E18, F18 to right.

•   Copy A1:D15 and paste into cell A25.

•   Sort this copied section ascending.  

•   Select nonadjacent sections.  These sections need to be comprised of cells A1:D15 and A17:F21 and A25:D39.  Set the print area to these sections.  If you look at the Print Preview you will see that this creates three separate sheets.

•   Draw borders between rows 2 & 3, 18 & 19, 20 & 21, 26 &27, and 37 & 38.

•   Delete row 32 (information about January).

•   Add a header with the text being “Summerfield Spending.”

•   Add a footer that is listed in the drop-down menu called “Page 1 of ?”.

Be sure to email a copy of your completed project to your instructor.

It must have YOUR NAME as the subject line.


These three pages are copies of what your spreadsheet should    look like in Print Preview.

 

Creating and Correcting Formulas

Before you can begin to use formulas it is necessary to understand what they are.  A formula is an expression that is used to calculate a value.  You can enter a formula by typing the expression into the active cell, or in special cases Excel will automatically insert the formula for you.  Excel formulas always begin with an equal sign (=) followed by an expression that calculates a value.  If you do not start with an equal sign, Excel will treat the expression you enter as ext.  The expression can contain one or more arithmetic operators, such as +, -, *, /, or ^, that are applied to either values or cells in the workbook.  The table below shows some common arithmetic operators and examples of Excel formulas.

Arithmetic Operation

Arithmetic Operator

Example

Description

Addition

+

=10+A5

=B1+B2+B3

Adds 10 to the value in cell A5

Adds the values of cells B1, B2, and B3

Subtraction

-

=C9-B2

=1-D2

Subtracts the value in B2 from the value in cell C9 Subtracts the value in cell D2 from 1

Multiplication

*

=C9*B9

=E5*0.06

Multiplies the value in cell C9 by the value in cell B9 Multiplies the value in cell E5 by 0.06

Division

/

=C9/B9

=D15/12

Divides the value in cell C9 by the value in cell B9 Divides the value in cell D15 by 12

Exponentiation

^

=B5^3

=3^B5

Raises the value in cell B5 to the third power Raises 3 to the power specified in cell B5

 

2a                      Using Functions

You’ve learned how to calculate values using formulas.  However, if you have a large set of values that you wish to calculate, the formula would be so large that it would be unmanageable.  In Excel you can easily calculate the sum of a large number of cells be using a function.  A function is a pre-defined, or built-in, formula for a commonly used calculation.  When entering a function there are several terms that you might want to know.  The syntax specifies the order in which you must enter the different parts of the function and the location in which you must insert commas, parentheses, and other punctuation.  The argument is the numbers, text, or cell references used by the function to calculate a value.  Some arguments are optional arguments because they are not necessary for the function to return a value.  Although these terms are the basis of functions, in this tutorial they will not be used when explaining how to insert and operate functions.

You can learn about each function using Excel’s Help, which will be necessary. However, the following chart describes several functions that are important for your use in your course.

Function

Syntax

Description

Average

AVERAGE

(number1,number2, )

Returns the average (arithmetic mean) of the arguments.

When averaging cells, keep in mind the difference between empty cells and those containing the value zero. Empty cells are not counted, but zero values are.

Median

MEDIAN

(number1,number2, )

Returns the median of the given numbers. The median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less.

If a reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included. 

If there is an even number of numbers in the set, then MEDIAN calculates the average of the two

numbers in the middle.

Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).

Standard       STDEV (number1,number2, )     STDEV uses the following formula:  

Deviation              

Estimates variance based on a sample. 

VAR            VAR uses the following formula:  Variance (number1,number2, )          

Counts the number of cells that contain numbers and also numbers within the list of arguments.

Count            COUNT (value1,value2, )     Arguments that are numbers, dates, or text representations of numbers are counted; but error Use COUNT to get the number of entries in a number field that's in a range or array of numbers.

values or text that cannot be translated into numbers are ignored. Counts the number of cells within a range that meet the given criteria. 

Count if                                                                  COUNTIF (range,criteria)      Range is the range of cells from which you want to count cells. Criteria is the criteria in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 32, ">32", "apples".

Sum of    SUMSQ (number1,number2, )         Returns the sum of the squares of the arguments. square root              

Returns the confidence interval for a population mean. The confidence interval is a range on either side of a sample mean. 

Confidence             CONFIDENCE (alpha,standard_dev,size)         Alpha is the significance level used to compute the confidence level. The confidence level equals intervals                         100*(1 - alpha) %, or in other words, an alpha of 0.05 indicates a 95 percent confidence level.

Standard_dev is the population standard deviation for the data range and is assumed to be known. Size is the sample size.

To use these functions, you will need to add them to your document.  You could simply type the function and its arguments into the cell, but you will often find that you have forgotten which arguments are required by the function and the correct order in which the arguments need to be entered.  To assist you, Excel provides the Insert Function button on the Formula bar.  Clicking this button displays a dialog box from which you can choose the function you want to enter.  Once you choose a function, another dialog box opens in which you specify values for all the function’s arguments.  The steps below walk you through how to insert a function into your worksheet.

1. Click the cell in which you will insert the function.

   

You can enter this function into more cells by using AutoFill.  The fill handle is a small black square located in the lower-right corner of a selected cell or range.  When you drag the fill handle, Excel automatically fills in the formulas used in the selected cells.  This feature is extremely useful to if you have a column of totals or related functions. 

2b

Nesting Functions

In come cases, you may need to use a function as one of the arguments (one of the numbers, text, or cell references used by the function to calculate a value) of another function.  You would use a Nested Function in this situation. Nested functions use another function as one of the arguments of another function.  The following formula sums a set of numbers (G2:G5) only if the average of another set of numbers (F2:F5) is greater than 50.  Otherwise it returns 0.  

 

In order to use a nested function, you must:

1.  Click the cell in which you want to enter the formula.

2.  Click the Insert Function button on the Formula Bar.

3.  Select the function you want to use.  You can use either method mentioned above.

4.  Enter the arguments

•    Enter cell arguments in the ways discussed above.

•    Enter another function as an argument by entering it in the argument box you want. For example, you can add SUM(A2:A5) into one of the argument boxes of the AVERAGE function.

A tip for you is that you can enter the same formula into a range of cells by selecting the range first, typing the formula, and then pressing CTRL+ENTER.


Nesting Functions

 

2c                      Writing Functions

Follow these steps when entering a formula into Microsoft Excel. 

1.  Click the cell where you want the formula value to appear.

2.  Type = and then type the expression that calculates the value you want.

3.  For formulas that include cell references, such as B2 or D78, you can type the cell reference or you can use the mouse or arrow keys to select each cell.

4.  When the formula is complete, press the Enter key.

There is one concept you must remember when entering formulas.  Excel performs the calculations in the order of precedence.  The order of precedence is a set of predefined rules that Excel follows to unambiguously calculate a formula by determining which operator is applied first, which an operator is applied second, and so forth.  First, Excel performs exponentiation (^).  Second, Excel performs multiplication (*) or division (/).  Third, Excel performs addition (+) or subtraction (-).  If the expression contains two or more operators with the same level of precedence, Excel applies them going from left to right in the expression.  You can also add parentheses to a formula to make it easier to interpret or to change the order of operations.  Excel will calculate any expression contained within the parentheses before any other part of the formula.  Perhaps you recognize this order of precedence.  It is the same one used in arithmetic equations.



 

2d                      Formula Errors

Finding and Correcting Formula Errors

Formula errors can result in error values as well as cause unintended results.  For these reasons it is a very good idea to check for formula errors in your worksheet.  There are two main ways to do this and both are very similar to the way Word checks for grammar errors in your Word document.  Excel uses certain rules to check for problems in formulas.  These rules do not guarantee that your spreadsheet is problem-free, but they do go a long way to finding common mistakes.  You can also turn these rules on or off individually.  This is done by selecting Tools on the menu bar.  Next click on Options and then click on the Error Checking tab.  Select or clear the check box or boxes next to the rules you wish Excel to use to check your worksheet.

A very important thing to remember before using either of these methods is that if the worksheet has already been check for problems and specific problems were ignored, the problems will not appear until the ignored problems have been reset.  This can be done by selecting Tools on the menu bar and then Options. Next, click the Error Checking tab and click on the Reset Ignored Errors button.  Finally select OK.

The first method you can use to check your worksheet for errors is very similar to using a spell checker.  While in the worksheet you wish to check for errors, select Tools on the menu bar.  Then click Error Checking.  A good tip to remember is to position the Error Checking dialog just below the formula bar because the the formula bar is where it is most convenient to make changes to a formula while using the Error Checker.  Next click a button on the right of the dialog.  The options are different for each type of problem.  If you click Ignore Error, the problem is marked to be ignored for each consecutive check.  Then click Next.  Continue until the error check is complete.

The second method you can use to check your worksheet for errors is to mark common formula problems within the worksheet and then correct them there.  This is similar to the way Word underlines misspelled or grammatical errors in a Word document.  However, instead of having an underlined cell, Excel places a triangle in the top-left corner of any cells that contain a formula that breaks one of the rules.  The picture to the right shows what a typical

Creating and Correcting Formulas

Formula Errors

Problem Button

 

cell looks like with one of these “error triangles.”  To enable this method of error checking, click Tools on the menu bar, followed by Options, and then click the Error Checking tab.  Select the Enable background error checking check box.  To change the color of the “error triangle,” in the Error Indicator Color box, select a new color.  Select a cell with a triangle in the top-left corner of a cell.  Then click the button that appears (pictured to the left), and then click the option you want.  The options are different for each type of problem, and the first entry describes the problem. Again, if you click Ignore Error, the problem is marked to be ignored for each consecutive check.  Repeat the process of using the Problem Button for each cell that has an error.

Common Errors

The following are several of the common errors that appear in Excel.  When attempting to fix the formulas that cause these errors, go through the previous process of finding and correcting errors.  The following are simply descriptions of the errors.

#####

Occurs when a column is not wide enough to display all the cell contents, or a negative date or time is used.

#Value

Occurs when the wrong type of argument or operand (items on either side of an operator in a formula) is used

#DIV/0

Occurs when a number is divided by zero (0).

#NAME?

Occurs when Excel doesn’t recognize text in a formula.

#N/A

Occurs when a value is not available to a function or formula.

#REF!

Occurs when a cell reference is not valid.

#NUM!

Occurs with invalid numeric values in a formula or function.

#NULL!

Occurs when you specify an intersection of two areas that do not intersect.  The intersection operator is a space between references.

Creating and Correcting Formulas Page 6 of 7                 Case Perspective

 

This Case Perspective continues with the same situation presented in Case Perspective 1 which was found at the end of the Entering and Editing Data tutorial.  You will begin by using the finished product of Case Perspective 1.  If you have not already completed that Case Perspective, you will need to complete it in order to begin this one.

In this Case Perspective, you will be responsible for adding formulas and functions into the worksheet in order to organize all the numbers included.

It’s your turn to practice!!  Read the Case Perspective to the left.  Then recreate the documents pictured on the following sheet using the guidelines presented on this page.

•   Change Print Area to cells A1:F39.

•   Change cell A39 to Average.

•   Insert the Average function into B39 so that it averages the values from B27 through B38.

•   Add the total function into D3 using the values from B3 and C3.

•   Use the AutoFill method to add the Total function into cells D4-14 for the respective rows.

•   Add the Total function into cells B15, C15 and D15 for their respective columns.

•   Change F18 to Median.

•   Add the Median function to F19 for cells B19-E19.

•   Use the AutoFill method to add this function to F20.

•   Add borders between cells D14-15 & between F20-21.

•   Change A21 to read Percentage of Total.

•   Add text wrapping to A21.

•   Into cell B21 add a formula that includes the sum of cells B19 &B20 divided by the total yearly cost displayed in cell D15.

•   AutoFill this formula into the remaining cells in the row.  You will have errors.

•   To correct these errors, click the error button and use the Edit in formula bar option and correct the formula by changing the last cell reference so that it always refers to D15 (instead of E, F, or G15).

•   Change the format for cells B21-F21 to Percentage.

•   Change D26 to Total per month subtracted from monthly average

•   Text wrap D26.

•   In D27 enter a formula that begins with the sum of cells B27-C38 [SUM(B32:C43)], then have that divided by 12 [/12].  Next subtract the sum of B27-C27 [-SUM(B32:C32)]. Be sure to begin with an equal sign, use capital letters and parenthesis where needed.  Hint:  All the needed parenthesis are included in this question. Simply combine the shown parts.

•   AutoFill this formula the remaining cells in this column.

•   Add totals for the above columns to cells B39, C39, and D39.

Be sure to email a copy of your completed project to your instructor. It must have YOUR NAME as the subject line.


 

Charts

Microsoft Excel allows you to create a wide variety of charts using data that is present in an Excel worksheet.  As with any chart, information presented in a chart is often easier to comprehend and allows the viewer to gain a better understanding of the relationship between the information.

 

3a                    Creating a Chart

The Chart Wizard available in Microsoft Excel enables you to create charts quite easily.  When creating a chart in Excel, follow these steps.

1.  You must first open the workbook that contains the information you wish to present in chart form.  

2.  Then you need to select the data you wish to use.  When selecting the data, include any label columns or rows that may be present.  For example, if you have a worksheet that has monthly data entered and there is a column that includes the months, include the names of the months when selecting your data.  By selecting these “label” fields, Excel will most likely automatically label the axis of your chart.  

3.  Click the Chart Wizard button on the Standard toolbar.  Here is a picture of that button: 

4.  Next, you must select the chart type and sub-type.  Refer to the picture below. 

 

5.  In the second step of the Chart Wizard, you can make any modifications or additions to the chart’s data source.  The first tab, Data Range, allows you to select the data you wish to use if you have not already done so or it allows you to modify the selected data range.  You are also able to choose if the series you wish to use is in rows or columns in your worksheet.  Feel free to change these options and see how the preview image changes.  This is the best way to discover which format works best for your data.

The second tab in the second step of the Chart Wizard is titled Series.  This tab allows you to do several important things.  It allows you to choose which cells have the name and values you wish to use for one set of information.  It also allows you to choose which cells you want to use to label the x-axis.  Refer to the picture below. 

 

6.  In the third step, you can make modifications to the chart’s appearance.  There are several tabs available.  Refer to the list and picture below. 

 

7.  In the fourth and final step, specify where you want the chart to be placed.  It is HIGHLY recommended that you save the chart As a new sheet, especially for the class you taking.  You can then name the new sheet anything you like.  Refer to the picture below.

 

It is also good to note that all of these steps can be accessed even after the Finish button is selected.  While viewing the chart as a new sheet, simply click Chart on the Menu Bar.  Then each of the following dialog boxes are the same as the Wizard windows:  Chart Type, Source Data, Chart Options, and Location.  This allows you to change any of the choices you made while going through the wizard.

 

             3b               Editing the Chart Display

In Excel, it is possible to select and format individual chart elements, such as the chart title, legend and axes.  There are several ways to format an individual chart element.  You can click the element to select it and then format its appearance using the same tools on the Formatting toolbar you used to format worksheet cells.  You can also double-click the chart element to open a dialog box that contains formatting options, or, finally, you can right click on an element and choose Format … to open up the same menu that appears after double-clicking.  Using the Formatting toolbar is usually quicker, but opening a format dialog box will provide you with more options and more control over the element’s appearance.  The following subtopics address several of the main elements you can format within your chart.

Font

To format any of the text within your chart, open up the Format dialog box by any of the methods described above.  For example, double-clicking any of the text within the chart will open up the Formatting dialog box.  Within whichever specific Format box you are in, select the Font tab.  This will bring up a box very similar to the Font dialog box present in all Microsoft programs.

It is also possible to add text to a chart by simply typing the text into the Formula Bar and then pressing Enter.  The text is placed into a textbox, and you are then able to modify it as you would any text by using the Formatting toolbar. You can also resize the textbox by dragging the squares in the corners of the textbox.  Furthermore, you can move the text box the same way you would any object by dragging the border when a quadruple arrow is present. 

Refer to the picture on the following page for a review of the commands possible.


 

Color

It is possible to change almost every color present in a chart ranging from the text (already mentioned above) to the backgrounds to the bars, lines, etc.  All of the color changes can again be done in the Formatting dialog box.  As with the font, double-click (or use one of the other methods) any part of the chart you wish to modify the color.  Depending on which part of the chart you are modifying, the dialog box will have a different appearance, however there will always be a tab titled Patterns which will contain all the color information.  Refer to the picture below for an overview of how to use this dialog box.

 

Scale

The scale for an Excel graph is displayed with values placed alongside the axis.  Excel chooses a default scale for the y-axis, usually designed to make the chart easy to read and to cover a range of reasonable values.  However, it is possible to modify this scale.  The first thing you will need to do is double-click any of the values on the y-axis.  The Format Axis dialog box opens.  You can use this dialog box to format the scale’s appearance and to change the range and increments used in the scale.  While in the Format Axis dialog box, you will need to select the Scale tab.   There are four main values that comprise the scale on your graph.  The minimum and maximum values are the smallest and largest tick marks that appear on the axis, in other words, the smallest and largest values displayed on your chart.  Tick marks are the marks that measure the values of the data, similar to marks on a ruler. The other two values, major unit and minor unit, refer to the values displayed on the axis.  These values may or may not be displayed.  The difference between major and minor tick marks is that major tick marks are displayed alongside an axis value, whereas minor tick marks are not.

Refer to the picture on the following page for definitions of the four main vales that comprise the scale and how to use this box. 

 

Gridlines

Gridlines are the lines present in your graph that help the viewer to judge the value of the data.  For both the x-axis and the y-axis, major and minor gridlines are available.  These lines can be turned on and off by bring up the Chart Options dialog box. This box is brought up by rightclicking on the plot area and choosing Chart Options.  Once this box is brought up, choose the Gridlines tab available at the top of the box.  Once this tab is opened, you can clearly see where to place checkmarks in order for major and minor gridlines to appear. The picture on the right of the box provides a very helpful preview of your chart with the selected gridlines.  As you might be able to guess, major gridlines correspond with the major unit value discussed in the previous section and the minor gridlines correspond with the minor unit described previously.  It is also very important to understand that the scale values present within the Format Axis dialog box are the same values that can be modified within the Format Gridlines dialog box.  So basically, you have already learned how to modify the gridline values in the previous section.

Legend

There are several formatting changes you can make to the legend on your graph.  You can apply color, change the font, or reposition it.  As always, you need to bring up the Format Legend dialog box in order to change the color, font, position.  The position can also be changed by dragging the legend the same way you would any, by simply clicking and dragging object (the Format Legend box would not be open if you chose this method).  However, this method will not adjust the graph in order to avoid overlap.  In order to do that, you will need to use the Format Legend dialog box.  You can then change the position by choosing the Placement tab and then choosing any of the locations listed within that tab.  All other changes can be done within the various tabs.

Titles

You can adjust the color, font and alignment of any of the titles present on your graph. Adjusting the color and font has been discussed several times within this tutorial already; however alignment has not been discussed since the first Excel tutorial.  Refer to the picture below for a review of the alignment options.

 

Even though you have already created a graph, it is still very simple to make adjustments to the data.

Changing a Value

Every chart you create is linked to the data in a worksheet.  As a result, if you change the data in the worksheet, Excel will automatically update the chart to reflect the change.  This is true for category labels as well as for data values.  A way to remember the connection between the data


Editing Data in a Chart

and the charts is by thinking of the cells you highlighted at the beginning or during the Chart wizard.  Any adjustments made in these cells will be reflected in your chart.

Adding a Value

There are several different circumstances in which you may wish to add a new value.  Perhaps you wish to add another row or column of information, such as another month.  Or perhaps you wish to add a complete new series of information, such as another spending category.  (A series is a set of related data points that are plotted in a chart.  Each data series in a chart has a unique color or pattern and is represented in the chart legend.)  Both of these tasks can be done after creating your chart.  Multiple methods exist for accomplishing these tasks. This tutorial will take you through just one method.  You may find quicker ways to accomplish certain tasks on your own.  That is fine.  The method described, however, will work for any kind of added values.

First, return to the worksheet and locate the cells that are attached to the chart.  If you are unsure which cells are attached, return to the chart sheet.  Select Chart from the Menu Bar.  Then click on Source Data.  After clicking Source Data, a dialog box will appear.  Be sure you have the Data Range tab select.  You will then be taken to the worksheet with the active data.  Observe what data is connected to the chart and then select Cancel.  Once you have located the attached data, you can then add the values you wish.  If you need to insert a row or column, do so.  Be aware that you can cut and paste partial information if you do not wish to add a row or column.  If you do use this method, be sure to check your formulas to make sure they include the correct information after adding new values.  

Once all the additions have been made, return to the chart sheet.  At this point you will again select Chart from the Menu Bar; then Source Data.  At this point you need to select the new range of data that you wish to include on your chart. Remember, you have already learned how to use this dialog box at the beginning of the tutorial.  Also be aware, if you added a new series, you may want to move to the second tab of the dialog box, Series, and check that all the necessary adjustments have been made for the new series.  Most often, Excel adds the new series without difficulty, but it is still a good idea to check this tab and make any needed changes.

 

Editing Data in a Chart

Adding Error Bars

Excel also allows you to easily add error bars to your chart.  Error bars are used in statistical or scientific data and show potential error or degrees of uncertainty relative to each data marker in a series.  Adding these error bars is very simple.  First, you must be viewing your chart.  Next, select the data series to which you wish to add error bars.  Then, using any of the methods you learned earlier in this tutorial, bring up the Format Data Series dialog box. You will need to select the X Error Bars or the Y Error Bars tab that is present in this box.  Refer to the picture below for instructions on how to use this menu.

 

When you have completed your chart, you may wish to make a hard copy of it by printing.  Printing a chart sheet is similar to printing a worksheet.  As when printing a worksheet, you should preview the printout before sending the worksheet to the printer.  From the Print Preview window, you can add headers and footers and control the page layout, just as you do for printing the contents of your worksheet, which you learned how to do in earlier tutorials.  You also have the added option of resizing the chart to fit within the confines of a single printed page.  To set the print options for your chart, you need to be viewing the chart sheet; however, it is irrelevant what you have selected at this time.  Then click File on the menu bar, followed by Page Setup.  Once in page setup you will need to click the Chart tab.  Within this menu, you have the option of choosing the Printed Chart Size.  Choose either Use full page, Scale to fit page, or CustomUse Full Page indicates that the chart will be resized to fit the full page, extending out to the borders of all four margins.  The proportions of the chart may change since it is extended in all directions to fit the page.  This is the default option.  Scale to fit page indicates that the chart will be resized until one of the edges reaches a margin border.  The proportions of the chart remain unchanged, but it might not fit the entire page.  Custom indicates that the dimensions of the printed chart are specified on the chart sheet outside of the Print Preview window.  You can also have the option of printing the chart at Draft quality or in black and white within this window.

Copying and Pasting

 

3e

Copying and Pasting

Microsoft Excel makes it very simple to copy your chart into any other Microsoft program.  You are able to use the same procedure you use whenever you copy any information in a Microsoft program.  To copy a chart you must be sure you are viewing it.  Then, regardless of what element, if any, is selected in the chart, select Edit from the menu bar and then Copy.  You are also able to copy the chart by right-clicking and then choosing Copy from the list of options.  Once the chart is copied you are able to paste it into any Microsoft program. Simply start the program, place your insertion point wherever you would like to insert the chart.  Then select Edit from the menu bar and then Paste. You can then resize the chart by dragging the resize handles located on the corners of the image. You can also apply text-wrapping and other various options in the same way you would an image.

Case Perspective

 

This Case Perspective continues with the same situation presented in Case Perspective 1 and Case Perspective 2 found at the ends of the previous two tutorials.  You will begin by using the finished product of Case Perspective 2 which built on the finished product of Case Perspective 1.  If you have not already completed these Case Perspectives, you will need to complete them in order to begin this one.

In this Case Perspective, you will be responsible for creating, editing, and formatting a chart to represent a portion of the information from the previous Case Perspectives.

It’s your turn to practice!!  Read the Case Perspective to the left.  Then recreate the documents pictured on the following sheet using the guidelines presented on this page.

•   Create a chart for the information in cells A2:C14.

•   When prompted, choose to create a line graph.  Choose the subtype on the second row, first column.

•   Create a Chart Title of Summerfield Counseling’s Annual Spending.

•   Create an X-axis title of Months and a Y-axis title of Amount.

•   Turn on Minor Gridlines for the X-axis.  (Major Gridlines are already selected so leave them on).

•   Create the chart as a new sheet and title the new sheet Annual Spending.

•   Set the zoom for the new chart at 75% so you can see the entire chart.

•   You decide you do not like the minor gridlines so you need to remove the minor gridlines for the Xaxis.  (Remember that you can access all the screens found in the wizard by selecting Chart from the menu bar).

•   Next, change the scale for the Major Unit for the Y-Axis Scale from 500 to 250.

•   Change the alignment for the X-axis (the months).  Set the Orientation to 30 degrees.

•   Change the background area of the Legend to a light gray color.  

•   Also, adjust the font to size 14 pt.  

•   Next, move the legend to the bottom of the chart (use the Format Legend dialog box for these steps).

•   Change data value for the April PR cost to $200.21 and then view the change in your chart.

•   You will now need to add a new series of information which will be another spending category.  You first need to cut cells D2:D15 and then paste them into E2:E15.

•   The heading for this column is Executive in D2.  In cells D3 through D14 enter the following information:  556.12; 452.15; 487.42; 150.49; 578.12; 321.21; 986.45; 842.15; 215.12; 1091.34; 465.14; 789.23

•   In D15 place the sum of cells D3:D14.  Don’t worry about formatting for this column.

•   Change the source data for the chart to cells A2:D14.

•   Now you need to add error bars to the data series PR.  Add error bars that are Both (positive and negative) and set the value at 8%.

The image below is only of the chart itself.  If all of the data series appear correct, it can be assumed that the worksheet values are correct. 

Be sure to email a copy of your completed project to your instructor.

It must have YOUR NAME as the subject line.


 



5