Excel in MS Excel (advanced tutorial)

The tutorial's second part on advanced formatting , is presented here.

Note:

• The screen reading software referred to in this document is JAWS for Windows 4.0.

• The menu commands and other features explained here are as found in Excel 9.0 of Microsoft Office 2000.

• The screen reader keystrokes have been prefixed with ‘Screen reader command’ to distinguish them from the application keystrokes.

- Using References: with references, you can identify cells or groups of cells on a worksheet.

- Range (colon): produces one reference to the cells enclosed between a rectangular area using the cell addresses of the two cells at the edges. It is

written as

(Top Cell Address : Bottom Cell Address)

For example, if you want to refer to cells A1, A2, A3 & A4 then the range will be written as (A1:A4)

• Similarly, if cells A1, A2, A3, B1, B2 & B3 are to be referred, the range will be written as (A1:B3)

4. Writing Formulas

Writing Formulas

You can perform calculations with your data using formulas, which are made up of data operators and often functions. Microsoft Excel offers you a variety

of functions that makes calculations easier for you.

Creating Simple Formulas

To tell Excel that you're entering a formula in a cell, you must begin the entry with an equal sign (=).

Lets create a simple formula with adds the value 25 to 35. In a blank cell let's say A1 enter

= 25 + 35

After entering the formula, press the Enter key for Excel to accept the formula. The result 60 will appear in A1. The formula however is displayed in the

formula bar. While navigating around in a worksheet, whenever the pointer selects a cell which has a formula, the screen reader announces this message.

The formula can be read by giving the command **CONTROL + F2.

Your formulae can use any of the numeric operators: plus (+) for addition, minus (-) for subtraction, asterisk (*) for multiplication, and slash (/) for

division. Other kinds of numeric operators include percent (%) which divides the preceding value by 100, and caret (^) which multiplies the preceding value

by itself the number of times specified by the following value .

For example, if you type = 23 ^ 3 in cell A1 and press enter, Excel multiplies 23 by itself there times and displays the result: 12167 in the cell.

Order of Evaluation

Excel performs a rigid set of rules, known as order of evaluation, when performing multiple computations in a single formula. Table 4.1 lists the Microsoft

Excel numeric operators in order to evaluate, from first to last. When two operators have equal precedence (for example, division and multiplication),

Excel performs the computations from left to right

-Negation

^ Exponentiation

/,* Division, Multiplication

+,- Addition, Subtraction

Let's take a look at this example

84 * 0.7 ^ 2 /24 * 3.26 - 29

Stepwise evaluation

1. Exponentiation = 84 * 0.7 ^ 2 /24 * 3.26 - 29

2. Multiplication = 84 8 0.49 / 24 * 3.26 - 29

3. Division = 41.16 / 24 * 3.26 - 29

4. Multiplication = 1.715 * 3.26 - 29

5. Subtraction = 5.5909 - 29

6. Result = -23.4091

You can override Excel's order of evaluation by enclosing parts of the formulae in parentheses. Excel then performs enclosed computations before proceeding

with the usual order of evaluation.

For example:

= (19+2) / 3

The sequence of evaluation will be as follows:

1. Addition = (19+2) / 3

2. Division = (21) / 3

3. Result = 7

Using Cell Addresses in Formulas

So far, the formulas we have used consist solely of values and operators. Formulas can also refer to the other cells. For example, the formula

= A1 + A2 + A3

Returns the sum of the values in cells A1, A2, A3

Excel offers a variety of functions that take a lot of the hard work out of creating and entering formulas. Refer back to the above formula. Instead of

typing

= A1+ A2 + A3

We could use the SUM function, that is

= SUM (A1:A3)

For finding out the maximum value in a range of cells, the following formula should be written.

=max (Range)

Similarly, for finding the minimum value

=min (Range)

For evaluating the average

=average (Range)

Some other important formulas are discussed below:

the IF formula

For logical purposes like allotting Grades to students on the basis of percentage/total marks the IF function is used.

=IF(CONDITION, "OPTION 1", "OPTION 2")

Here CONDITION refers to the basis on which distinction will be made and OPTIONS refer to the grades/remarks to be given.

If the number of options is more than two then two or more IF arguments are used in the same formula.

=IF(CONDITION 1, "OPTION 1", IF(CONDITION 2, "OPTION 2", IF(CONDITION 3, " OPTION 3", "OPTION 4")))

The number of Ifs USED is ALWAYS one less than the number of options AVAILABLE.

For inserting current date and time

=NOW ()

For counting the number of occurrence of certain text in data:

=COUNTIF(RANGE, "TEXT")

For calculating the future value or the maturity value of an investment:

=FV(RATE%, PERIOD,, PRINCIPAL AMOUNT)

For calculating the payments to be made for repayment of a loan:

=PMT(RATE%, NUMBER OF INSTALMENTS, LOAN AMOUNT)

For knowing the weekday "weekday" function is used. Syntax =weekday ("date of birth")

Copying Formulas

You can copy formulas into a range of cells just as you can copy data into a range. To copy a formula, select the cell containing the formula and use the

copy and paste command as explained earlier.

Totaling Rows and Columns Automatically

One of the tasks that you need to frequently do is total rows and columns. You could create a new formula every time you needed to total a row or column,

but Microsoft Excel provides an easier way. The AutoSum button on the Standard toolbar automatically creates a formula to total the rows and columns for

you.

To use the AUTOSUM formula from the keyboard, press ALT = in the cell where you want the total. Excel then displays a SUM formula containing the range for

which totaling will be done. If the range is acceptable, press ENTER. The result of the AUTOSUM formula is displayed in the cell.

AutoSum can be used in three ways:

1. locate and total the rows and columns in the range nearest to the current cell, or

2. total any range you select or

3. add grand total to a range containing other totals

To automatically total the nearest range, you type the AUTOSUM formula or click AutoSum bottom and press Enter, or double click the AutoSum button.

To total a specific range, you select a range and then use the AUTOSUM command or click the AutoSum button.

When you use the AutoSum button or type in ALT = once, the formula is created, and then have the option of accepting it (by pressing Enter), or modify it.

Whichever method you use, be sure that there is a blank row and column around your data for the cells you want to total.

5. Formatting Data

Formatting Data

Excel allows you to format worksheet cells before or after you enter data. You can-

• Change the row height and column width to fit the contents of the row and columns.

• Change the font, font-size and font-style of individual characters in the cell.

• Change the alignment of text

• Change the format of numeric data

• Add borders and colours.

Changing the Column Width and Row height

When an entry is too long to be fully displayed you can remedy the situation by widening the column. To widen any column (example column A) follow the instructions

given below.

You can adjust several columns at once by first selecting the columns and then adjusting the width of any one of the selected columns.

Using the Format menu to change the column width

1. Select any cell in the column A. (You need to select only one cell in a column to change the width for entire column).

2. Choose the Column command from the format menu. Then select the appropriate option from the following choices available in its sub menu.

Width Command: Displays the Column Width dialog box. You can enter the number 0 through 255 in the Column Width box.

AutoFit Selection Command: Sets the column to the minimum width necessary to display the contents of the selected cells.

Hide Command: hides the selected columns. The hidden column is then not displayed on the screen.

Unhide Command: Unhide hidden columns within the selection. To select hidden columns, select cell in the columns immediately to the left and right of the

hidden one.

Standard Width Command: Displays the Standard Width dialog box. To return the selected columns to the standard width choose the OK button.

Adjusting Row Height

It is similar to changing the width of columns. You can select the Row command from the Format menu, and then choose a command to

• Set a numeric row height (Height command)

• Automatically fit the row to the largest font in the row (AutoFit Command)

• Hide or unhide rows (Hide or Unhide command)

Alternatively, when you double-click the lower border of a row heading, the row-height adjusts to fit the tallest entry in the row.

You can adjust several rows at once by selecting rows and then adjusting the height on any one of the selected rows.

Aligning Worksheet Data

When you open a new sheet and begin entering data, your text is automatically left-aligned your numbers are automatically right-aligned. However you may

decide to change the alignment of the text to be centered in the cells. You can easily align text to the right, left, or centre text across columns.

1. Select the cell or range that you want to change

2. Select the CELLS command from the FORMAT menu. CONTROL + 1 is the shortcut for this command.

3. A dialog box opens. Switch to the ALIGNMENT tab page (CONTROL + TAB can be used).

4. Select from the HORIZONTAL & VERTICAL combo box the alignment you want for the selected data.

5. This dialog box also has the following three checkboxes: Wrap text-Wraps text into multiple lines in a cell. The number of wrapped lines is dependent

on the width of the column and the length of the cell contents.

Shrink to fit-Reduces the apparent size of font characters so that all data in a selected cell fits within the column. The character size is adjusted automatically

if you change the column width. The applied font size is not changed.

Merge cells-Combines two or more selected cells into a single cell. The cell reference for a merged cell is the upper-left cell in the original selected

range.

6. Orientation combo box-It can be used to change the orientation of the text that is make it inclined. It sets the amount of text rotation in the selected

cell. 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.

7. Select OK button to apply the changes.

To change the alignment quickly, Click the Align Left, Centre, or Align Right buttons on the Formatting toolbar.

If you want to align a title across several columns, you select the cells across which you want the text to be centered in and click the CENTRE ACROSS COLUMNS

button on the formatting toolbar. This command is also available in the HORIZONTAL combo box on the ALIGNMENT tab discussed above.

Formatting Fonts

You can change fonts and font size with the Font and Font Size boxes on the Formatting toolbar, or you can use the Format cells command. You can also use

the Bold, Italic, or Underline button on the toolbar.

Changing Fonts and Sizes

1. Select the cells whose font you want to change. The entire worksheet should be selected to apply the same font for the whole sheet.

2. Select the CELLS command from the FORMAT menu. Switch to FONT TAB.

3. select a font and font style of choice from the FONT combo box and FONT STYLE combo box.

4. The FONT SIZE combo box Changes the size of the selected text. The sizes available depend on the printer and the selected font.

5. This dialog box also provides choices for UNDERLINE STYLE, FONT COLOUR & STRIKE THROUGH.

6. Select OK to apply the changes.

Adding Borders and Colors

You emphasize particular areas of the sheet and specific cells by using borders and colors. Borders add lines above, below, or to either side of the cell

or around it, you can shade a cell in one of many patterns or colors.

The BORDER and PATTERNS TABS of the FORMAT CELLS dialog box(CONTROL +1) provide numerous options for making the worksheet look more attractive.

On the BORDER TAB page use the following.

• BORDER STYLE: Select an option under Style to specify the line size and style for a border.

• COLOUR BUTTON MENU: Select a color from the list to change the color of the selected text or object.

PRESET BUTTONS: Select NONE button to remove existing border. Choose from OUTLINE, INSIDE buttons to apply border outside or the inside the edge of the

cells selected.

• BORDER CHECK BOX: Use them to apply or remove border on any side of the selection. When the checkbox is checked (using SPACEBAR) the border is applied.

The PATTERNS TAB offers the following choices:

• PATTERN COLOUR PALETTE- Select a color to change the background color of the selection.

• PATTERN MENU- Select a background color in the Color box, and then select a pattern in the Pattern box to format the selection with color patterns.

To apply borders, or colors through mouse, select the cell you want to change and then use the following buttons on the toolbar.

Border buttons

Colour buttons

Adding Number Formats

The default number format for all cells on a new worksheet is the General format. In the General format, Excel displays numbers as integers (789), decimal

fraction (7.89) or a scientific notation (7.89E+08) if the number is longer than the width of the cell. The General format displays up to 11 digits.

Changing Number Format

You can format the numbers in a cell by using the Currency style, Percent Style, and Comma Style buttons on the toolbar. Each of these styles has a default

number of decimal points that you can change with the increase Decimal and Decrease buttons.

Numbers can be formatted by either selecting the Style button on the formatting toolbar or selecting the FORMAT CELLS COMMAND.

The advantage of using the FORMAT CELLS COMMAND is that you can use more choices of number formats than you can do with the buttons on the toolbar.

To add currency style:

1. Select the cells to be formatted.

2. Select CELLS command in the FORMAT menu to open the dialog box. Change to NUMBER TAB.

3. It has a CATEGORY LIST BOX. Use it to select an option in the list, and then select the options that you want to specify with this number format. The

Sample box shows how selected cells will look with the formatting you choose.

4. Choose currency from the list. Then specify the number of digits you want after decimal in the edit box that follows.

5. Choose a symbol for the currency from the SYMBOL combo box.

6. You also need to choose a format for the negative numbers from the list box. Do OK to apply the currency format.

Alternatively, click the currency style button on the formatting toolbar.

To add percent style

1. Select the cells to be formatted to the percent style.

2. Select PERCENT from the CATEGORY LIST box(discussed above).

3. Specify the number of decimal places in the following edit box and press OK button.

From the mouse, click the percent style button on the toolbar

Applying the percent style causes the number .12 to be displayed as 12%

Formatting Dates

To format dates you would have to use FORMAT CELLS option and then select DATE from the category list. You have a variety of date option to select from.

For example, if you have entered July 5, 1994 in a cell and from the Format Codes list, you select dd-mm-yy the date would be displayed as 5-Jul-94

6. Printing & Other Topics

Previewing Worksheet

Previewing your worksheet shows you what you need to change before you print. This option is helpful because you don't waste a lot of time and paper checking

to see whether the necessary rows and columns appear on the sheet.

To preview the worksheet:

1. Select the Print Preview option from the FILE MENU or

2. Click the PRINT PREVIEW BUTTON on the toolbar

3. Your sheet appears in the Preview window. You can use the Zoom button which allows you to magnify the contents of the worksheet.

4. 4. To use the screen reader to check the preview , activate the JAWS CURSOR using **INSERT + -(DASH). Then use PAGEUP to take the cursor to the top of

the window. Now read the screen using the arrow keys. This way you can get an idea of the text on the page.

Page Setup

Before printing a worksheet, the margins, the paper size and headers and footers need to be fixed according to requirement. The PAGE SETUP command provides

various options which are discussed below.

From the File menu, choose PAGE SETUP. The PAGE SETUP dialog box opens. It has THE FOLLOWING four tab pages

Adding margins

1) MARGINS TAB: This page has the following fields.

• TOP, BOTTOM, LEFT & RIGHT margins- These are in the form of spin boxes that is a value can be written or chosen using the arrow keys. Enter margin settings

and see results in the Preview box. Adjust measurements in the Top, Bottom, Left, and Right boxes to specify the distance between your data and the edge

of the printed page.

• HEADER & FOOTER- Enter a number in the Header or Footer box to adjust the distance between the header and the top of the page or between the footer and

the bottom of the page. The distance should be smaller than the margin settings to prevent the header or footer from overlapping the data.

• CENTER OF PAGE- Center the data on the page within the margins by selecting the vertically check box, the horizontally check box, or both.

• PRINT & PRINT PREVIEW buttons- These can be used to preview the output and then print the pages.

• OPTIONS button- Use Options to set options specific to your selected printer.

• Ok & CANCEL BUTTONS- OK button closes this dialog box and applies any changes you've made. CANCEL button closes the dialog box and does not apply any

Change.

Choosing paper size

2) PAGE TAB: This page has options for setting the paper size and orientation. The options are-

• PORTRAIT/LANDSCAPE radio button- In portrait position the height of the page is more than the width whereas in landscape position the width is more than

the height.

• SCALING - Reduces or enlarges the printed worksheet. Select the Adjust to check box, and then enter a percentage number in the % normal size box. You

can reduce the worksheet to 10 percent of normal size or enlarge it to 400 percent of normal size.

• Fit to option reduces the worksheet or selection when you print so that it fits on the specified number of pages. Select the Fit to check box, enter a

number in the page(s) wide by box, and enter a number in the tall box. To fill the paper width and use as many pages as necessary, type 1 in the pages(s)

wide by box and leave the tall box blank.

• PAPER SIZE (combo box) - Select Letter, Legal, or other size options to indicate the size you want your document printed.

• PRINT QUALITY - Click the resolution you want to specify print quality for the active worksheet. Resolution is the number of dots per linear inch (dpi)

that appear on the printed page. Higher resolution produces better quality printing in printers that support high-resolution printing.

• FIRST PAGE NUMBER- Enter Auto to start numbering pages at "1" (if it is the first page of the print job) or at the next sequential number (if it is not

the first page of the print job). Enter a number to specify a starting page number other than "1."

• OK, CANCEL, PRINT & PRINT PREVIEW buttons- Like the previous tab page these options are here also and perform the same functions.

Defining print area

3) SHEET TAB: It is used to specify the printing area. It has the following fields.

• PRINT AREA (EDIT BOX) - Click the Print area box to select a worksheet range to print, and then drag through the worksheet areas that you want to print.

The Collapse Dialog button at the right end of this box temporarily moves the dialog box so that you enter the range by selecting cells in the worksheet.

When you finish, you can click the button again to display the entire dialog box.

• ROWS TO REPEAT AT TOP & COLUMNS TO REPEAT AT TOP- Select an option under Print titles to print the same columns or rows as titles on every page of a printed

worksheet. Select Rows to repeat at top if you want specific rows as your horizontal title for each page. Select Columns to repeat at left if you want

vertical titles on each page. Then on the worksheet, select a cell or cells in the title columns or rows you want. The Collapse Dialog button at the right

end of this box temporarily moves the dialog box so that you enter the range by selecting cells in the worksheet. When you finish, you can click the button

again to display the entire dialog box.

• GRID LINES (CHECK BOX) - Select the Gridlines check box to print horizontal and vertical cell gridlines on worksheets.

• BLACK & WHITE (CHECK BOX) - Select the Black and white check box if you formatted data with colors but are printing on a black-and-white printer. If you

are using a color printer, selecting this option may reduce printing time.

• DRAFT QUALITY (CHECK BOX) - Select the Draft quality check box to reduce printing time. When this option is selected, Microsoft Excel does not print gridlines

and most graphics.

• ROW & COLUMN HEADINGS (CHECK BOX) - Select the Row and column headings check box to print row numbers and column letters in the A1 reference style or

numbered rows and columns in the R1C1 reference style.

• COMMENT (COMBO BOX) - Select at end of sheet option to print comments beginning on a separate page at the end of the document. Select the As displayed

on sheet option to print comments where they are displayed when you view them on the worksheet. If you click As displayed on sheet, Microsoft Excel will

print only the comments that are displayed. To display all comments, click Comments on the View menu. To display an individual comment, right-click the

cell that contains the comment, and then click Show Comment on the shortcut menu.

• PAGE ORDER (RADIO BUTTON) - Click Down, then over or Over, then down to control the order in which data is numbered and printed when it does not fit on

one page. The sample picture previews the direction your document will print when you choose one of these options.

• OK, CANCEL, PRINT & PRINT PREVIEW buttons - They perform the same function as in MARGINS & PAGE tab pages.

Header & Footers

4) HEADER /FOOTER TAB: It has options to create the desired header and footer for the pages to be printed.

• Select a built-in header or footer in the HEADER/FOOTER BOX. The built-in header or footer is copied to the Header/Footer dialog box where you can format

or edit the selected header. Or use CUSTOM HEADER OR FOOTER BUTTONS to create a custom header for your worksheet.

• The CUSTOM HEADER/FOOTER button opens a dialog box. The dialog box has three sections for typing in the text of the header.

• Enter the text you want in the Left section box to display or print the header in the top-left corner of the worksheet or the footer in the bottom-left

corner of the worksheet.

• Enter the text you want in the Center section box to display or print the header or footer centered at the bottom of the worksheet.

• Enter the text you want in the Right section box to display or print the header in the top-right corner of the worksheet or the footer in the bottom-right

corner of the worksheet.

There are some buttons in this dialog box:

• The FONT BUTTON Changes the font, font size, and text style of the selected text in the Left section, Center section, or Right section box.

• Page Number button inserts page numbers in the header or footer when you print the worksheet. Microsoft Excel updates the page numbers automatically when

you add or delete data, or set page breaks.

• Total Pages button inserts the total number of pages in the active worksheet and adjusts the page numbers automatically when you print the worksheet.

For example, if you want to use the format "Page 1 of 12," "Page 2 of 12," and so on, click where you want to insert the first page number, and then click

where you want to insert the total page number.

• Date button inserts the current date.

• Time button inserts the current time.

• File Name button inserts the file name of the active workbook.

• Sheet Name button inserts the name of the active worksheet.

• OK, CANCEL, PRINT & PRINT PREVIEW buttons are provided in this tab page also and perform the same function as discussed above.

Adding & Removing Page Breaks

When you print a large worksheet, page breaks are automatically added. You might not always like where you page breaks occur. If you want to insert your

own page breaks at specific rows

Select a row and use the Page Break command on the Insert menu. The Page break is inserted above the selected rows.

To add a vertical page break that is to print only a particular number of columns on a page select the column to the right of the place where you want the

page break and then select the PAGE BREAK command from the INSERT menu.

To add both vertical and horizontal page breaks, select a cell at the bottom right corner of the place where you want the page break. Then select the PAGE

BREAK command from the INSERT menu. Horizontal and vertical page breaks will be inserted along the top and left borders of the selected cell.

To remove a page break added manually, you select the row below the page break, and use the Remove Page Break on the insert menu. The same should be done

to remove vertical page breaks after selecting the column to the right of the page break.

Printing a Worksheet

After you have setup your worksheet, you've can print them. Use the Print command from the File menu.

When you use the Print command, you can make additional choices before you print, such as select the number of copies or select the pages to print. You

can also print a selected range, a selected sheet, or your entire workbook.

Copying and Pasting Cells for Special Results

After you have copied cells using the Copy command, you can choose the Paste Special command from the Edit menu to select several options for pasting the

copied cells

You can perform the following operations using the Paste Special dialog box:

- Paste only a cell's formula, value, format, or note by selecting an option button under Paste.

- Combine the contents of the copy and paste areas in selecting the Formulas or Values option under Paste and then, under operation , selecting the operation

you want to use to combine each copied cell and its paste area

For example, if you select the subtract option button, the copied formula or value will be subtracted from the special area formula or value

- Transpose the copied rows and columns in the paste area by selecting the paste area and then selecting the Transpose check box. For example, if the data

in the copy area is arranged in columns, the data will be pasted in rows.

Creating Formulas with Relative and Fixed References

Excel follows two types of cell addresses: relative and fixed. Relative cell addresses identify cells by their position in relation to the active cells.

This means that when you copy or move a formula, unless you specify otherwise, the addresses of the cells in the formula will be adjusted automatically

to fit the new location.

As an example of relative addressing, suppose that you want to sum the contents of several columns of cells, but you don't want to enter =SUM() function

over and over again. Only column C is summed, using the formula =SUM(C5:C8) in the cell C10. You want to add the contents of the cells in column D, E,

F and G in the same manner that the contents of cells in column C were added. Copy the Formula over cells D10, E10, F10 and G10. The column address in

the formula =SUM(C5:C8) changes from Column C to D,E,F and G respectively.

Fixed Cell addresses, on the other hand, refer to the fixed or absolute position of cells. Fixed addresses use dollar signs ()$) to indicate absolute position

of the cells addresses. For example, the formula

=$A$1*C3

will multiply the contents of cell C3 with that of A1. this formula if copied to other cells will only change the address of second cell that is C3 in reference

to the active cell.

Mixed cell addressing refers to a combination of relative and absolute addressing. Because a cell address has two components a column and a row It is possible

to fix either portion while leaving the other unfixed or relative

$D10 Column address fixed

D$10 Row address fixed

To change the relative address to a fixed address or absolute address, select the reference in the formula and press F4. Or you can type a dollar sign ($)

before both column and row indicators in the cell.

Manipulating Sheets

Removing Sheets

Assuming you have 16 sheets in your workbook and you want to delete a sheet. The first thing you have to do is to make sure that the sheet you want to delete

is the active sheet. Follow these steps.

• From the Edit menu, choose Delete Sheet. A dialog box opens informing you that the sheet will be deleted permanently.

• Choose OK to confirm the deletion. The dialog box closes and the sheet is deleted.

Adding Sheets

Just as you remove sheets from the workbook you can also add new sheets. As discussed earlier you can have a maximum of 255 sheets in a workbook. To insert

a sheet before Sheet 3.

• Make sheet 3 active.

• From the Insert menu, choose Worksheet.

A new sheet is inserted before Sheet3.

Naming Sheets

The Sheets are named as Sheet1, Sheet2, etc. To change the name of Sheet1 to a more meaningful name.

• Select the RENAME command from inside the SHEET sub menu which is in the FORMAT menu, or alternatively, double click the Sheet1 tab. The Rename Sheet

dialog box opens.

• In the Name box, type 1994 Sales and then press Enter. Sheet1 is renamed to 1994 Sales. Sheet1 will now be renamed to 1994 Sales.

7. Charting Your Data

What are Charts

A worksheet calculates and presents differences and similarities between numbers. It also displays the changes in numbers over time. But data by itself

cannot illustrate these effectively. With charts, you make your data visual. You can create charts in Excel using ChartWizard.

Creating Charts

You can create charts in two ways:

- either on the same sheet as your data;

- or on a separate chart sheet in the same workbook

When you create a chart on the same sheet as your data, you can view both the data and the chart at the same time. When you create a chart on separate chart

sheet in the same workbook, you still have easy access to the chart, but you can print the chart separately.

To create a chart.

1. Select the data that you want to use in the chart.

2. Select CHART from the INSERT menu.

3. A wizard opens. It is called the chart wizard and it has four steps for creation of a chart.

FIRST STEP

• The first dialog box that opens is the CHART TYPE dialog box with two tab pages, namely STANDARD TYPES & CUSTOM TYPES.

• In the first step you have to choose the chart type you want from the CHART TYPE LIST BOX which appears on the STANDARD TYPE TAB. A brief description

of the various chart types available has been provided in the next section.

• Each chart type selected has few sub-types. You need to select one. The screen reader provides a description of the various sub-types.

• PRESS AND HOLD TO VIEW (button)- Previews the currently selected chart type as it looks when applied to your data.

• The CUSTOM TYPES TAB page of this dialog box can be used to create a new type of chart.

SECOND STEP

• On pressing the NEXT button the wizard opens the CHART SOURCE dialog box. This dialog box has two tab pages, DATA RANGE & SERIES. Do the following on

the DATA RANGE tab-

• DATA RANGE (edit box)- Click in the Data range box, and then select the data on your worksheet that you want to plot. If you select the data before you

start the Chart Wizard, or if you change the existing source data, the range may already appear in this box. The Collapse Dialog button at the right end

of this box temporarily moves the dialog box so that you enter the range by selecting cells in the worksheet. When you finish, you can click the button

again to display the entire dialog box.

• SERIES (radio button)- It has two options: columns and rows. You specify in which series the data should be used for creation of the chart.

• The SERIES list box on the SERIES TAB page lists existing data series names. You can add and remove data series from the chart without affecting the data

on your worksheet. You can also give names to the different series.

THIRD STEP

• In the third step the CHART OPTIONS dialog box opens. It has six tab pages.

• In the TITLES TAB you give the title for the chart, x-axis and y-axis.

• In the AXIS TAB you specify whether you want the categories for x-axis and y-axis to be displayed.

• The GRIDLINES TAB page has options for display of gridlines on the chart.

• The LEGEND TAB provides options for placement and positioning of legend which contains the names of the series on the chart.

FOURTH STEP

• The CHART Location dialog box opens in this step. Here you specify the sheet in which the chart is to be placed. You can choose (from the radio button)

whether you want the chart to be placed on a particular sheet in the workbook or you want to create a separate chart sheet.

• The new chart sheet is added to the active workbook, to the left of the active worksheet containing the associated data. The Chart Sheets you create in

a workbook are named Chart1, Chart2 and so on by default. You can rename these using the Rename Command.

• Press FINISH BUTTON to insert the chart on the worksheet.

Selecting the Chart type

Excel's ChartWizard includes several chart types. The type of chart you can create depends on the data you select. Your selection can include only one data

series in the chart, that is, either a single Row, or column, or it can contain multiple series multiple rows and columns.

Examples of chart types

Area chart

An area chart emphasizes the magnitude of change over time. By displaying the sum of the plotted values, an area chart also shows the relationship of parts

to a whole.

Column chart

A column chart shows data changes over a period of time or illustrates comparisons among items. Categories are organized horizontally, values vertically,

to emphasize variation over time.

Stacked column charts show the relationship of individual items to the whole. The 3-D perspective column chart compares data points along two axes.

Bar chart

A bar chart illustrates comparisons among individual items. Categories are organized vertically, values horizontally, to focus on comparing values and to

place less emphasis on time.

Stacked bar charts show the relationship of individual items to the whole.

Line chart

A line chart shows trends in data at equal intervals. The data series is plotted on the charts as points and then they are joined by lines.

Pie chart

A pie chart shows the proportional size of items that make up a data series to the sum of the items. It always shows only one data series and is useful

when you want to emphasize a significant element.

To make small slices easier to see, you can group them together as one item in a pie chart and then break down that item in a smaller pie or bar chart next

to the main chart.

Scatter chart

An xy (scatter) chart either shows the relationships among the numeric values in several data series or plots two groups of numbers as one series of xy

Coordinates. This chart shows uneven intervals - or clusters - of data and is commonly used for scientific data.

When you arrange your data, place x values in one row or column, and then enter corresponding y values in the adjacent rows or columns.

Bubble chart

A bubble chart is a type of xy (scatter) chart. The size of the data marker indicates the value of a third variable.

To arrange your data, place the x values in one row or column, and enter corresponding y values and bubble sizes in the adjacent rows or columns.

Surface chart

A surface chart is useful when you want to find optimum combinations between two sets of data. As in a topographic map, colors and patterns indicate areas

that are in the same range of values.

Radar chart

In a radar chart, each category has its own value axis radiating from the center point. Lines connect all the values in the same series.

A radar chart compares the aggregate values of a number of data series. In this chart, the data series that covers the most area, Brand A, represents the

brand with the highest vitamin content.

8. Appendix

Appendix A: Excel Application keystrokes

Keys for moving and scrolling in a worksheet or workbook

Press

Arrow keys

Move one cell up, down, left, or right

CONTROL+ ARRW key

Move to the edge of the current data region

HOME

Move to the beginning of the row

CONTROL+HOME

Move to the beginning of the worksheet

CONTROL+END

Move to the last cell on the worksheet, which is the cell at the intersection of the rightmost used column and the bottom-most used row (in the lower-right

corner), or the cell opposite the home cell, which is typically A1

PAGE DOWN

Move down one screen

PAGE UP

Move up one screen

ALT+PAGE DOWN

Move one screen to the right

ALT+PAGE UP

Move one screen to the left

CONTROL+PAGE DOWN

Move to the next sheet in the workbook

CONTROL+PAGE UP

Move to the previous sheet in the workbook

CONTROL+F6 or CONTROL+TAB

Move to the next workbook or window

CONTROL+SHIFT+F6 or CONTROL+SHIFT+TAB

Move to the previous workbook or window

Move to the next pane in a workbook that has been split

SHIFT+F6

Move to the previous pane in a workbook that has been split

CONTROL+BACKSPACE

Scroll to display the active cell

Display the Go to dialog box

SHIFT+F5

Display the Find dialog box

SHIFT+F4

Repeat the last Find action (same as Find Next)

TAB

Move between unlocked cells on a protected worksheet

Keys for previewing and printing a document

CONTROL+P or CONTROL+SHIFT+F12

Display the Print dialog box

Work in print preview

Arrow keys

Move around the page when zoomed in

PAGE UP or PAGE DOWN

Move by one page when zoomed out

CONTROL+UP ARROW or CONTROL+LEFT ARROW

Move to the first page when zoomed out

CONTROL+DOWN ARROW or CONTROL+RIGHT ARROW

Move to the last page when zoomed out

Keys for working with worksheets, charts, and macros

SHIFT+F11 or ALT+SHIFT+F1

Insert a new worksheet

F11 or ALT+F1

Create a chart that uses the current range

ALT+F8

Display the Macro dialog box

ALT+F11

Display the Visual Basic Editor

CONTROL+F11

Insert a Microsoft Excel 4.0 macro sheet

CONTROL+PAGE DOWN

Move to the next sheet in the workbook

CONTROL+PAGE UP

Move to the previous sheet in the workbook

SHIFT+CONTROL+PAGE DOWN

Select the current and next sheet in the workbook

SHIFT+CONTROL+PAGE UP

Select the current and previous sheet in the workbook

Keys for entering data

Keys for entering data on a worksheet

ENTER

Complete a cell entry and move down in the selection

ALT+ENTER

Start a new line in the same cell

CONTROL+ENTER

Fill the selected cell range with the current entry

SHIFT+ENTER

Complete a cell entry and move up in the selection

TAB

Complete a cell entry and move to the right in the selection

SHIFT+TAB

Complete a cell entry and move to the left in the selection

ESC

Cancel a cell entry

BACKSPACE

Delete the character to the left of the insertion point, or delete the selection

DELETE

Delete the character to the right of the insertion point, or delete the selection

CONTROL+DELETE

Delete text to the end of the line

Arrow keys

Move one character up, down, left, or right

HOME

Move to the beginning of the line

F4 or CONTROL+Y

Repeat the last action

SHIFT+F2

Edit a cell comment

CONTROL+SHIFT+F3

Create names from row and column labels

CONTROL+D

Fill down

CONTROL+R

Fill to the right

CONTROL+F3

Define a name

Keys for working in cells or the formula bar>

BACKSPACE

Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit cell contents

ENTER

Complete a cell entry

CONTROL+SHIFT+ENTER

Enter a formula as an array formula

ESC

Cancel an entry in the cell or formula bar

CONTROL+A

Display the Formula Palette after you type a function name in a formula

CONTROL+SHIFT+A

Insert the argument names and parentheses for a function after you type a function name in a formula

CONTROL+K

Insert a hyperlink

ENTER (in a cell with a hyperlink)

Activate a hyperlink

Edit the active cell and position the insertion point at the end of the line

Paste a defined name into a formula

SHIFT+F3

Paste a function into a formula

Calculate all sheets in all open workbooks

CONTROL+ALT+F9

Calculate all sheets in the active workbook

SHIFT+F9

Calculate the active worksheet

= (equal sign)

Start a formula

ALT+= (equal sign)

Insert the AutoSum formula

CONTROL+; (semicolon)

Enter the date

CONTROL+SHIFT+: (colon)

Enter the time

CONTROL+SHIFT+" (quotation mark)

Copy the value from the cell above the active cell into the cell or the formula bar

CONTROL+` (single left quotation mark)

Alternate between displaying cell values and displaying cell formulas

CONTROL+' (apostrophe)

Copy a formula from the cell above the active cell into the cell or the formula bar

ALT+DOWN ARROW

Display the AutoComplete list

Keys for formatting data

ALT+' (apostrophe)

Display the Style dialog box

CONTROL+1

Display the Format Cells dialog box

CONTROL+SHIFT+~

Apply the General number format

CONTROL+SHIFT+$

Apply the Currency format with two decimal places (negative numbers appear in parentheses)

CONTROL+SHIFT+%

Apply the Percentage format with no decimal places

CONTROL+SHIFT+^

Apply the Exponential number format with two decimal places

CONTROL+SHIFT+#

Apply the Date format with the day, month, and year

CONTROL+SHIFT+@

Apply the Time format with the hour and minute, and indicate A.M. or P.M.

CONTROL+SHIFT+!

Apply the Number format with two decimal places, thousands separator, and minus sign (-) for negative values

CONTROL+SHIFT+&

Apply the outline border

CONTROL+SHIFT+_

Remove outline borders

CONTROL+B

Apply or remove bold formatting

CONTROL+I

Apply or remove italic formatting

CONTROL+U

Apply or remove an underline

CONTROL+5

Apply or remove strikethrough formatting

CONTROL+9

Hide rows

CONTROL+SHIFT+( (opening parenthesis)

Unhide rows

CONTROL+0 (zero)

Hide columns

CONTROL+SHIFT+) (closing parenthesis)

Unhide columns

Keys for editing data

Edit the active cell and put the insertion point at the end of the line

ESC

Cancel an entry in the cell or formula bar

BACKSPACE

Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit the cell contents

Paste a defined name into a formula

ENTER

Complete a cell entry

CONTROL+SHIFT+ENTER

Enter a formula as an array formula

CONTROL+A

Display the Formula Palette after you type a function name in a formula

CONTROL+SHIFT+A

Insert the argument names and parentheses for a function, after you type a function name in a formula

Display the Spelling dialog box

Keys for inserting, deleting, and copying a selection

CONTROL+C

Copy the selection

CONTROL+X

Cut the selection

CONTROL+V

Paste the selection

DELETE

Clear the contents of the selection

CONTROL+HYPHEN

Delete the selection

CONTROL+Z

Undo the last action

CONTROL+SHIFT+PLUS SIGN

Insert blank cells

Keys for moving within a selection

ENTER

Move from to bottom within the selection (down), or move in the direction that is selected on the Edit tab (Tools menu, Options command)

SHIFT+ENTER

Move from bottom to within the selection (up), or move opposite to the direction that is selected on the Edit tab (Tools menu, Options command)

TAB

Move from left to right within the selection, or move down one cell if only one column is selected

SHIFT+TAB

Move from right to left within the selection, or move up one cell if only one column is selected

CONTROL+PERIOD

Move clockwise to the next corner of the selection

CONTROL+ALT+RIGHT ARROW

Move to the right between nonadjacent selections

CONTROL+ALT+LEFT ARROW

Move to the left between nonadjacent selections

Keys for selecting data and cells

CONTROL+SHIFT+* (asterisk)

Select the current region around the active cell (the current region is a data area enclosed by blank rows and blank columns)

SHIFT+arrow key

Extend the selection by one cell

CONTROL+SHIFT+arrow key

Extend the selection to the last nonblank cell in the same column or row as the active cell

SHIFT+HOME

Extend the selection to the beginning of the row

CONTROL+SHIFT+HOME

Extend the selection to the beginning of the worksheet

CONTROL+SHIFT+HOME

Extend the selection to the beginning of the worksheet

CONTROL+SHIFT+END

Extend the selection to the last used cell on the worksheet (lower-right corner)

CONTROL+SPACEBAR

Select the entire column

SHIFT+SPACEBAR

Select the entire row

CONTROL+A

Select the entire worksheet

SHIFT+BACKSPACE

Select only the active cell when multiple cells are selected

SHIFT+PAGE DOWN

Extend the selection down one screen

SHIFT+PAGE UP

Extend the selection up one screen

CONTROL+SHIFT+SPACEBAR

With an object selected, select all objects on a sheet

CONTROL+6

Alternate between hiding objects, displaying objects, and displaying placeholders for objects

CONTROL+7

Show or hide the Standard toolbar

Turn on extending a selection by using the arrow keys

SHIFT+F8

Add another range of cells to the selection; or use the arrow keys to move to the start of the range you want to add, and then press F8 and the arrow keys

to select the next range

SCROLL LOCK, SHIFT+HOME

Extend the selection to the cell in the upper-left corner of the window

SCROLL LOCK, SHIFT+END

Extend the selection to the cell in the lower-right corner of the window

Tip : When you use the scrolling keys (such as PAGE UP and PAGE DOWN) with SCROLL LOCK turned off, your selection moves the distance you scroll. If you

want to keep the same selection as you scroll, turn on SCROLL LOCK first.

Keys for selecting cells that have special characteristics

CONTROL+SHIFT+* (asterisk)

Select the current region around the active cell (the current region is a data area enclosed by blank rows and blank columns)

CONTROL+/

Select the current array, which is the array that the active cell belongs to

CONTROL+SHIFT+O (the letter O)

Select all cells with comments

CONTROL+

Select cells in a row that don't match the value in the active cell in that row. You must select the row starting with the active cell.

CONTROL+SHIFT+|

Select cells in a column that don't match the value in the active cell in that column. You must select the column starting with the active cell.

CONTROL+[ (opening bracket)

Select only cells that are directly referred to by formulas in the selection

CONTROL+SHIFT+{ (opening brace)

Select all cells that are directly or indirectly referred to by formulas in the selection

CONTROL+] (closing bracket)

Select only cells with formulas that refer directly to the active cell

CONTROL+SHIFT+} (closing brace)

Select all cells with formulas that refer directly or indirectly to the active cell

ALT+; (semicolon)

Select only visible cells in the current selection

Keys for selecting charts and chart items

CONTROL+PAGE DOWN

Select the next sheet in the workbook, until the chart sheet you want is selected

CONTROL+PAGE UP

Select the previous sheet in the workbook, until the chart sheet you want is selected

Keys for selecting an embedded chart

Note: The Drawing toolbar must already be displayed. Press F10 to make the menu bar active.

Press CONTROL+TAB or CONTROL+SHIFT+TAB

to select the Drawing toolbar.

Press the RIGHT ARROW key

to select the Select Objects button on the Drawing toolbar.

Press CONTROL+ENTER

to select the first object.

Press the TAB key

to cycle forward (or SHIFT+TAB to cycle backward) through the objects until sizing handles appear on the embedded chart you want to select.

Press CONTROL+ENTER

to make the chart active.

Keys for selecting chart items

DOWN ARROW

Select the previous group of items

UP ARROW

Select the next group of items

RIGHT ARROW

Select the next item within the group

LEFT ARROW

Select the previous item within the group

Keys for menus and toolbars

F10 or ALT

Make the menu bar active, or close a visible menu and submenu at the same time

TAB or SHIFT+TAB (when a toolbar is active)

Select the next or previous button or menu on the toolbar

CONTROL+TAB or CONTROL+SHIFT+TAB (when a toolbar is active)

Select the next or previous toolbar

ENTER

Open the selected menu, or perform the action assigned to the selected button

SHIFT+F10

Show a shortcut menu

ALT+SPACEBAR

Show the program icon menu (on the program title bar)

DOWN ARROW or UP ARROW (with the menu or submenu displayed)

Select the next or previous command on the menu or submenu

LEFT ARROW or RIGHT ARROW

Select the menu to the left or right or, with a submenu visible, switch between the main menu and the submenu

HOME or END

Select the first or last command on the menu or submenu

ESC

Close the visible menu or, with a submenu visible, close the submenu only

CONTROL+DOWN ARROW

Display the full set of commands on a menu

Tip: you can select any menu command on the menu bar or on a visible toolbar with the keyboard. To select the menu bar, press ALT. (Then to select a toolbar,

press CONTROL+TAB repeatedly until you select the toolbar you want.) Press the underlined letter in the menu name that contains the command you want. In

the menu that appears, press the underlined letter in the command name that you want.

Insert an AutoShape by using the keyboard

1. Press ALT+U to select the AutoShapes menu on the Drawing toolbar.

2. Use the arrow keys to move to the category of AutoShapes you want, and then press the RIGHT ARROW key.

3. Use the arrow keys to select the AutoShape you want.

4. Press CONTROL+ENTER.

Note: To edit the AutoShape, select the AutoShape, and then use the keyboard to select AutoShape on the Format menu. Select the options you want on the

available tabs.

Insert a text box by using the keyboard

Use the keyboard to select the Text Box button on the Drawing toolbar.

Press CONTROL+ENTER.

Type the text you want in the text box.

When you finish typing and want to switch back to the worksheet, press ESC twice.

Note: To format the text box (add a fill color or change the size, for example) select the text box.

Select Text Box on the Format menu, and then select the options you want on the available tabs.

Appendix B: JAWS keystrokes for Excel

DESCRIPTION

KEYSTROKE

Informational Keystrokes for Columns

List cells in current column

CTRL+SHIFT+C

Read column total

INSERT+NUM PAD ENTER

Say column title

ALT+SHIFT+C

Say FIRST cell in current column

ALT + 1

ALT + 2 Say SECOND cell in current column

ALT + 3 Say THIRD cell in current column

ALT + 4 Say FOURTH cell in current column

Set column titles to row range

ALT+CTRL+SHIFT+C

Set current column to the column containing row totals

CTRL+INSERT+ENTER

Informational Keystrokes for Rows

List cells in current row

CTRL+SHIFT+R

Read row total

INSERT+DELETE

Say row title

ALT+SHIFT+R

Say FIRST cell in current row

ALT + CONTROL + 1

ALT + CONTROL + 2 SECOND cell

ALT + CONTROL + 3 THIRD cell

ALT + CONTROL + 4 FOURTH cell

Set row titles to column

ALT+CTRL+SHIFT+R

Set current row to the row containing column totals

CTRL+INSERT+DELETE

Miscellaneous Informational Keystrokes

Describe cell border

ALT+SHIFT+B

Lists cells at page break

CTRL+SHIFT+B

List cells with comments

CTRL+SHIFT+' (APOSTROPHE)

Read cell comment

ALT+SHIFT+' (APOSTROPHE)

List cells with data visible on screen

CTRL+SHIFT+D

List defined monitor cells

CTRL+SHIFT+M

List worksheets

CTRL+SHIFT+S

Say range of cells visible in active window

ALT+SHIFT+V

Read hyperlink

ALT+SHIFT+H

Report gridline status

ALT+SHIFT+G

Say active cell coordinates

INSERT+C

Say excel version (Excel 95 only)

CTRL+SHIFT+V

Say contents of focus rectangle

INSERT+N

Say cell font and attributes

INSERT+F

Say formula

CTRL+F2

Read spelling error and suggestion

INSERT+F7

Navigation Keystrokes

Move to prior screen in spreadsheet

ALT+PAGE UP

Move to next screen in spreadsheet

ALT+PAGE DOWN

Next Sheet

CTRL+PAGE DOWN

Prior Sheet

CTRL+PAGE UP

Move down to the edge of current data region

CTRL+DOWN ARROW

Move up to the edge of current data region

CTRL+UP ARROW

Move left to the edge of current data region

CTRL+LEFT ARROW

Move right to the edge of current data region

CTRL+RIGHT ARROW

Selection Keystrokes

Select column

CTRL+SPACEBAR

Select hyperlink

CTRL+SHIFT+H

Select region

CTRL+SHIFT+8

Select row

SHIFT+SPACEBAR

Select worksheet objects

CTRL+SHIFT+O

Collapse selection to current cell

SHIFT+BACKSPACE

top of page

Configuration Keystrokes

Set monitor cell

ALT+CTRL+SHIFT+1 THROUGH 0

Read monitor cell

ALT+SHIFT+1 THROUGH 0

Formula input mode

= (EQUALS)

AutoFilter

CTRL+SHIFT+A

AutoSum

ALT+= (EQUALS)

Date stamp

CTRL+; (SEMICOLON)

Time stamp

CTRL+SHIFT+; (SEMICOLON)

Close Office Assistant

CTRL+INSERT+F4

ALT+SHIFT+V

Read hyperlink

ALT+SHIFT+H

CTRL+SHIFT+O

Source: N.A.B., New Delhi

You are here

Excel in MS Excel (advanced tutorial)

Facebook comments