Ms Excel Manual New

Table of content Title U

U

U

page U

Chapter 1 3 Introduction to Excel ............................................................................................................................3 1.1 What is Ms-Excel? .........................................................................................................................3 1.2 What is a Workbook? .....................................................................................................................3 1.3 What is a Worksheet? .....................................................................................................................3 Starting Microsoft Excel ......................................................................................................................3 1.4 Creating a New workbook ..............................................................................................................4 1.4.1 Working with Workbooks ...................................................................................................5 1.5 Saving a Workbook ......................................................................................................................5 1.6 Opening Existing workbook .........................................................................................................5 1.7 Managing Worksheets. .................................................................................................................5 1.8 Moving and Copying a worksheet among Workbooks ..................................................................6 1.9 Splitting Worksheets ....................................................................................................................6 Chapter 2 7 Entering Data & Performing Calculations ...........................................................................................7 2.1 Performing Calculations with Formula ..........................................................................................7 Order of Operations ..............................................................................................................................7 2.1.1 Calculate Resulting Without Entering a Formula ...............................................................7 2.1.2 Working with Absolute Reference ......................................................................................7 2.2 What are Functions? .......................................................................................................................8 2.2.1 Using the Function Wizard ..................................................................................................8 2.2.2 Copying Formula .................................................................................................................8 2.3 Editing a Worksheet .......................................................................................................................9 2.3.1 Moving Within a Worksheet ...............................................................................................9 Selecting Cells ..............................................................................................................................9 2.3.2 Copying Data.......................................................................................................................9 2.3.3 Using Drag-and-Drop ..........................................................................................................9 2.3.4 Moving Data ......................................................................................................................10 2.3.5 Deleting Data ....................................................................................................................10 2.4 Inserting and Removing Cells, Rows, and Column .....................................................................10 2.4.1 Merging Cells ....................................................................................................................10 2.4.2 Inserting Rows and Columns.............................................................................................11 2.4.3 Removing Rows and Columns ..........................................................................................11 2.5 Formatting Worksheets ................................................................................................................11 2.5.1 Formatting Font characteristics........................................................................................11 2.5.2 Formatting Number ...........................................................................................................12 2.6 Adding Borders and Shading .......................................................................................................12 2.6.1 Adding Border to cells.......................................................................................................12 To add border to a cell or range: ................................................................................................12 2.6.2 Adding Shading to Cells ....................................................................................................12 2.6.3 Aligning Text in cells ........................................................................................................13 2.7 Using AutoFormat ........................................................................................................................13 2.8 Changing Column Width and Row Height ..................................................................................13 2.9 Freezing Section of Worksheet ....................................................................................................13 To freeze both columns and/ or row titles: .................................................................................14 To unfreeze titles: .......................................................................................................................14 2.10 Hiding Workbooks, Worksheets, Columns, and Rows ..............................................................14 UT

TU

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

TU

UT

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

TU

UT

UT

TU

UT

TU

UT

TU

UT

TU

TU

UT

UT

1

Chapter 3 14 Chart ...................................................................................................................................................14 3.1 Chart Types ................................................................................................................................14 3.2 Charting Terminology ..................................................................................................................15 3.3 Creating a chart ............................................................................................................................15 3.4Formatting chart ............................................................................................................................16 3.5 Printing a Chart ............................................................................................................................17 Chapter 4 ............................................................................................................................................17 Using a List to Organize Data ............................................................................................................17 4.1 Filtering ........................................................................................................................................17 4.1.1 Using Auto Filter ...............................................................................................................17 4.2.2 Advanced Filtering ............................................................................................................18 4.3 Sorting Data ..................................................................................................................................18 Chapter 5 19 Data summarization ............................................................................................................................19 5.1 Calculating Subtotal .....................................................................................................................19 5.2 Pivot table .....................................................................................................................................19 5.2.1 Lay out the report on Screen .............................................................................................19 5.2.2 Lay out report in the wizard ..............................................................................................20 Chapter 6 20 Printing ...............................................................................................................................................20 Printing ...............................................................................................................................................20 6.1 Changing the Page Setup ..............................................................................................................20 6.2 Page Tab .......................................................................................................................................21 6.3 Header /footer tab .........................................................................................................................21 Sheet tab .....................................................................................................................................21 6.4 Previewing a print Job ..................................................................................................................22 6.5 Printing your workbook................................................................................................................22 6.5.1 Selecting a Print Area ........................................................................................................22 6.5.2 Adjusting Page Breaks ......................................................................................................22 6.5.3 Printing Column and Row Headings .................................................................................23 6.6 Adding Header and Footers ..........................................................................................................23 6.7 Scaling a Worksheet to Fit on a Page ...........................................................................................23 Print a worksheet to fit a paper width or a number of pages ......................................................24 TU

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

TU

UT

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

TU

UT

2

Chapter 1 Introduction to Excel 1.1 What is Ms-Excel? Microsoft Excel is a spreadsheet and data analysis program in Microsoft Office. • A basic spreadsheet is comprised of a table of values, some of which are calculated by formula and functions. • You can use Excel as a database tool to build and organize your database so that it is easy to access and manipulate. • You can also use Excel as data analyzing tool to find answers to “what if” type questions, compare results of different scenarios, or find the best solution to complex problems. The spreadsheet file contains the workbook and the worksheets.

1.2 What is a Workbook? A Workbook is a Microsoft Excel file. Each workbook can contain up to 255 worksheets .The sheet tabs at the bottom of the worksheet (labeled sheet 1, sheet 2, and so on) let you flip through the worksheets by clicking them with the mouse.

1.3 What is a Worksheet? A worksheet is a separate page in a workbook which is a working area and each worksheet contains a grid consisting of alphabetized columns and numbered rows. Where a row and a column intersect, they form a box called a cell. Each cell has an address that consists of the column letter and row number (as A1, B2, C4, and so on). You enter data and formula in the cells. We can use a worksheet to: • Store, manipulate, calculate, attach charts and analyze data such as numbers, text and formula • Graphic elements such as lines, rectangles, text boxes and buttons to your worksheet.

Starting Microsoft Excel T

T

1. 2. 3. 4.

Click on the start button Point to Programs, Choose Microsoft Excel from the programs sub menu, Microsoft Excel opens

3

1.3 Elements of Microsoft Excel Window T

T

Title bar Cell Tool bars Name Box

Menu Bar

Column Heading

Cell Pointer Row Heading

Standard Toolbar Formula Bar Formatting Toolbar

Sheet tab Navigating Button Status Bar

Sheet Tabs

Exiting Excel

To exit Excel, perform the following options: • Click the Close (x) button in the Excel main window. • Open the File menu and select Exit. or • Press Alt+F4

1.4 Creating a New workbook When you start Microsoft Excel, a new workbook opens. To begin working on a workbook, just start typing your data. However, if you want to create a new workbook at any time, you can use the Standard toolbar or File menu.

4

1.4.1 Working with Workbooks A workbook can contain many sheets, so you can organize various kinds of related information in a single file. A workbook can have different types of sheets, such as worksheets, chart sheets and others.

1.5 Saving a Workbook In order to keep excel files in your computer; you need to save your workbook after or while you are working. You should also save periodically while you work so that you don't lose your work in the event of a power interruption or hardware problem. To save a new Workbook or existing workbook as a new workbook file: 1. 2. 3. 4.

Form the File menu, choose Save as. The save as dialog box appears. choose the folder and/or drive where you want to save your workbook If you want to save the document under a name different from the one proposed, type the new name for the workbook. Click the save button.

To save an Existing Workbook Quickly under the same Name and location: Choose the Save command from the File menu (click the save button from the standard toolbar). Note:- By default, Excel will save your spreadsheet file with the .xls extension.

To close a workbook:

Select File, Close (or click the Close button of the workbook window), if the workbook is not saved, Excel informs you to do so.

1.6 Opening Existing workbook To open an Existing workbook

1. 2. 3. 4.

Choose Open from the File menu ( or Click the Open button from the standard toolbar ). Select the drive and / or the directory where the workbook is stored, Select the file you want to open

Choose the Open button. Note: - You can open a workbook you recently worked on by choosing it from the list at the bottom of the file menu.

1.7 Managing Worksheets. A workbook is like a ledger pad containing multiple worksheets. One can use only a single worksheet, but some times it is convenient to place several related worksheets together in a workbook. In this way it is easy to flip through your data, and you can perform calculations using numbers from any sheet. If your workbook includes many sheet tabs and you can't see the tab for the sheet you want to move to, click the tab scrolling buttons to the left of the work sheet tabs. The plain arrows scroll one tab at a time in the direction of the arrow. The arrows with lines scroll to the last or the first tab in the workbook. By default, new workbook opens with 3 sheets named sheet1 through sheets3.

5

You can do any of the following to arrange your workbook the way you want.

Selecting worksheets: • • •

To select a single worksheet, click its tab. The tab becomes highlighted to show that the worksheet is selected. To select several neighboring worksheets, click the tab of the first worksheet in the group, and then hold down the Shift key and click the tab of the last worksheet in the group. To select several non-neighboring worksheets, hold down the Ctrl key and click each worksheet's tab.

To insert a new worksheet: 1. Select a worksheet before which you want to insert the new worksheet. 2. From the Insert menu, select Worksheet. To delete a worksheet: 1. Select the worksheet (s) you want to delete. 2. From Edit menu, choose Delete sheet. 3. Click Ok to confirm the deletion.

To rename a sheet: 1. Double click the tab of the worksheet you want to rename. 2. Type a descriptive name of up to 31 characters. 3. Press Enter.

1.8 Moving and Copying a worksheet among Workbooks To move a sheet to another workbook: 1. Select the worksheet you want to move. 2. Choose Move or Copy Sheet from the Edit menu. 3. Select the destination workbook and where you want sheet placed. To copy a sheet to another workbook 1. Select the sheet you want to copy. 2. Choose Move or Copy sheet from the Edit menu. 3. Select the destination workbook where you want to put the copy sheet, then select the Create a Copy Check box. 4. Select the Ok button. To arrange the windows for the active workbook: 1. Open the Window menu, and then select Arrange. The arrange windows dialog box appears. 2. Choose one of the arrangements. Tiled Horizontal, Vertical, and Cascade. 3. Select Windows of active workbook check box. 4. Select the Ok button.

1.9 Splitting Worksheets To split a worksheet to view two parts at one time: 1. Click either the vertical or the Horizontal Split bar. 2. Drag the split bar in to the worksheet window. 3. Drop the split bar, and Excel splits the window at that location. To remove the split, drag it back to its original position on the scroll bar.

6

Chapter 2 Entering Data & Performing Calculations Entering Data The different types of data that you can enter to a worksheet are :• Text • Numbers • Date/Time • Formula • Functions

2.1 Performing Calculations with Formula T

T

Ms-Excel uses formula to perform calculations on the data you entered in the worksheet. With formula, you can perform addition, subtraction, multiplication and division by using the values contained in various cells. For example , if you want to determine the average of the three value contained in cells A1, B1 ,and C1.type the following formula in the cell where you want the result to appear as: =(A1+B1+C1)/3 Note: - Every formula must begin with an equal sign (=)

Order of Operations T

T

Excel performs within a formula in the following order. Equations within parentheses. 1st Exponentiation. 2nd Multiplication and division 3rd Addition and subtraction. 4th P

P

P

P

P

P

P

P

Note: - A cell containing a formula normally displays the formula's resulting value on the worksheet, when you select a cell containing a formula; the formula is always displayed in the formula bar.

2.1.1 Calculate Resulting Without Entering a Formula You can view the sum of a range of cells simply by selecting the cells and looking at the status bar. You can also preview the average, minimum maximum and the count of ranges of cells. To do so, right-clicking on the right side of the status bar and select the option you want from the shortcut menu that appears.

2.1.2 Working with Absolute Reference When you use the fill handle to create additional formula in the previous section, the cell references changed automatically relative to the new location. For this reason, such reference are called Relative Reference. But some times you may not want cell reference to change when copying the formula. References that don't change when copied are called Absolute Reference. Absolute cell reference must have a dollar sign preceding both the column letter and the row number. You can also create mixed absolute cell reference, which have a dollar sign preceding the column letter but not the row number or vice versa. To create absolute cell references: 1. Active the cell in which you want to write the formula. 2. Enter a standard formula (without any absolute reference). 3. Press the F4 function key until you get the type of reference you want Now, when you copy the formula that contains absolute reference, the row, and /or the column, which is absolutely referenced will not be changed relatively.

7

2.2 What are Functions? Functions are complex ready-made formula that performs a series of operations on specified range of values, For example, to determine the sum of a series of numbers in cells A1 to H1, you can enter the function =SUM (A1:H1) instead of entering =A1+B1+C1+…+H1 and so on. Function can use range reference (such as B1:B3), range names (such as SALES). or numerical values (such as 585, 86). Every • • •

function consists of the following three elements The = sign indicates that what follows in a function ( formula) The function name, such as SUM, indicates which operation will be performed. The argument, such as (A1:H1), indicates the cell address of the value on which the function will act. The argument is often a range of cells, but it can be much more complex.

You can enter function either by typing them in cells or by using the function wizard, the following table shows Excel's most common function that you'll use in your worksheets.

2.2.1 Using the Function Wizard Although you can type function directly in to a cell just as you can type formula; you may find it easier to use the function Wizard leads you formula the process of inserting a function. The following steps walk you through using the Function Wizard: 1. 2. 3. 4.

Select the cell in which you want to insert the function Type = sign or click the Edit Formula button on the Formula bar. The formula palette appears. Select the function you want to insert from the Functions list by clicking the Function name. If you don't see your function listed, select All from the function category box. 5. Enter the arguments for the formula. If you want to select a range of cells as an argument, click the Collapse Dialog button again to select a range of cells as an argument 6. Click Ok. Excel inserts the function and argued to select cell and displays the result. Note: - To edit a function, click the Edit Formula button. The formula palette appears. Change the arguments as needed and click OK

2.2.2 Copying Formula T

T

If you want to create similar formula such as =B8*C10, B9*C11, B10*C12 and so on it is possible to use the fill handle button of the cell to copy the formula to other cells after creating once. Although you could enter all formula separately, it's much easier to use Excels' fill handle to create a sequence of formula that are identical except for cell references. When you copy formula using the fill handle, Excel adjusts cell references accordingly. You can also copy formula down a column or across a column , the fill handle is probably the fastest method. But if you are copying a formula to a location else where in the worksheet, the copy and paste technique is preferable. To copy a formula: 1. Activate the cell that contains the formula, if it's not active. 2. Point to the fill handle of the cell. The cell pointer gets a new shape - a small Plus sign. 3. Drag down or to the right to select the cells to be filled with identical formula. except for cell references. 4. Release the mouse button.

8

2.3 Editing a Worksheet T

T

After creating any worksheet, it is possible to modify (edit) the worksheet contents to copy, move, delete, formula and values that you have entered in your worksheet.

2.3.1 Moving Within a Worksheet After the worksheet you want to work on is displayed, you'll need some way of moving to the various cells within the worksheet. Keep in mind that the part of the worksheet displayed onscreen is only a small part of the actual worksheet. To move around the worksheet with your keyboard, use the tab, arrow and Enter keys.

Selecting Cells To edit and format worksheet content you must select those cells first. Then you can perform the appropriate action. • To select a single cell, click it • To select adjacent cells (a range), click the upper-left cell in the group and drag down to the lower-right cell to select additional cells. • To select non-adjacent cells, press and hold the Crtl Key as you click individuals’ cells. • To select an entire row or column of cells, click the row or column header. To select adjacent rows or columns, drag over their headers. To select nonadjacent rows or columns, press Crtl and click each header that you want to select.

Editing a Cell

After you have entered data into a cell, you may edit it in either the Formula bar or in the cell itself. To edit an entry of a cell: 1. Click the cell in which you want to edit data. 2. To begin editing, click on the formula bar or press F2 or double click the cell. 3. Click the Enter button on the formula bar or press Enter on the keyboard to accept your changes or to reject the modification press the Esc key or click on the cancel button.

2.3.2 Copying Data T

T

When you copy or move data, a copy of that data is placed in a temporary storage area called the Clipboard. You can copy data to other sections with in the worksheet or to other worksheets or workbooks. When you copy, the original data remains in its place and a copy of it is placed on new location. To copy data: 1. Select the cell (s) that you want to copy. 2. Click the Copy button on the standard toolbar. The contents of the selected cells (s) are copied to the Clipboard. 3. Select the first cell in the destination area. ( To copy the data to another worksheet or workbook, change to that worksheets or workbook first.) 4. Click the paste button, Excel inserts the contents of the clipboard in to the new location.

2.3.3 Using Drag-and-Drop The fastest ways to copy worksheet content is simply hold down Ctrl key and drag the cell(s) which contains the data and drops to the required location. If you forget to hold down the Crtl key, Excel moves the data instead of copying it. To drag a copy to a different sheet, press Ctrl+Alt as you drag the selection to the sheet's tab. Excel switches you to that sheet, where you can drop your selection in the appropriate location.

9

2.3.4 Moving Data T

T

Moving data is similar to copying except that the data is removed from its original place and placed in the new location. To move data: 1. Select the cells (s) you want to move. 2. Click the Cut button. 3. Select the first cell in the destination area. To move the data to another worksheet, change to that worksheet. 4. Click Paste.

2.3.5 Deleting Data T

T

To delete the data in a cell, you can just select the cell and press Delete. However, Excel offers additional options for deleting cells: • With the Edit->Clear command, you can choose to delete just the formatting of a cell (or an attached comment) instead of deleting its content. The formatting of cell including the cell's color, border style, numeric format, font size, and so on. • With the Edit, Delete command, you remove cells and everything in them.

2.4 Inserting and Removing Cells, Rows, and Column Inserting cells Sometimes, you will need to insert information into a worksheet, right in the middle of existing data. With the Insert command, you can insert one or more cells, or enter rows or columns. To insert a single cell or a group of cells: 1. Select the cell (s) before which you want the new cell (s) inserted. Excel will insert the same number of cells as you select. 2. Open the insert menu and choose Cells. The insert dialog box shown below appears. 3. Select Shift Cells Right or Shift Cells Down. 4. Click OK. Excel inserts the cell (s) and shifts the data in the other cells in the specified direction. Note: - A quick way to insert cells is to hold down the Shift key and then drag the fill handle (the little box in the lower-right corner of the selected cell or cells). Drag the fill handle up, down, left, or right to set the position of the new cells.

2.4.1 Merging Cells You can merge-in one cell with other cells to form a big cell that is easier to use. Merging cells especially handy when creating a decorative title at the top of your worksheet. Within a single merged cell, you can quickly change the font, Font size, color, and border style of your title. To create merged cells: 1. Select the range in which you want to merge. 2. Open the Format menu and select Cells. The format cells dialog box appears. 3. Click the Alignment tab. 4. Click Merge cells. You may also make adjustment to the text within the merged cells. For Example, you may want to select center in the Vertical drop-down list to center the text vertically within the cell. You can merge selected cells and center the data by clicking the Merge and Center button on the Formatting toolbar. Removing Cells If you want to remove the cells completely: 1. Select the range of cells you want to remove.

10

2. Open the edit menu and choose Delete. The Delete dialog box appears. 3. Select the desired Delete option: Shift Cells Left or Shift Up. 4. Click OK.

2.4.2 Inserting Rows and Columns 1. To insert a single row or columns, select the cell to the left of which you want to insert a column or which where you want to insert a row. To insert multiple columns or rows, select the number of columns or rows you want to insert. To insert columns, drag over the column letters at the top of the worksheet. To insert rows, drag over the row numbers. For example, select three columns letters or row numbers to insert three columns or rows. 2. Open the Insert menu select Rows or Columns. Excel inserting the row (s) or column (s) and shift the adjacent rows down or the adjacent columns to the right. The inserted rows or columns contain the same formatting as the cells you selected in step 1. Note:- To quickly insert rows or columns, select one or more rows columns. Then right-click one of them and choose Insert from the shortcut menu.

2.4.3 Removing Rows and Columns Deleting rows and columns is similar to deleting cells. When you delete rows, the rows below the deleted row move up to fill the space. When you delete a column, the columns to the right shifts left. To delete row or column: 1. Click the row number or column letter of the row or column you want to delete. You can select more than one row or column by dragging over the row number or column letters. 2. Open the Edit menu and choose Delete. Excel deletes the row (s) or column (s) and renumber rows and columns sequentially. All cell references in formula will be updated appropriately, unless they are absolute ($) values.

2.5 Formatting Worksheets T

T

You can use the formatting options of Microsoft Excel to add emphasis to your data or to make your worksheet easier to read and visually more appealing. For instance, you can change the appearance of data in your worksheet by changing the font type, font style, and color of data in cells. You can format numbers to designate dollar amounts, percent ages, decimals, scientific notation, dates, or times.

2.5.1 Formatting Font characteristics T

T

To change the font of cells content 1. Select the cell or range of cells you want to format. 2. Open the Format menu and choose Cells, or press Ctrl + 1. (You can also right-click the selected cells and choose Format Cells from the shortcut menu). 3. Click the Font tab. The font options will be displayed. 4. Select the options you want. 5. Click Ok or press Enter. Note:- A faster way to enter font changes is to use the Formatting toolbar.

11

2.5.2 Formatting Number T

T

Numeric Values are usually more than just numbers. They represent a dollar value, a date a percent, or some other value. To apply number formatting 1. select the cell or range of cells that contains the values you want to format 2. Open the Format menu and choose Cells. The format cells dialog box appears. 3. Click the Number tab. 4. In the Category list, select the numeric format category you want to use. 5. Make changes to the format as needed. 6. Click Ok or press Enter. Excel reformats the selected cells based on your selections.

2.6 Adding Borders and Shading 2.6.1 Adding Border to cells As you work with your worksheet on-screen, you'll notice that each cell is identified by gridlines that surround the cell. Normally, these gridlines do not appear in the printout; and even if you choose to print them, they may appear washed out. To have more well-defined lines appear on the print out (or on-screen, for that matter), you can add borders to select cells or entire cell ranges. A border can appear on all four sides of a cell or only on selected sides which ever you prefer. Note: - the gridline do not print by default, but if you want to try printing your worksheet with gridlines first just to see what it looks like, open the File menu, select Page Setup. Click the Sheet tab, select Gridlines, and click OK

To add border to a cell or range: 1. 2. 3. 4. 5.

Select the cell (s) around which you want a border to appear. Open the format menu and choose Cells. The format cells dialog box appears. Click the Border tab Select the desired position, style (thickness), and color for the border. Click OK or press Enter.

Note: - To add borders quickly, select the cells around which you want the border appear, and then click the borders drop down arrow in the formatting toolbar. Click the desired Border. If you click the Border button itself (instead of the arrow), Excel automatically adds the Borderline you chose recently to the selected cells.

2.6.2 Adding Shading to Cells To add shading to a cell or range 1. Selected the cell(S) you want to shade. 2. Open the Format menu and choose Cells. 3. Click the Patterns tab. Excel displays the shading options. 4. Click the Pattern drop-down arrow, and you will see a grid that contains all the colors from the color palette, as will as patterns. Select the shading color and pattern you want to use. 5. Click OK or press Enter. Note:- A quick ways to add cell shading (without a pattern) is to select the cells you want to shade, click the Fill Color drop-down arrow in the Formatting toolbar, and click the color you want to use.

12

2.6.3 Aligning Text in cells When you enter data into Excel worksheet, that data is aligned automatically. Text is aligned on the left, and numbers are aligned on the right. Both text and numbers are initially set at the bottom of the cells. How ever, you van change both the vertical and horizontal alignment of data in your cells. To change the alignment: 1. Select the cell or range of cells, select the entire range of blank cells in which you want the text centered, including the cell that contains the text you want to center. 2. Pull down the Format menu and select Cells, or press Ctrl+1 . The format cells dialog box appears. 3. Click the Alignment tab. The alignment options appear in front. 4. choose the desired option 5. Click OK.

2.7 Using AutoFormat Excel offers the AutoFormat feature, which takes provides you with predefined table formats that you can apply to a worksheet.

To use AutoFormat features:

1. Select the Worksheet(S) and cell(S) that contain the data you want to format. 2. Open the Format menu and choose AutoFormat. 3. In the Table Format list, choose the predefined format you want to use. When you select a format, Excel shows you what it will looks like in the Sample area. 4. To exclude certain elements from AutoFormat, click the Options button and choose the formats you want to avoid. 5. Click OK.

2.8 Changing Column Width and Row Height You can adjust the width of a column or the height of a row by using a dialog box or by dragging with the mouse. You might not want to bother adjusting the row height because it's automatically adjusted as you change font size. However, if a column's width is not as large as its data, that data may not be displayed and may appear as #######. In such case, you must adjust the width of the column in order for the data to be displayed fully.

To adjust the row height or column width with the mouse: 1. To change the height or width of rows or columns, select them by dragging over the row or column headings. 2. Position the mouse pointer over one of the row heading or column heading to adjust column width; (Use the bottom border of the row heading to adjust column width; use the bottom border of the row heading to adjust the row height.) 3. Drag the border to the size you need it. 4. Release the mouse button, and Excel adjusts the height or column width.

2.9 Freezing Section of Worksheet By freezing worksheet titles, you can display designated rows and/ or columns on the screen at all times. This is useful for scrolling through a long worksheet and keeping track of titles. Frozen panes don't affect print out. That is, even if you frozen column and row headings, those headings won't necessarily appear on each page of your printed spreadsheet.

13

To freeze both columns and/ or row titles: 1. Select the cell below the title row and / or to the left of the title column. 2. From the Window menu, choose Freeze panes.

To unfreeze titles: 1. Select the window you want to unfreeze. 2. From the Window menu, choose Unfreeze panes. Note: - When a worksheet window, that has frozen title is active, the freeze panes command changes to the Unfreeze panes command.

2.10 Hiding Workbooks, Worksheets, Columns, and Rows To hide • A workbook, open the Windows menu and select Hide. • A sheet, click its tab to select it. Then open the Format menu, select sheet, and select Hide. • Rows or columns, click a row or column heading to select it. Then open Format menu, select Row or column, and select Hide. To redisplay hidden data Select the hidden area first. For example, select the rows, columns, or sheets adjacent to the hidden ones. Then Unhide form the sheet command of the format menu.

Chapter 3 Chart Chart T

T

A chart is visual representation of worksheet data. Microsoft Excel creates several chart types, such as Pie, bar, and line charts. When you create a chart, values from worksheet cells (or data points) are displayed as bars, lines, columns ,pie slides or other shapes in the chart.

3.1 Chart Types With Excel, you can create various types of charts. The chart type you choose depends on your data and on how you want to present that data. These are the major chart types and their purpose:Pie: -Use this chart to show the relationship among parts of whole. Bar: -Use this chart to compare clues at a given point in time. Column: - Similar to the Bar chart, use this chart to emphasize the difference between items. Line: -Use this chart to emphasize trends and the change of values over time. Scatter: -Similar to the line chart; use this chart to emphasize the difference between two sets of values.

14

Area: -Similar to the line chart; use this chart to emphasize the amount of changes in values over time. Embedded charts: -A chart that is placed on the same worksheet that contains the data used to create the chart. A chart can also be placed on a chart sheet in the worksheet so that the worksheet and chart are separate, which is called Independent Chart. Embedded charts are useful for showing the actual data and its graphic representation side by side. Microsoft Excel offers different types of charts to choose from two-dimensional chart types and three-dimensional chart types. You can select a number of built-in formats for each chart type, or you can add your own custom formatting to create exactly the kind of chart you need.

3.2 Charting Terminology Before you start creating charts, familiarize yourself with the following terminology Data Series: The bars, pie wedges, lines, or other elements that represent plotted values in a chart. For Example, a chart might show a set of similar bars that reflects a series of values for the same item. The bars in the series would all have the same pattern. If you have more than one pattern of bars, each pattern would represent a separate data series. For instance, charting the sales for Territory1 versus Territory2 would require two data series one for each territory. Often, data series correspond to rows of data in your worksheet. Categories: Categories reflect the number of elements in a series. You might have two data series to compare the sales of two different territories and four categories to compare these sales over four quarters. Some charts have only one category, and others have several, Categories normally correspond to the columns that you have in your chart data, and the category labels coming from the column headings. Axis: One side of a chart. A two-dimensional chart has an x-axis (horizontal) and y-axis (vertical). The x-axis contains all the data series and categories in the chart. If you have more than one category, the x-axis often contains labels that define what each category represents. The y-axis reflects the values of the bars, lines, or plot points. In a three dimensional chart, the z-axis represents the vertical plane, and the x-axis (distance) and y-axis (width) represents the two sides on the floor of the chart. Legend: Defines the separate series of a chart. For example, the legend for a pie chart will show what each piece of the pie represents. Gridlines: Emphasize the y-axis or x-axis scale of the data series. For example, major gridlines for the y-axis will help you follow a point from the x-axis to identify a data point's exact value.

3.3 Creating a chart To use the chart Wizard, Follow these steps: 1. Select the data you want create chart. If you typed names or other labels (such as Qtr 1, 2 and so on) and you want them included in the chart, makes sure you select them. 2. Click the Chart Wizard button on the standard toolbar. 3. The chart wizard step 1 of 4 dialog box appears. Select a Chart Type and Click Next. 4. Next you're asked if the selected range is correct. You can correct the range by typing a new range or by clicking the Collapse Dialog button (located at the right end of the data Range text box) and select the range you want to use. 5. By default, Excel assumes that your different data series are stored in rows. You can change this to columns if necessary by clicking the Series in columns option .When you're through, click Next. 6. Click the various tabs to change options for your chart. For example, you can delete the legend by clicking the Legend tab and deselecting Show Legend. You can add a chart title on the title tab. Add data labels (labels which display the actual value being

15

represented by each bar, line, and so on) by clicking the Data Labels tab When you finish making changes, click Next. 7. Finally, you're asked if you want to embed the chart (as an object) in the current worksheet, or if you want to create a new worksheet for it. Make your selection and click the Finish button. Your completed chart appears.

Formatting chart You can specify a fill effect, change the line width or border style, or change colors for the chart area, the plot area, data markers gridlines, axes tick marks, and error bars walls and floor in 3-D charts. T

T

1. On a chart sheet or in an embedded chart. double-click the chart element that you want to change. 2. On the Patterns tab, do any of the following: o To specify a fill effect, click Fill Effects, and then select the options that you want on the Gradient, Texture, or Pattern tabs. T

T

T

T

Tip You can also fill a chart element with a picture. On the Picture tab, click Select Picture, double-click the picture that you want, and then click Insert. To clear a fill effect or picture and return the chart element to the default formatting, click Automatic under Area. o

To change border and line styles, select the options that you want under Border. To clear all border formatting, click None. Note: Different chart elements have different options available under Border. For the chart area, for example, there are Shadow and Round corners check boxes that you can select.

o

To change colors, select the color that you want under Area. To clear all color formatting, click None.

Note: Formatting applied to an axis is also applied to the tick marks on that axis. Gridlines are formatted independently of axes. Moving and Resizing A Chart: To move an embedded chart, click anywhere in the chart area and drag it to the new location. To change the size of a chart, select the chart, and then drag one of its handles (the black squares that border the chart). Drag a corner handle to change the height and width, or drag a side handle to change only the width. (Note that you can't really resize a chart that is on a chart sheet by itself.) Customizing Your Chart with the Chart Toolbar You can use the Chart toolbar to change how your chart looks. If chart toolbar is not displayed, you can turn it on by opening the View menu, selecting Toolbars, and then selecting Chart.

16

3.5 Printing a Chart If a chart is an embedded chart, click it to select it and then open the chart. If you want to print just the embedded chart, click it to select it and then open the file menu and select print. Make sure that the selected Chart option button is selected. Then click to print the chart. If you created a chart on a separate worksheet. You can print the chart separately by printing only that worksheet.

Chapter 4 Using a List to Organize Data The data in an Excel worksheet is often referred to as a list. A list is simply a series of rows that contain similar data and that are topped by a row of identifying labels .The advantage of lists is that you can manipulate them to suit your needs. You can search for data that meets specific conditions; filter out other data that you don't need to see at the moment. You can also sort the list in variety of ways. List is often referred to as databases. In addition, A record is all the data about one subjectall personal data. A filed is a data category. For Example, the fields in personnel list might include Name, Father Name, Id No, Department, Salary and Emp Date. The column heading are often is called field names. It's best not to have more than one list per worksheet. You should also leave at least one blank row and column between the list and any other data in your worksheet so Excel can identify the list automatically.

4.1 Filtering Often you'll want to see all the records in your worksheet. But sometimes you'll want to see only selected portions of your data and the bigger your worksheet grows; the more important this becomes. Using the AutoFilter command of the data menu, Excel lets you temporarily filter your data, searching for and displaying only those records that meet certain conditions. Once you filter your list, you can view, edit, copy or print the remaining records, just as you can with any other data in Excel.

4.1.1 Using Auto Filter 1. Activate any cell in the list 2. Select Data, Filter and then choose AutoFilter from the submenu that appears. Each field now has a drop-down arrow associated with it. These arrows let you filter the list by the values in particular fields 3. Click on the drop-down arrow of the field by which you to filter 4. Choose Custom. The custom dialog box will be displayed 5. In the upper-right text box, Type the number or text that will be used to filter the list

17

6. In the upper-left text box, choose an operator to specify how the records should be tested against the number or the text 7. Click OK to filer the list. Using the second set of drop-down list boxes of the custom Auto filter dialog box you can filter records that meet two criteria, instead of one. Note: - it is possible to filter fields that contain only text using operators. Since Excel considers A to be less than the letter B, and so on, you can enter Abebe and choose "greater than operator" to find names that fall after the name Abebe. In addition, you can use the wild card character (*) to search all records whose name you can't remember in full. For instance, you can enter M* and choose "equal operator" to find names begin by the letter M.

4.2.2 Advanced Filtering T

T

To filter a list by using advanced filter 1. Copy the column labels from the columns that contain the values you want to filter. (The list must have column labels.) 2. Paste the column labels in the first blank row of the criteria range. Make sure there is at least one blank row between the criteria values and the list. 3. In the rows below the criteria labels, type the criteria you want to match. 4. Click a cell in the list. 5. On the Data menu, point to Filter and then click Advanced Filter. 6. To filter the list by hiding rows that don't match your criteria, click Filter the list in place. To filter the list by copying rows that matches your criteria to another area of the worksheet, click Copy to another location option, click in the Copy to box, and then click the upper-left corner of the paste area. 7. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. To move the Advanced filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog button. Note: - If the worksheet contains a range named Criteria, the reference for the range will appears automatically in the Criteria range box.

4.3 Sorting Data Another way to change the way you view your data is to sort it, rearranging the records in your list based on the contents of one or more fields. You need to sort by more than one fields when you need to break ties that appear in the field by which the list is sorted. To sort a list: 1. Activate any cell in the list. 2. Select Data Sort. The sort dialog box will be displayed. 3. Under sort By, click on the drop-down arrow. A list of fields will be displayed. 4. Select the filed by which you want to sort. 5. Select the order of the sort: click the Ascending or Descending option buttons. 6. To sort on one or two additional fields to break ties in the first field, repeat step 4 and 5 in the first and, if necessary, the second Then By area. 7. Finally click on OK. To quickly sort a list using the field of the active cell, one can use the Sort Ascending or Sort Descending buttons of the standard toolbar.

18

Chapter 5 Data summarization 5.1 Calculating Subtotal Another way to manipulate a list is to summarize the data it contains. Using the Subtotal command of the Data menu, you can calculate subtotals and grand totals upon specific field, determine the average values in a particular set of records, find out the maximum or minimum of values in a particular category, and count the number of items in a designated category. To determine subtotals based upon a particular column: 1. Sort the cell listed on that column. 2. Choose Data, Subtotal. The subtotal dialog box will be displayed 3. Select the field upon which the list sorted in the top drop-down list. Excel calculates subtotal each time the value of this field id changed. 4. Choose a function from the use function list box: Sum, Count, Average, Max, Min and etc 5. Choose the field in which you want to display the subtotal in the Add subtotals in the Add subtotals to: list box: 6. Finally select OK.

5.2 Pivot table A PivotTable report is an interactive table that quickly combines and compares large amounts of data. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest. Create a PivotTable report 1. Open the workbook where you want to create the PivotTable report. o If you are basing the report on a Web query, parameter query, report template, Office Data Connection file, or query file, retrieve the data into the workbook, and then click a cell in the Microsoft Excel list containing the retrieved data. If the retrieved data is from an OLAP database, or the Office Data Connection returns the data as a blank PivotTable report, continue with step 6 below. o If you are basing the report on an Excel list or database, click a cell in the list or database. 2. On the Data menu, click PivotTable and PivotChart Report. 3. In step 1 of the PivotTable and PivotChart Wizard, follow the instructions, and click PivotTable under What kind of report do you want to create? 4. Follow the instructions in step 2 of the wizard. 5. Follow the instructions in step 3 of the wizard, and then decide whether to lay out the report onscreen or in the wizard. Usually you can lay out the report onscreen, and this method is recommended. Use the wizard to lay out the report only if you expect retrieval from a large external data source to be slow, or you need to set page fields to retrieve data one page at a time. If you aren't sure, try laying out the report onscreen. You can return to the wizard if necessary.

5.2.1 Lay out the report on Screen 1. From the PivotTable Field List window, drag the fields with data that you want to display in rows to the drop area labeled Drop Row Fields Here.

19

If you don't see the field list, click within the outlines of the PivotTable drop areas, and make sure Show Field List

is pressed in.

To see what levels of detail are available in fields that have levels, the click the field.

next to

2. Drag fields with data that you want to display across columns to the drop area labeled Drop Column Fields Here. 3. Drag fields that contain the data that you want to summarize to the area labeled Drop Data Items Here. icon can be dragged to this area. Only fields that have the or If you add more than one data field, arrange these fields in the order you want: Right-click a data field, point to Order on the shortcut menu, and use the commands on the Order menu to move the field. Drag fields that you want to use as page fields to the area labeled Drop Page Fields Here. 4. To rearrange fields, drag them from one area to another. To remove a field, drag it out of the PivotTable report.

5.2.2 Lay out report in the wizard If you've exited from the wizard, click PivotTable and PivotChart Report on the Data menu to return to it. 1. In step 3 of the wizard, click Layout. 2. From the group of field buttons on the right, drag the fields that you want onto the ROW and COLUMN areas in the diagram. 3. Drag the fields that contain the data that you want to summarize onto the DATA area. 4. Drag fields that you want to use as page fields onto the PAGE area. 5. If you want Excel to retrieve data one page at a time, so you can work with large amounts of source data, double-click the page field, click Advanced, click Query external data source as you select each page field item, and then click OK twice. (This option is unavailable for some types of source data, including OLAP databases and Office Data Connections.) 6. To rearrange fields, drag them from one area to another. Some fields can only be used in some of the areas; if you drop a field in an area where it can't be used, the field won't appear in the area. 7. To remove a field, drag it out of the diagram. 8. When you are satisfied with the layout, click OK, and then click Finish

Chapter 6 Printing Printing 6.1 Changing the Page Setup You can print the whole workbook at once or just one or more pages at a time.

20

Before you print a worksheet, you should make sure that the page is set up correctly for printing. To do this, open the file menu and choose Page setup. The following list outlines the page setup settings, grouped according to the tab on which they appear.

6.2 Page Tab Orientation: - select Portrait to print across the short edge of a page; select Landscape the print across the long edge of a page. (Landscape makes the page wider than it is tall.) Scaling :-You can reduce and enlarge your workbook or force it to fit within a specific page size. Paper Size:-This is 81/2 by 11 inches by default, but you can choose a different size from the list. Print Quality:- You can print your spreadsheet in draft quality to print quickly and save wear and tear on your printer, or you can print in high quality for a final copy. print quality is measured in dpi (dots per inch); the higher number, the better the print. First P age Number :-You can set the starting page number to something other than 1. The Auto option (default) tells Excel to set the starting page number to be 1 of if it is the first page in the print job, or to set the first page number at the next sequential number if it is not the first page in the print job.

Margins tab Top, Bottom, Left, Right, you can adjust the size of the top, bottom, left, and right, margins. Header, Footer you van set specify how far you want a Header of Footer printed from the edge of the page. (You use the Header / footer tab to add a header or footer to your workbook) Center on a Page you can center your workbook data between the left and right margins (Horizontally) and between the top and bottom margins (Vertically).

6.3 Header /footer tab Header, Footer you can add a header (such as a title) that repeats at the top of each page. Custom Header, Custom footer you can use the Custom Header or Custom footer button to create headers and footers that insert the time, Date, Worksheet tab name, and worksheet filename.

Sheet tab Print Area you can print a portion of a workbook or worksheet by entering the range of cells you want to print. You can type the range, or click the collapse Dialog Box icons at the right of the page setup dialog box out of the way and drag the mouse pointer over the desired cells. If you do not select a print area. excel will print either the sheet or the workbook, depending on the options set in the page tab. Print Titles If you have a row or column of entries that you want repeated as titles on every page, type the range for this row or columns, or drag over the cells with the mouse pointer. Print You can tell Excel exactly how to print some aspects of the workbook. For example, you can have the grade line (the lines that define the cells ) printed. You can also have a color spreadsheet print in blank-and-white. Page Order you can indicate how data in the worksheet should be read and printed: in sections from top to bottom or in sections from left to right. This is the way Excel handles printing the areas out side of the printable area . for example, if some column to the right doesn't first page, and some rows don't fit at the bottom of the first page, you can specify which area will print next. When you finish entering your settings, click OK button.

21

6.4 Previewing a print Job To preview a print job, open the File menu and select Print Preview or click the print preview button in the standard toolbar.

6.5 Printing your workbook After setting the page setup and previewing your data, it is some time to print, You can print selected data, selected sheets, or the entire Workbook. To print your worksheet:

1.

If you want to print a portion of the worksheet, select the range you want to print of you want to print one or more sheets within the Workbook, select the sheet tabs. to print the entire workbook, skip this step. 2. Open the File menu and select Print (or press Ctrl+p). the print dialog box appears. 3. Select the options you would like to use: Page Range lets you print one or more pages. For example, if the selected print area will take up 15 pages and you want to print only page 5-10, select page (s) and type the number of the first and last page you want to print in the Form. Print what enables you want to print the currently selected cells, the selected worksheet, or the entire workbook. Copies enable you to print more than one copy of the selection, worksheet, or workbook. Collate enables you to print a complete copy of the selection, worksheet, or workbook before the first page of the next copy is printed. This option is available when you print multiple copies.

6.5.1 Selecting a Print Area You can tell Excel what part of the worksheet you to print using the print Area Option. To select a print area and print your worksheet at the same time, follow these steps 1. Open the File menu and choose page Setup. 2. Click the sheet tab to display the Sheet options. 3. Click the collapse dialog icon to the right of the print Area text box. Excel reduces the page setup dialog 4. Drag over the cells you want to print. 5. Click the collapse dialog icon to return to the page setup dialog box. 6. Click print in the page setup dialog box to display the print dialog box. Then click ok to print your worksheet.

6.5.2 Adjusting Page Breaks T

T

When you print a workbook, Excel determines the page break based on the paper size and margins and the selected print area To make the pages look better and break things in logical places, you may want to override the page break with your own breaks. How ever, before you add pages breaks, try these options: • Adjust the width of individuals column to make the best use of space • Consider printing the workbook sideways (using Landscape orientation). • Change the left, right, top, and bottom margin to smaller values. If after trying these options you still want to insert page breaks, Excel offers you an option of preview exactly where the page breaks appear and then adjusting them. 1. Open the View menu and select Page Break Preview. 2. If a message appears, click OK Your worksheet is displayed with page breaks, 3. To move a page break, drag the dashed line to the desired location. To delete a page break, drag it off the screen.

22

To insert a page break, move to the first cell in the column to the right of where break inserted. Then open the Insert menu and select Page Break. A dashes line appears to the left of the selected row. To exit page break Preview and return to your normal worksheet View, open the View Manu and select Normal.

6.5.3 Printing Column and Row Headings T

T

Excel Provides a way for you to select labels and titles that are located on the top edge and left side of a large worksheet, and print them on every page of the printout. This options is useful when a worksheet is too wide to print a single page. If don't use this option, the extra columns or row will be print on subsequent pages without any descriptive labels. To print column and/ or row headings on every page: 1. Open the File menu and choose Page Setup dialog box appears. 2. Click the Sheet tab to display the sheet options. 3. To create columns labels and a worksheet title, click the collapse Dialog icon to the right of the Row to Repeat at Top text box. Excel reduces the page Setup dialog box in size. 4. Drag over the row you want to print on ever page; A dashed line border surround the selected

area, and absolute cell references with dollar signs ($) appears in the row to repeat at Top text box; 5. click the Collapse Dialog icon to return the page setup dialog box 6. To repeat. Row labels that appears on the left of the worksheet, click the Collapse Dialog icon to the right of the column to Repeat at Left text box.

7. Select the row labels you want to repeat. 8. Click the Collapse Dialog icon to return once again to the page setup dialog box. 9. To print your worksheet, click Print to display the print dialog box. Then click OK.

6.6 Adding Header and Footers Excel lets you add headers and footer to print in formation at the top and bottom of every page of the printout, The information can include any text, as well as page numbers, the current date and time, the workbook Filename and the worksheet tab name. Top add header and footers, follows these steps: 1. Open the view menu and choose Header and Footer, or click the Header and Footer tab in the page setup dialog box. 2. To select a header, click the Header drop-down arrow. Excel displays a lost of suggested header you want. The sample header appears the top of the Header/Footer tab. 3. To select footer, click the Footer drop-down arrow, Excel displays a list of suggested footer information, Scroll through the list and click footer you want. The sample footer appears the top of the Header / Footer tab. 4. Click OK to close page setup dialog box and return to your worksheet. Or click the print bottom to display the print dialog box, and click OK. To print your worksheet. Note:- If the suggested header or footer suit you click the Custom Header or custom Footer bottom and enter your exact specifications.

6.7 Scaling a Worksheet to Fit on a Page If your worksheet is too large to print on one page even after you change the orientation and margins, you might consider using the Fit To options. This option shrinks the

23

worksheet to make it fit on the specific number of pages. You can specify the document's width and height.

Print a worksheet to fit a paper width or a number of pages 1. 2. 3. 4.

Click the worksheet On the File menu, click Page Setup, and then click the Page tab. Under Scaling, click Fit to. Do one of the following:

24