- Definition of a Spreadsheet
- Advantages of Using Electronic Spreadsheets over Manual Spreadsheet
- Examples of Spreadsheets
- Components of a Spreadsheet
- Application Areas of a Spreadsheet
- Creating A Worksheet/Workbook Using Microsoft Excel
- Cell Data Types
- Cell Referencing
- Basic Functions and Formulae
- Editing a Worksheet
- Worksheet Formatting
- Data Management
- Printing Worksheets
- A spreadsheet is essentially a ledger sheet that lets you enter, edit and manipulate numeric data.
- There are two types of spreadsheets namely:
- The manual spreadsheet.
- The electronic spreadsheet.
- A manual spreadsheet is the most commonly used type by book keepers as a ledger book with many sheets of papers divided into rows and columns on which various amounts of money are entered manually using a pen or pencil. You can visit your bursar's office and request to see a ledger sheet.
- An electronic spreadsheet on the other hand is prepared using a computer program that enables the user to enter values in rows and columns similar to the ones of the manual spreadsheet and to manipulate them mathematically using formulae. '
- In these notes, the word spreadsheet shall be used to refer to the electronic spreadsheet. '.
- The electronic spreadsheet utilizes the powerful aspects of the computer like speed, accuracy and efficiency to enable the user quickly accomplish tasks.
- The electronic spreadsheet offers a larger virtual sheet for data entry and manipulation.
For example the largest paper ledger you can get is one that does not exceed 30 columns and 51 rows while with an electronic spreadsheet, the least ledger has at least 255 columns and 255 rows!
- The electronic spreadsheet utilizes the large storage space on computer storage devices to save and retrieve documents.
- The electronic spreadsheet enables the user to produce neat work because the traditional paper, pencil, rubber and calculator are put aside. All the work is edited on the screen and a final clean copy is printed. With a handwritten spreadsheet, neatness and legibility depends on the writer's hand writing skills.
- Electronic spreadsheets have better document formatting capabilities.
- Electronic spreadsheets have inbuilt formulae called functions that enable the user to quickly manipulate mathematical data.
- An electronic spreadsheet automatically adjusts the result of a formula if the values in worksheet are changed. This is called the automatic recalculation feature. For a manual sheet, changing one value means rubbing the result and writing the correct one again.
- VisiCalc: This was the first type of spreadsheet to be developed for personal computers.
- Lotus 1-2-3: This is integrated software with spreadsheet module graphs and database.
- Microsoft Excel
- VP-Planner etc.
In these notes, the spreadsheet that will be considered in details is Microsoft Excel.
- A spreadsheet has three components
- This is the component in which data values are entered. It is made up of rows and columns. The intersection between a row and a column is called a cell. A row is a horizontal arrangement of cells while a column is a vertical arrangement of cells. Each row is labeled with a number while each column is labeled with a letter as shown in the Figure below. Each cell is referenced using the column label followed by the row label e.g. cell B3 has the value 20. A group of many worksheets make up a workbook.
A B C D E F 1 2 3 20 4 5
- Data values can be entered in the cells of the spreadsheet and managed by special Excel features found on the Data menu.
- These features were incorporated in Excel but they actually belong to database management software. One of such feature is filtering records, using forms, calculating subtotals, data validation pivot tables and pivot chart reports.
- If the data values for the same entity (related values) are entered on the same row, they form a record. Hence a worksheet can be manipulated to some extent as a database that has data records entered in it. Figure 2.2 shows a worksheet having two records, Joy and Jeff.
A B C D 1 Name IDNo. Age Nationality 2 Joy 123445 25 Kenyan 3 Jeff 125675 35 Ugandan
NB: A spreadsheet file is structured in such a way that it can be visualised as a table of records. That is why such a 'file can be imported into a database program as will be discusses later in databases.
- A graph is a pictorial representation of the base data on a worksheet. Most spreadsheets refer to graphs as charts. A chart enables the user to present complex data elements from a worksheet in a simple easy to understand format. Examples of charts are pie charts, line charts and bar charts. As shall be discussed later, it is easy to generate charts when working with a spreadsheet program. Figure below shows an example of a pie chart.
- Spreadsheets provide a set of data analysis tools that can be used to save steps when developing complex statistical or engineering analyses. The user is usually expected to provide the appropriate data and parameters for each analysis. The statistical tool then uses appropriate automated statistical or engineering functions and then displays results in an output table. Some of the tools generate charts in addition to the output tables.
- Because most of these tools are complex, the user needs to have the statistical background knowledge before attempting to use the tools. Examples of some simple statistical functions include the following:
- Average: This is used to calculate the mean of a set of values.
- Median: This is used to return the value in the middle of a set of values.
For example a set of values may be composed of: 10 20 30 40 50 60.
The average of the set is 35 and its median is also 35. The median is found by taking the average of the two numbers at the centre of the set, in this case 30 and 40.
- Many accountants find the spreadsheet a useful tool to use in recording daily transactions and keeping of financial records. Spreadsheets provide a user friendly environment for financial management and they come with inbuilt functions that make accounting tasks easier.
For example, the spreadsheet can be used by accountants to do the following:
- To track the value of assets over time (depreciation and appreciation)
- To calculate profits
- To prepare budgets
- Other formula like sum, average, product etc. enables the accountant to carry out his daily work without any problem.
- A spreadsheet enables neat arrangement of data into tabular structure. Related data can be typed on the same worksheet. However, when data is on different worksheets, the worksheets can be linked to enhance accessibility.
- Data management functions include sorting, filtering (displaying only the required items) and using forms to enter and view records.
- Spreadsheets enable the user to create, edit, save, retrieve and print worksheet data and records.
- The automatic recalculation feature enables the use of "What if' analysis technique. This involves changing the value of one of the arguments in a formula to see the difference the change would make on the result of the calculation. For example, a formula to calculate a company's profit, margin may be as follows:
Profit = (Total units sold x sale price) - (Total units bought x cost price) - Operating expenses.
A sales manager in the company can ask the following question: What if sales increase by 20%, how much profit wills the company make? The manager substitutes the total units sold value with one that is 20% higher and the spreadsheet automatically displays the new profit. A traditional analysis method would require a different work sheet to be prepared. Therefore, this method can be used for financial forecasting, budgeting, stock portfolio analysis, cost analysis, cash flow etc.
- To start Microsoft Excel, click Start button, point to Programs and then select Microsoft Excel from the programs menu This procedure may vary slightly depending on the version of Excel you are using or the computer's hardware and software configuration.
- The Windows environment allows a person to place shortcuts to a program's executable (.exe) file in various places like the desktop. If the Excel shortcut is on the desktop, simply double click it to start the application.
- The Microsoft Excel application window opens as shown in the Figure below. Make sure that you can be able to identify all the labeled parts of the Microsoft Excel application window.
- The Microsoft Excel application window is made up of the following components:
- Quick Access Toolbar: Displays quick access to commonly used commands.
- Title bar: It has the title of the application and control buttons for minimising, maximising and closing the application
- File Tab: The File tab has replaced the Office button. It helps you to manage the Microsoft application and provide access to its options such as Open, New, Save, As Print, etc.
- The name box to the left of the formula bar displays the position of the cell in which data or a formula is being entered which is also called the current cell.
- Cell: The intersection of a row and column; cells are always named with the column letter followed by the row number (e.g. A1 and AB209); cells my contain text, numbers and formulas.
- Range: One or more adjacent cells. A range is identified by its first and last cell address, separated by a colon. Example ranges are B5:B8, A1:B1 and A1:G24
- Status bar: It shows the processing state of the application. For example, on its left is the word Ready which shows that the spreadsheet is ready to receive user commands. '
- New Sheet: Add a new sheet button
- Ribbon: Displays groups of related commands within tabs. Each tab provides buttons for commands.
- Formula ar: This is one of the most important components of the Microsoft Excel application window. It enables the user to enter or edit a formula or data in a cell. You can identify the formula bar because it has an equal sign (or fx).
- The Worksheet: Consists of cells, rows and columns. Data is entered here for manipulation.
- View Option: Display worksheet view mode.
- Cell pointer: It marks the position of the current cell or the insertion point. It is special cursors that is rectangular in shape and makes the current cell appear as if it has darker boundaries.
- Worksheet labels: These are usually of the format Sheet 1, Sheet 2 etc. A workbook may have several sheets. It is also possible to rename the sheets by right clicking on the labels then choosing rename command from the shortcut menu that appears. The active sheet (one being used) has its label appearing lighter in colour than the rest. To move to a particular sheet in the workbook, simply click its sheet label.
- Vertical and horizontal scroll bars: Clicking the arrows at their ends moves the worksheet vertically and horizontally on the screen respectively.
- The worksheet has the following components: Cells: An intersection between a row and a column.
- Rows: Horizontal arrangement of cells. Columns: Vertical arrangement of cells.
- Range: Is a group of rectangular cells that can be selected and manipulated as a block.
- Click cell D5. Notice that the cell pointer immediately moves to the cell and the name box reads D5. Typing on the keyboard now inserts entries in cell D5 as long as the pointer is still there.
- Click letter A that heads the first column. Notice that the whole column is highlighted.
- Double click cell E10. Notice that the text cursor forms in the cell and you can now type characters inside the cell. Also the status bar will now read enter which means that Microsoft Excel expects you to enter a value in the cell.
- Click the down arrow on the vertical scroll bar. The worksheet moves upwards on the screen. The opposite happens when you click the up arrow on the vertical scroll bar.
- Click the right button on the horizontal scroll bar. The worksheet moves to the left. The opposite happens when you click the left button on the horizontal scroll bar.
- Press the right arrow key on the keyboard. Notice that the cell pointer moves one column to the right on the same row. This can also be done by pressing the Tab key once.
- Press the left arrow key on the keyboard. Notice that the cell moves one column to the left on the same row. Pressing gives the same results.
- Press the up arrow key on the keyboard. Notice that the cell pointer moves one row up on the same column.
- Press the down arrow key on the keyboard. Notice that the cell pointer moves one row down on the same column.
- Press the end key. The status bar will display the message "END". If you press the right arrow key, the cell pointer will move right to the last cell on the row. If the left up or down keys were to be pressed instead, the cell pointer would move to the last cell to the left, top or bottom respectively.
- Pressing Ctrl+Home moves the cell pointer to the first cell of the worksheet i.e. cell A1.
- At its simplest level, creating a worksheet consists of starting the spreadsheet program and entering data in the cells of the current worksheet. , However, a person can decide to create a worksheet either using the general format or from a specially preformatted spreadsheet document called a template.
Using the general format
- When a spreadsheet program is running it will present the user with a new blank screen of rows and columns. The user can enter data in this worksheet and save it as a newly created worksheet.
- If this is not available then click File tab and select the new command. On the screen that appears, Double click the blank workbook option. Enter data in the new worksheet created.
Using a template
- Click File menu Tab then new command. On the spreadsheets solutions tab, double click the template that you wish to create. Figure below shows some examples of templates that may be present for selection.
- NB: If the template was saved previously on the hard disk, it will open as a new worksheet with all the preformatted features present allowing the user to enter some data.
- However, some templates may require the original program installation disk in order to be able to use them because they may not have been copied to the hard disk during program installation.
- Editing a cell means changing the contents of the cell. Before the contents in a cell can be. Changed, the cell must be selected by making it the current cell. To edit a cell entry proceeds as follows:
- Move the cell pointer to the cell you wish to edit.
- Double click the formula bar for the text cursor to appear in the bar. The status bar message changes to edit
- Use the keyboard to delete and add contents to the formula bar then press enter key to apply. Click the save button on the quick access toolbar or CTRL+S to save the edited changes.
- As you have experienced with the previous two examples, working with one item at a time is tedious and time consuming. Using a range saves time when working with a large amount of data.
- A range is a rectangular arrangement of cells specified by the address of its top left and bottom right cells, 'separated by a colon (:) eg. Range A1:C10
Selecting multiple ranges
- When using a mouse, you can select more than one range without removing the highlight from the previous. To do this:
- Hold down the Shift key or the Ctrl key while you click on the row header of the second range you want to highlight. What happens? Do you notice the difference when holding down the shift and the ctrl keys?
- Shift key will cause all columns/rows between the selected and the newly clicked cell to be highlighted.
- Ctrl selects individually clicked cells or range.
- You can hide some rows or columns in order to see some details, which do not fit, on the screen. To do this:
- Highlight the columns/rows you want to hide
- Right click on the selected colum/row then click hide command on the menu that appears.
- To save a worksheet, one has to save the workbook in which it belongs with a unique name on a storage device like a hard disk. The procedure below can be used to save a workbook:
- Click File Tab option then select Save as' command. Alternatively, click the save icon on the quick access toolbar. The save as dialog appears
- Select the location in which your workbook will be saved in the Save in box then type a unique name for the workbook in the File name box. Make sure that the option Microsoft Excel Workbook is selected under the save as type box.
- Click the Save button to save.
Retrieving a saved workbook
- This means opening a workbook that was previously saved.
- Click File tab option then the Open command. Alternatively just click the Open command on the standard toolbar. The open dialog box appears on the screen.
- Click the Look in drop down list arrow and select the drive or folder where the workbook was saved. For example, if you saved in a flash drive, insert it in the usb port then select the name of the flash drive. A list of folders and files in the drive will appear in the list box.
- Double click the icon of the workbook you want and the worksheet will be displayed in the Microsoft Excel window. Notice that the cell pointer is in the same cell it was in when the worksheet was last Saved.
- Click File then Close command. This closes the worksheet but does not Close the Excel spreadsheet program. Alternatively, click the; close button of the worksheet window
- Click File tab then Exit command. This closes not only the worksheet but also the spreadsheet program as well. Alternatively click the close button of the main application window.
- There are four basic types of data used with spreadsheets:
- Any text or alphanumeric characters entered in a cell are viewed as labels by the spreadsheet program. Labels are used as row or column headings usually to describe the contents of the row or column. For example, if a column will have names of people, the column header can be NAMES. Sometimes, numbers can be formatted so that they can be used as labels. To achieve this add an apostrophe just before the most significant digit in the number. For example, the number 1990 will be treated as numeric. if typed in a cell but' 1990 will be treated as a label.
- Labels are aligned to the left of the cell and cannot be manipulated mathematically.
- These are numbers that can be manipulated mathematically. They may include currency, date, numbers (0-9), special symbols or text that can be manipulated mathematically by the spreadsheet.
- These are user designed mathematical expressions that create a relationship between cells and return a value in a chosen cell. In Microsoft Excel, a formula must start with an equal sign. For example, the formula =B3+D4 adds the contents of B3 and D4 and returns the sum value in the current cell.
- Excel formulae use cell addresses and the arithmetical operators like plus (+) for addition, minus (-) for subtraction, asterisk (*) for multiplication and forward slash (/) for division.
- Using cell addresses, also called referencing, enables Microsoft Excel to keep calculations accurate and automatically recalculates results of a formula in case the value in a referenced cell is changed. This is called automatic recalculation.
- These are inbuilt predefined formulae that the user can quickly use instead of having to create a new one each time a calculation has to be carried out Microsoft Excel has many of these formulae that cover the most common types of calculations performed by spreadsheets. To add the contents of cell B3 and D4 the sum function can be used as shown below: = Sum (B3:D4)
- A cell reference identifies a cell or a range of cells on the worksheet and shows Microsoft Excel where to look for the values or data needed to use in a formula. With references, you can use data contained in different cells of a worksheet in one formula or use the value from one cell in several different formulae.
- By default, Microsoft Excel uses the A1 cell referencing style. This means that a cell is identified by its column label followed by the row number. However, the R1C1 referencing style can be used. In this case, the cell is referencing by its row number followed by its column number. The table below gives a few examples of equivalent referencing using both styles.
A1 style R1C1 style B2 R2C2 C10 R10C3 E20 R20C5
- The R1C1 style is useful when automating commonly repeated tasks using special recording programs called Macros.
- When performing tasks that require cell referencing, you can use formulae whose cell references keep on changing automatically depending on their position in the worksheet.
- This is called relative cell referencing. A good example would be if you type the formula =A1+B1 in cell C1. If the same formula is copied to cell C2 the formula automatically changes to =A2+B2.
- These are cell references that always refer to cells in a specific location, of the worksheet even if they are copied from one cell to another. To make a formula absolute, add a dollar sign before the letter and/or number, such as $B$lO. In this case, both the column and row references are absolute. .
- Labels of columns and rows on a worksheet can be used to refer to the cells that fall within those columns and rows. It is possible to create a name that describes the cell or range then use it instead of having to specify a range with actual cell references. Such a descriptive name in a formula can make it readable and easier to understand its purpose.
For example, the formula =SUM(SecondQuarterProfits) might be easier to identify than =SUM(A10:C20). In this example, the name SecondQuarterProfits represents the range A10:C20 on the worksheet. Names can also be used to represent formulae or values that do not change (constants). For example, you can use the name. Tariffs to represent the import tax amount (such as 7.0 percent) applied to imports.
To create a named range
- To create a named range proceeds as follows:
- Select the range to be named:
- Click inside the name box to move the text cursor inside. Delete the Cell reference that is there and type a name for the range.
- Press Enter key to apply.
- Select the range to be named:
- Formulae perform mathematical operations ranging from very simple arithmetic problems to complex scientific, financial and mathematical analysis.
- Average: It returns the average (mathematical mean) of a set of values which can be numbers, arrays or references that contain numbers. If the value 20 is in cell D1O and 30 in E10 then: =Average(D10:E10) returns 25 as the average of the two values.
- Count: Counts the number of cells that contain values within a range e.g. = count (A10: E10) many return a value 5 if all the cells have values.
- Max: It returns the largest value in a set of values. It ignores text and logical values e.g. = Max (A10:E10) will return the maximum value in the range.
- Min: It returns the smallest value in a set of values. It ignores text and logical values e.g. = Min (A10:E10) will return the minimum values in the range.
- Mode: It returns the most frequently occurring value in a set of values. e.g. = Mode (A10:E10)
- Rank: Returns the rank of a number in a list by comparing its size relative to the others.
For example if A1 to A5 contains numbers 7, 3.8,3.8, 1 and 2 then RANK (A2, A1:A5,1) returns 3 while RANK (A1, A1:A5,1) returns 5. The general format is RANK (number to be ranked, range, order).
- If: It returns a specified value if a condition is evaluated and found to be true and another value if it is false. If (marks > 50, "pass", "fail") will display a pass if values are more than 50 else it will display fail.
- Countif: Counts the number of cells within a specified range that meet the given condition or criteria. e.g. suppose A10:E10 contains eggs, beans, beans, eggs, eggs, countif(A10:E10, "Eggs") will return 3.
- Sumif: It adds values in the cells specified by a given condition or criteria. e.g. For example if A10 to E10 contains values 10,50,60, 30, 70, to sum all values greater than 50 =Sumif(A10:E10, ">50"). This returns 130.
- Sum: adds values in a range of cells as specified and returns the result in the specified cell. e.g Sum (A10:E10) adds values in the range
- Product: multiplies values in a range of cells and returns the result in the specified cell.
For example if A10 has 30 and B10 has 3, Product (A10:B10) will return 90.
Arithmetic formulae - using operators
+ (plus) adds values as specified
- (minus) subtracts values as specified
* (multiplication) multiplies values
/ (division) divides values.
( ) parenthesis encloses arguments to be calculated first.
For a formula =(A1 +C3)/E20, if the value in E20 is not zero, the result is displayed in the current cell.
Order of execution
- If several. Operators are used in a single formula; Microsoft Excel performs the operations in the order shown in Table 2.3. Formulas with operators that have same precedence i.e. if a formula contains both a multiplication and division operator are evaluated from left to right. Enclosing part of the formula to be calculated in parentheses or brackets makes that part to be calculated first.
Operator Name Precedence 1. −
3. * and / !
4. + and -
Negation as in −1
Multiplication and division
Addition and subtraction
- Spreadsheet software automates many processes that could have been tedious if done manually. For example with Microsoft Excel, you can do calculations using formulae fairly easily as you give the data and correct instructions to the program. Copying and moving of, data can also be done quickly and efficiently.
- When data is cut or copied from the worksheet, it is temporarily held in a storage location called the clipboard.
- To copy a cell or a range of cells:
- Highlight the cells or range you want copied
- Click on the copy button, located on the home tab
- Select the cell in which you want to place a copy of the information
- From the home tab again, click Paste command. The Paste command puts a copy from the clipboard on the specified location
- Unlike the Copy command where a duplicate copy is created, the Move command transfers the contents of the original cell (s) to a new location
To move a range of cells:
- Highlight the range you want to move.
- From the home tab, select Cut.
- Specify the location you want to move the contents to.
- From the home tab again, click Paste.
- Worksheet formatting refers to enhancing the appearance of the worksheet to make it more attractive and appealing to the reader. Appropriate formatting should be used to lay emphasis, catch attention and bring otherwise II hidden detail to the fore of the document.
- The golden rule of formatting is to use simple clear formats. It essentially consists of changing text colour and typeface (font), size, style and alignment. In Microsoft Excel, format the cells whether empty or not and their contents will acquire the set format automatically.
- To format a single cell, make it the current cell then format menu option and select the cells command In the format cells dialog box, make the formatting specifications that you wish then click the ok button to apply. If it is a range of cells, they must be highlighted first before formatting them as a block of cells.
- Highlight the cells that have the text to be formatted.
- Click the dialog box launcher under the font group on the home tab. The format cell dialog box shown below appears
- Select the font tab as shown in the figure by clicking it.
- Select the font type e.g. Times New Roman. Other font formatting features like style, size, underline and colour are available and can be selected.
- Click button to apply.
NB: Alternatively, use the formatting toolbar to accomplish all your text formatting needs. Notice that the options in the font dialog box are commands on the formatting toolbar.
- Highlight the cells that have the numbers to be formatted.
- Click the dialog box launcher under the font group on the home tab to launch the format cells dialog box as shown in the figure below.
- Select the Number tab as shown in the figure below.
- You can now choose number formats as explained below:
Number Meaning General general format cells have no specific number format. Number Used for general display of numbers e.g. 2345.23. Currency For displaying general monetary values e.g. $100, Ksh.10. Accounting Lines up the currency symbols and decimal points Date Displays date in chosen format. Time Displays time in chosen format. Percentage Multiplies the value in a cell with 100 and displays it as %. Text Formats cells to be treated as text even when numbers are entered. Custom For a number format not predefined in Microsoft Excel, select custom then define the pattern.
- You may need to put a printable border around your worksheet or in a range of cells to make it more attractive and appealing.
- To put a border:
- Highlight the range you wish to insert borders.
- Click the dialog box launcher under the font group on the home tab to launch the format cells dialog box as shown in the figure below.
- Click the borders tab and specify the border options for left, right, top and bottom.
- From the style options, select the type of line thickness and style. Also select the preset options.
- Click the OK button. The selected range will have a border around it.
- Sometimes, the information entered in the spreadsheet may not fit neatly in the cell set with the default height and width. It therefore becomes necessary to adjust the height of a row or the width of a column. The standard width of a column in Microsoft Excel is 8.43 characters but can be adjusted to any value between 0 and 255.
Changing column width
- Move the mouse pointer to the right hand side line that separates the column headers i.e. for instance the line between A and B.
- Notice that the mouse pointer changes from a cross to a double arrow
- Click the mouse button and hold it down so that you can now resize the width of the column by dragging to the size you wish. After Dragging to the required point release the mouse button and the Column will have a new size.
NB: Alternatively, Click the column header to select the whole column then Right click. Click the Column Width command from the menu that appaears. Type a width in the dialog box that resembles the Figure below then click Ok button to apply.
NB: To change the widths of several columns at the same time, highlight them first before following this method.
Changing row height
- Point to the line that separates two row numbers e.g. the line between 1 and 2. The mouse pointer becomes a double arrow.
- Drag the line until the height of the row is as required then stop and release the mouse button.
NB: Alternatively, Click the row header to select the whole row then Right click. Click the Row height command from the menu that appaears. Type a height in the dialog box that appears then click Ok button to apply.
Inserting rows and columns
- Click cell A5 to make it the current or active cell.
- Select the drop down arrow on the insert command from the home tab. Select Insert Sheet Rows to insert a row above cell A5 and shift all the other rows downward.
To insert columns
- Click cell A5 to make it the current or active cell.
- Select the drop down arrow on the insert command from the home tab. Select Insert Sheet columns to insert a column to the left of column A and shift all the others to the right.
NB: Alternatively, Right click on the selected cell then select insert on the menu that appears to display the dialog box select the entire row or entire column options to insert a row or column respectively.
- The word global in this case refers to the entire worksheet. In order to format the whole worksheet globally, it must be selected as a whole.
- Two methods can be used to select a worksheet globally:
- Click the top left comer of the worksheet that has a blank column header i.e. immediately on the left of A and just above I,
- Press Ctrl+A on the keyboard.
- Notice that the whole worksheet becomes highlighted. It can now be formatted as one big block using format cells command.
- At times, it becomes necessary to use advanced data management tools to manage large ,data stored on a 'worksheet. For example, if the worksheet has many records, it may become necessary to arrange them in a particular order using a method called sorting for easier access to data items. Other methods of data management include use of filters, total/subtotal function and forms.
- To carryout sorting proceed as follows:
- Highlight the range that you wish to sort by clicking its column header letter.
- Click the Data tab then Sort . Notice that the Sort by field is already reading the field that you selected. This field is called the criteria field.
- Select the field to be used as the key for sorting and the sort order as either descending or ascending then click OK button to apply.
- Filtering is a quick and efficient method of finding and working with a subset of data in a list. A filtered list will only display the rows that meet the condition or criteria you specify. Microsoft
- Excel has two commands for filtering lists.
- The auto filter: It uses simple criteria and includes filter by selection.
- Advanced filter: It uses more complex criteria.
- In this Notes we will look at the autofilter.
- Filters can be applied to only one list on a worksheet at a time.
- Click a cell in the list that is to be filtered; usually the list is in a column.
- On the Data tab, point to Filter
- To display only the rows that contain a specific value, click the arrow in the column that contains the data you want to display.
- Click the value that is to be displayed by the filter from the drop down list.
NB: Sometimes while looking through a list of values on a large worksheet, you may come to a value of interest and want to see all other occurrences of the value in the spreadsheet.
- Simply click the cell that has the value then click auto filter on the standard toolbar. Microsoft Excel turns on AutoFilter and then filters- the list to show only the rows you want.
- Consider the following scenario: A company that has many salespersons will need to know how much each of them should be paid at the end of a period by looking at individual sales volumes. Also, the grand total for all the payments has to be calculated. Therefore, if the salespersons are held in a list, there would be need to calculate the amount due to each of them. This can be called a subtotal in the list. All the subtotals can then be added together to make the grand total. Consider the following list:
Name Amount Owed Stephen 6000 Joy 3000 Stephen 2000 Virginia 5000 Joy 800 Stephen 200 Virginia 5000
- Microsoft Excel can automatically summarise the data by calculating subtotal and grand total values of the list. To use automatic subtotals, the list must have labelled columns and must be sorted on the columns for which you want subtotals. In this example, the list is first sorted by name
- Click a cell in the list that will have subtotals e.g. cell A3.
- On the Data tab click Subtotals command
- Notice that all the data range is now selected.
- In each change in box, select Name from the drop down list because we want a subtotal for each of the names.
- In the Use function box select the sum function then select the list for which subtotals will be inserted in the add subtotals box by checking the appropriate label. In this case it is the amount owed field.
- Click ok button to apply and the list will now have sub totals inserted
- Use theAutoCalculate feature in Microsoft Excel to automatically show the total of a selected range. When cells are selected, Microsoft Excel displays the sum of the range on the status bar. Right clicking this function displays other functions like Min, Max and Average that can also be used. To find the total of a range, highlight it then click the autosum icon ∑ on the standard toolbar.
- A form is a specially prepared template that the users can use to enter data in a worksheet. It is specifically formatted to enable users to enter data in a format that is more convenient to them. If data is collected on paper before entering in the computer, then a form can be created to have the layout of the data on the paper to quicken data entry procedures. To display a form: Click 'Data, then form.
- Charts/graphs are graphics or pictures that represent values and their relationships. A chart helps the reader to quickly see trends in data and to be able to compare and contrast aspects of data that would otherwise have remained obscure. Microsoft Excel has both two-dimensional and 3-dimensional charts that can be used instead of the raw data in the table that has to- be studied for a long time to understand it.
- The various types of charts available include column, bar, line. Pie, bubble and area charts among others. Consider carefully the type of chart that would best represent the base data in the worksheet before creating one. For example, if the aim is to depict the performance index of a student from Form I-to 3, a line chart would be most appropriate because it clearly shows the trend in performance.
- Line chart - represents data as lines with markers at each data value in the x-y plane.
- Column chart- represents data as a cluster of columns comparing values across categories. .
- Bar chart - data values arranged horizontally as clustered bars. Compares values across categories.
- Pie chart - it displays the contribution of each value to a grand total.
- Scatter chart - compares pairs of values on the same axis.
- To view types of charts, right click the chart object then select the chart type command.
- A chart must be based on values that are already entered in the worksheet.
- To create a chart:
- Select the range of values for which you want to create a chart.
- Select the Insert tab, then in the Charts group, click on the Chart button of your choice. The chart sub-types will appear which will provide you more chart options to select from. To view all chart types, click on the dialog box on the Charts group. The Insert Chart window will appear with all chart types.
- Select the All Charts tab.
- Click on the first Column button in the window.
- Click on the OK button
- Your chart selection will appear on the spreadsheet
- Once the chart is created, its size and location can be changed in the worksheet. The chart element is enclosed inside a boundary called the chart area and hence both can be resized independently. Simply click the object you wish to resize and use the object handles just like in objects to drag to size. To move the chart, click inside the chart area then drag to the desired position.
- A data range is a rectangular block of cells that provides the base data that is used to create the chart. In charting, a data range is referenced as an absolute range e.g . =Sheet1!$B$2:$C$8 which means that the base data is found on Worksheet 1 and absolute range B2:C8.
- To see the data range of a chart, right click it then select the Source data command. .
- Each representation of data on a chart can either be labelled by a value or text label. For example, in a bar chart that compares the height of pupils, each bar can be given a value label to make it more readable.
- Select the chart
- Click on the plus sign that appears on the right side of the chart to display the chart elements as shown in the figure below
- Check the data label options to display labels
- Each chart must have a heading showing clearly what it represents. To I make the chart understandable, include axis titles.
- To include axis titles proceed as follows:
- Select the chart
- Click on the plus sign that appears on the right side of the chart to display the chart elements
- Check the Chart title option to display the Chart title then edit it to your preferred title
- The legend is like a key that explains what each colour or pattern of the data representation in the chart means. For example, Microsoft Excel may give red colour to one data value and green to the other. Without a legend it would be difficult to know how to differentiate the two sets of values.
- To create a legend:
- Select the chart
- Click on the plus sign that appears on the right side of the chart to display chart elements
- Check the legend option to display the legend and edit it to your preference
- A worksheet will finally be printed for sharing with others or for filing purposes. If it contains objects like charts, it may not fit on a standard printing page using the default printing options and settings. Therefore, Microsoft Excel allows the user to preview and set up the pages of a' worksheet in order to fit them on the hard copy page.
- Click Page Layout tab then Page setup dialog box launcher command to display the page setup dialog box shown below.
- On the Page tab, select the orientation of the page.
- After making the necessary selections, click OK to apply.
- To print click File tab then Print command. The print dialog, box appears .
- Select printer - the name box in this dialog box enables a person to select the printer that will be used to print the document. All the printers that are installed on the computer will be available here.
- The print what options are:
- Selection - this prints the selected worksheet area.
- Workbook - prints all the worksheets in the workbook.
- Selected chart - prints the selected chart only.
- As explained earlier, page orientation refers to the layout of the text on the page. A worksheet can also be printed on either landscape or portrait depending on the number of columns across the worksheet.
- The number of copies box specifies how many copies of a particular worksheet or workbook should be printed.
- Sometimes only some specified pages in a workbook are specified for printing e.g. if a workbook has 100 pages and you wish to print only pages 50 to 60 select the page(s) range button then type 50 and 60 in the from, to boxes respectively before clicking the OK button.
- After selecting all the options, click the OK button to print.
Some common printing problems
- A message appears on the screen saying that the printer specified could not be found in the directory.
Possible problems and solutions
- The printer could be off. Switch it on and it will start printing.
- The data cable to the printer could be loose. Make sure it is firm at the ports.
- The wrong printer could have been selected. Select the right one in the print dialog box and send the print job again.
- A message appears on the screen reading that there is paper jam. The printer is clogged with a paper jam. Alert the lab, technician or the Teacher to clear the paper jam.