Computer application in business

What is Microsoft Excel

What is a spreadsheet
How to identify the components and user interface of Microsoft Excel How to identify and remember the names of buttons and toolbars Types of cell entries
How to start Excel, open, save and close Excel files

1.1 Introduction to Microsoft Excel 2003
Welcome to Microsoft Excel 2003! This is the instructor manual that is built and to be used by the instructor who will be teaching BCOMP1101 ? Computer Application for Business to Business Year 1 Semester 1 students. In this instructor manual, there will be explanation about Microsoft Excel 2003.

It is recommended you should use Microsoft Excel 2003 application to teach the students since this software is still widely used at the moment despite that Microsoft Office 2007 will be released in 2007. Currently the version of Microsoft Excel 2003 is Excel 11.0 and above (with various patches to upgrade the application). You still can use some of the older versions of Microsoft Excel such as 2000 and 2002 (Office XP version) but some features may not be available nor are different compared to Microsoft Excel 2003.

1.2 What is a Spreadsheet?
Microsoft Excel is a powerful spreadsheet program allows you to organize data, complete calculations, make decisions,

Need essay sample on "Computer application in business"? We will write a custom essay sample specifically for you for only $ 13.90/page

graph data, develop professional looking reports, publish organized data to the Web, and access to real-time data from Web sites. The four major parts of Excel are:

1. Worksheets
Worksheets allow you to enter, calculate, manipulate, and
analyze data such as numbers and text. The term worksheet means the same as spreadsheets.
2. Charts
Charts pictorially represent data. Excel can draw a variety of twodimensional and three-dimensional charts. 3. Databases
Databases manage data. For example, once you enter data

onto a worksheet, Excel can sort the data, search for specific data, and select data that meets criteria.
4. Web Support
Web support allows Excel to save workbooks or parts of a
workbook in HTML format so they can be viewed and manipulated using a browser. You also can access real-time data using Web queries. A spreadsheet is a rectangular table (or grid) of information, often financial information. The word came from “spread” in its sense of a newspaper or magazine item (text and/or graphics) that covers two facing pages, extending across the center fold and treating the two pages as one large one. The compound word “spreadsheet” came to mean the format used to present bookkeeping ledgers—with columns for categories of expenditures across the top, invoices listed down the left margin, and the amount of each payment in the cell where its row and column intersect— which were traditionally a “spread” across facing pages of a bound ledger (book for keeping accounting records) or on oversized sheets of paper ruled into rows and columns in that format and approximately twice as wide as ordinary paper. A workbook is a collection of worksheets. A worksheet is a grid of columns and rows in which you enter labels, value and data. Cells are organized into rows and columns to create a worksheet. Each cell can contain up to 32,000 characters. There are 256 columns run vertically up and down a worksheet whereas there are 65536 rows run horizontally from left to right across a worksheet.

1.3 Microsoft Excel 2003 User Interface
The user interface of Microsoft Excel has some similarities with Microsoft Word except for certain menus, toolbars and commands. On this page, a sample screenshot of Microsoft Excel 2003 is shown here with labels to name the user interface components.

Figure 1.1: Microsoft Excel 2003 interface

The Title Bar is located at the top of the window includes the application Controlmenu icon, the application name, the filename of the active document,
and the Minimize, Restore (or Maximize), and Close buttons. The filename of the active document is also known as the workbook name. A workbook is a collection of worksheets. A worksheet is a grid of columns and rows in which you enter labels, value and data.

Just below the title bar is the menu bar. The menu bar contains the menus for the application such as File, Edit, View Insert, Format, Tools, Data, Window and Help. On the same row of the menu bar, there are Ask a Question field box and minimize, restore (or maximize) and close buttons for that particular active workbook. Below the menu bar, there are 2 toolbars, which are standard toolbar and formatting toolbar. The toolbars contain buttons which perform specific functions. More of these will be described in 1.6 Identifying Toolbars and Buttons.

Just below the formatting toolbar are two very important components of Excel that you will need them all the time. On the left is the Name Box. It displays the current active cell or cells. Inside the Name Box contains the cell reference. A cell reference the column letter and row number that identifies a cell; e.g. cell A1 refers to the cell at the intersection of column A and row 1. The Formula Bar is located to the right of the Name box. It is used to create and edit values. The Formula Bar becomes active whenever you begin keying data into a cell. When the Formula Bar is active, the Enter, Cancel, and Edit Formula buttons appear.

When you select a cell, you will notice that there will be one column heading letter and one row heading number being highlighted in light blue. This is to indicate that you have selected a cell and it became an active cell. At the same time, you will see that there’s a thick black border surrounds the active cell. At the bottom of this thick black border is a small black square box which we call it fill handle. We will see what the usage of the fill handle is, later.

However, if you select more than one cells, you selected a range of cells.
These selected cells have their column and row headings highlighted in light blue to indicate they are being selected. However, there can only be one active cell at a time. In this case, the active cell is a white box whereas the rest of inactive cell are in light blue colour. This “white box” will be indicated as active cell on the name box. The scroll boxes and scroll bars on the right and bottom of a worksheet allows you to navigate through the entire active worksheet in order to see the other parts of it. At the bottom of the window, there are tab scrolling buttons that you can scroll through the sheet tabs of the Excel workbook. The buttons without the | only scroll left and right by a bit whereas the buttons with the | will jump to the first and the last sheet tabs respectively. Clicking on these tabs scrolling buttons does not change the active worksheet view to another worksheet. The tab split box is meant to adjust the length of the scroll bars.

In order to view other worksheets, you have to click on the sheet tabs. An active sheet tab has its words in bold. You can set colours for sheet tabs to make it easier to differentiate and identify.

Excel 2002 and later versions have task pane features. Task Pane is a panel to the right of the window that has many functions and features that you can quickly access and navigate around.
At the bottom of the window, there is a row that is known as the status bar. The status bar shows some additional information about the current active workbook and application. For example, it may show quick calculations of selected range of cells containing numbers, num lock, caps lock and scroll lock keys enabled or disabled.

1.4 Starting Microsoft Excel 2003
Now, we will learn how to start using Microsoft Excel 2003 after learning and understanding the basic components and interface of Microsoft Excel 2003. To launch Microsoft Excel 2003, there are few ways to do it.

1) Click on the shortcut icon on the desktop, if the shortcut icon is present. 2) Launch the application from Start
Microsoft Excel 2003.

All Programs

Microsoft Office

3) Launch the application from Start
All Programs
New Microsoft
Office Document. Then choose new Excel Workbook option and click Ok. 4) Open My Computer. Then on the address bar, type in/navigate to C:Program FilesMicrosoft OfficeOffice10 and then search for EXCEL.exe application file. Note: This method may be different because it depends on how Microsoft Office was installed.

Once you have launched Microsoft Excel, you show a blank workbook with task pane displayed as shown in Figure 1.2.

Figure 1.2: Blank workbook

1.5 Types of Cell Entries
After the application is launched with a blank workbook, there are few things you need to know about entering data into cells in a spreadsheet. 1) Constant ? An entry that does not change. For example, numeric value or descriptive text.

2) Function ? A predefined computational task.
3) Formula ? A combination of numeric constants, cell references, arithmetic operators and functions. It always begins with an equal sign ( = ).
It is important to understand the 3 types of cell entries because when you follow instructions to do an Excel project, you must understand the jargons in the sentences.

1.6 Identifying Toolbars and Buttons
Right now, with Microsoft Excel launched, it is time to identify the toolbars and their buttons. By referring back to Figure 1.1, we can see three toolbars are brought to attention by the arrows and names. The standard toolbar as its name applies, it consists of buttons that corresponds to most basic commands in Excel (and also other Microsoft applications as well, except for some minor differences). The formatting toolbar is located just below the standard toolbar. The formatting toolbar consists of buttons that correspond to common formatting operations. Formatting means to change the looks and styles of cells. The toolbar at the bottom of the window is the drawing toolbar. Drawing toolbar consists of buttons that correspond to common drawing commands in Excel.

There are many other toolbars available in Excel such as Charts, Forms, Formula Auditing, PivotTable, Protection, Visual Basic, Web and etc. Some of the other common toolbars that we may use are Charts, Forms and PivotTable. Each toolbar has buttons with a specific picture/icon to be unique from others. Each of these buttons perform specific task. The following table provides you the simple information of each task name, icon/button, how to access it from the menu bar or the alternative keyboard shortcut.

Task

Icon /
Button

Menu Bar

Keyboard
Shortcut

New Blank
Document

File | New

CTRL+N

Open

File | Open

CTRL+O

Save

File | Save

CTRL+S

File | Print

CTRL+P

Print Preview

File | Print Preview

Spelling

Tools | Spelling

Cut

Edit | Cut

CTRL+X

Copy

Edit | Copy

CTRL+C

Paste

Edit | Paste

CTRL+V

Undo

Edit | Undo

CTRL+Z

Redo

Edit | Redo

CTRL+Y

Insert Hyperlink

Insert | Hyperlink

CTRL+K

E-mail

Format Painter

AutoSum

Insert | Function

ALT+=

Paste Function

Insert | Function

SHIFT+F3

Sort Ascending

Data | Sort

ALT + D | S

Sort Descending

Data | Sort

ALT + D | S

Chart Wizard

Insert | Chart

F11

Drawing

View |Toolbars | Drawing

Zoom

View | Zoom

Microsoft Office
Excel Help

Help | Microsoft Office Excel Help

PivotTable and
PivotChart Report

Data | PivotTable and PivotChart
Report

ALT + D | P

New Comment

Insert | Comment

ALT + I | M

AutoFilter

Data | Filter | AutoFilter

ALT + D | F | F

Find

Edit | Find

CTRL+F

Find and Replace
Go To
Font

Edit | Replace
Edit | Go To
Format | Font | Font Tab

CTRL+H
CTRL+G
CTRL+D

Font Size

Format | Font | Font Tab

CTRL+D

Bold

Format | Font | Font Tab

CTRL+B

Italic

Format | Font | Font Tab

CTRL+I

Underline

Format | Font | Font Tab

CTRL+U

Align Left

CTRL+L

Center
Align Right
Merge and
Center
Currency Style

Format | Cells | Number Tab

Percentage Style

Format | Cells | Number Tab

Comma Style

Format | Cells | Number Tab

Increase Decimal

Format | Cells | Number Tab

Decrease
Decimal
Decrease Indent

Format | Cells | Number Tab

Increase Indent

Format | Cells | Alignment Tab

CTRL+E
CTRL+R

Insert Borders

Fill Color

Format | Borders and Shading |
Borders Tab
Format | Cells | Pattern Tab

Font Color

Format | Font | Font Tab

AutoFormat

Format | AutoFormat

CTRL + 1 |
Border Tab
CTRL + 1 |
Pattern Tab
CTRL + 1 | Font
Tab
ALT + O | A

Format Cells

Format | Cells

ALT + O | E

Increase Font
Size
Decrease Font
Size
Superscript
Subscript
Language

Format | Cells | Font Tab

CTRL + 1 | F

Format | Cells | Font Tab

CTRL + 1 | F

CTRL + 1 | F
CTRL + 1 | F

Insert WordArt

Insert | Picture | WordArt

ALT + I | P | W

Insert Clip Art

Insert | Picture | Clip Art

ALT + I | P | C

Insert Picture
From File
Line Color

Insert | Picture | From File

ALT + I | P | F

Tools | Formula Auditing | Formula
Auditing Mode
File | Exit

CTRL + `

Right to Left
Sheet Right-toLeft
Insert Text Box

Line Style
Dash Style
Arrow Style
Shadow
3-D
Formula Auditing
Mode
Quit Word

ALT+F4

Table 1.1: Buttons, menu commands and shortcut keys

1.7 Identifying Menus and Commands
Microsoft Excel menus are more or else similar to other Microsoft Office applications except for some menus such as Data menu. Each menu has a list of commands. Each command has a caption letter which has an underline beneath it. This allows user to quickly run/access to that command by pressing the alphabet on the keyboard. Some commands under the menus have shortcut keys. Shortcut keys are commands that can be invoked by pressing the correct keys from the keyboard. They can save time rather then using mouse to navigate the cursor to access the menu and commands. Certain menus have submenus, which expands and displays more commands that are grouped together and they belong to that submenu command.

1.8 Opening Excel Files
After we have identified and understood some of the most important and commonly used menus, commands, toolbars and buttons, we move on to some of the basic tasks that users do. First of all, we look at opening Excel files. To open an Excel file, you can do it by using one of the methods that you have learned from Table 1.1. Once you have run the open command, you should see a dialog box such as the one shown in Figure 1.3.

Figure 1.3: Open Excel file dialog box

This Open Excel file dialog box will be one of the most common dialog box that you will see every time when you use Excel. There are few important parts that you need to know. The Look In list box allows you to drop the list and it shows you the directory of the computer and other drivers. The Look In list is an important component that allows you to quickly navigate through the folders in your hard disk. On the same row, the buttons to the right of the Look In list are Back, Up One Level, Search the Web, Delete, Create New Folder, Views and Tools. Each of these performs a task. For example, the Up One Level button navigates back up one level of the folder that you are currently viewing.

You can see some Excel files at Figure 1.3 which is viewed as Details. These Excel files have a filename, size, type and date modified. They can be sorted in ascending and descending orders.
The most important part of this dialog box is actually the Open button. This button has a drop down arrow that displays more ways of opening a file. The Files of type list can quickly filters and displays the chosen file extensions. To open a file, first you search for it, then click on Open button or double click on it.

1.9 Saving Excel Files
Saving a file is relatively important as to opening a file. Saving a file will store the data and records as a computer file inside the hard disk. There are a few ways to save Excel files.
1) Save command ? This command is usually used when you have already performed the saving command at least once. When you run Save command, it will save all the data into the current file name. 2) Save As command ? This command is usually used when you want to save the Excel data as another file type or with a different file name.

Figure 1.4: Save As dialog box

Figure 1.4 shows the Save As dialog box. It looks similar to Figure 1.3 except the command is different. On top of that, when saving an Excel file, you can specify which type of file you want to save as. Each type of file extension has its usage. The most commonly used types are Microsoft Excel Workbook *.xls and Template *.xlt. We will look at template in the later chapters.

1.10 Closing Excel Files
Once you finished using Microsoft Excel files, you may want to close it. To close an Excel file, you can click on the ‘X’ button of the active workbook (not the application close button which is located on the top-most right hand corner). When you close a workbook that has not been saved, you will be prompt whether you want to save the changes made to the current workbook (file).

Summary
Microsoft Excel 2003 is currently the latest version in the market (2006). Excel is a spreadsheet application that consists of rows and columns to form cells. Many cells build up a worksheet. By combining many worksheets, you form a workbook. There are a few ways to launch Microsoft Excel, open, save and close Excel files. Menus contain lists of commands that run specific
functions. Toolbars are a bar full of commonly used buttons in which each button has a specific function. Shortcut keys are another way to quickly access and run functions of Excel. If one can identify Microsoft Excel user interface, one can easily master the basic of Excel in no time because the user interface of Excel can assist users to quickly understand how to use the application to do a job.

Chapter 2
Developing a Professional Looking
Worksheet
2.1 Create Workbook and Multiple Worksheets
2.2 Enter Text and Numbers
2.3 Use Simple Functions (Sum) and Formulas
2.4 Simple Formatting
2.5 Page Setup and Orientation
2.6 Printing Workbook

OBJECTIVES
In this chapter, student will learn:
?
?
?
?
?
?

To create blank workbook and multiple worksheets
To enter constant text and numbers
To use simple functions and formulas
To apply simple formatting on worksheets
To set-up workbook for printing
Printing workbook

2.1 Create Workbook and Multiple Worksheets
First of all, you ask students to create a blank workbook by launching Microsoft Excel 2003. Once Microsoft Excel 2003 is launched, you should see the screen as shown in Figure 2.1.

Figure 2.1: Blank Workbook

This blank workbook has 3 worksheets which are labelled as Sheet1, Sheet2 and Sheet3. If you want to insert more worksheets into the workbook, you follow one of the two steps below:
1) Right click on Sheet1 and then choose Insert command. The Insert dialog box appears. You should choose Worksheet under the General tab. 2) Click on Insert menu and choose Worksheet command.
To rearrange the new worksheet, click on the new worksheet tab name and then drag it to the new location that you want it to be. But if the workbook has too many worksheets, on top of that some of them are not visible (need to scroll to view the sheet tabs), the easier way to rearrange the worksheet is by right click on the sheet tab that you want to move. Then you choose Move or Copy… command. You select a new location that you want to move the worksheet to.

We can set colours on the sheet tabs and rename the sheet tabs. To delete a worksheet is simple by just right click on the desire worksheet and then choose delete command.

2.2 Enter Text and Numbers
Once you have understood the 3 types of cell entries from Chapter 1, we will begin typing some constant data into Sheet 1 by following the instructions below. 1) Type in Makanlah! @ Limkokwing, Cyberjaya in cell A1.

2) Type in January 2006 Sales in cell A2.

3) Type in the following starting on cell A3.
Stall
Malay
Chinese
Indian
Western
Beverage
Total

Week1
5673
1235
5535
4356
7864

Week2
4324
4215
5643
4256
5889

Week3
3672
6341
4564
6765
6876

Week4
3573
4567
5654

3244
7854

Total

The calculation for the total column and row will be done in the next section. As you type the constant text and numbers into each cell, there are times that you may made mistakes. If you make a mistake, you can double click on the cell with mistake and then correct the mistake.

An alternative way to edit a mistake is by selecting the cell with mistake and then click on the formula bar to correct the mistake. You must have noticed that the formula bar contains the text/numbers that you type in. The formula bar is an important part of Excel that you can see the contents of the cells. You should have something that looks like in Figure 2.2.

Figure 2.2: Text and number constants

2.3 Use Simple Functions (Sum) and Formulas
Recall back Chapter 1, formula begins with an equal sign whereas a function is a computational task. The next step next is to use a function to add up all the values of a column/row and place the total values on the Total column and row. The function that adds up all the values in the cells is the Sum function. Follow the steps below to calculate the totals for each category.

1) At cell F4, type in =sum(A4:E4). Press Enter.
2) At cell A9, type in =sum(A4:A8). Press Enter.
Once you have done that, you will see the total value in those 2 cells. When you select cell F4, you can see the Sum formula inside the formula bar. Ok, so far only 2

cells contain the Sum function. An alternative way to do it is to use the
AutoSum button. It is quicker because the Sum function has been chosen as the default function to calculate a range of cells.

However, it can be very tiring to repeat the typing/calculating the same function and formula onto the adjacent cells. To quickly sum up the values for the other groups, the formulas inside the cells can be copied to adjacent cells by using the fill handle.

The fill handle is a small black square located in the lower-right corner of the heavy border around the active cell. Position the mouse cursor over the fill handle and it will turn into a cross hair. Left click and drag to adjacent cells to copy the formulas onto other cells.

The other cells in the Total column and row contain the Sum function and formula. You will notice that the cell reference for cell F4 is different from cell F5, F6, F7, etc. The cell reference that changes in this manner is known as relative cell reference. The formula in cell F9 can be either =sum(F4:F8) or =sum(B9:E9). In the end, both formulas produce the same total value. After following the steps above, you should get a similar result as shown in Figure 2.3.

If you notice that there’s a colon ( : ) in between F4 and F8, it indicates a range of cells whereby F4 is the first cell reference and F8 is the last cell reference.

Figure 2.3: Added formulas

2.4 Simple Formatting
Once you have completed entering the text, numbers and formulas into the worksheet, the next step is to enhance the look of this worksheet ? that is to do formatting. Follow the instructions below to format the list.

1) Select cell A1 to F1. Click on Merge and Center button from the
formatting toolbar. Click on Bold button and change the font size to 14. Change the background colour to blue-grey by clicking on Fill Color. Finally, change the font colour to white colour.

2) Select cell A2 to F2. Click on Merge and Center button from the formatting toolbar. Click on Bold button and change the font size to 14. Change the background colour to blue-grey by clicking on Fill Color. Finally, change the font colour to red colour.

3) Select cell A3 to F3 and bold the column titles. Then you add a bottom border immediately.
4) Select cell B3 to F3 and then align right the titles.
5) Select cell A4 to A9 and bold the row titles. Then you add a right border and a left border immediately for cell A3 to A9.
6) Adjust the column A width to 120 pixels.
7) Select cell A4 to A8 and click on Increase Indent button once. 8) Select cell A9 to F9 and add a top and double bottom border. 9) Select cell F3 to F9 and add a right border.
10) Select cell B9 to F9 and add a customize currency format by accessing Format Cells
Number Tab
Custom option. Type in the following onto
the Type: field
“RM”0.00
Then you click on Decrease Decimal button twice and then align right the totals.
11) Select cell B4 to F8 and then align right the digits.
After you have completed these 11 steps, you should see a list as shown in Figure 2.4.

Figure 2.4: Formatted table

2.5 Page Setup and Orientation

After you have finished formatting the table, you proceed on to the next phase which is page setup. Page setup command is accessed from File menu. Page setup deals with page settings of the current active worksheet or selected worksheets. The Page Setup dialog box has 4 tabs: Page, Margins, Header/Footer and Sheet. The Page tab has orientation, scaling, paper size, print quality and page number settings. The important part of this tab is the printing orientation, scaling and paper size. If in any case that your worksheet is very big in which the print out will expand to multiple pages with scaling set to 100%, you may need to print everything on one paper. Thus the Fit to option is very useful that is if you do not know what percentage to use for the Adjust to setting.

The Margins tab has settings to adjust the worksheet from the edge of the paper, as well as the Center on page settings. Bear in mind, the margins values depend on your Excel settings, which can be in centimeters, inches, etc. The Header/Footer tab contains the settings to include header and footer to the selected worksheet(s). Headers and footers are located at the top and bottom margins of the print outs. The header and footer in Excel are different from Microsoft Word and PowerPoint whereby in Excel, there are custom header and footer that have 3 sections: left, center and right. Since they are customizable, you can add almost anything such as time, date, text, file location, page number, pictures, etc. The Sheet tab allows you to set your print area, rows and columns to repeat on top and left, and other printing settings. However, usually the most important usage of this tab is the print area and repeat printing titles over multiple pages. You will learn this soon in the coming chapters when you are working on large worksheets. Now, you continue your Excel worksheet by accessing the Page Setup command. The settings that you need to adjust are:

12) Page orientation: Landscape
13) Paper size: A4
14) Center on Page: Horizontally
15) Margins: Top & Bottom

2.5, Left & Right

1.9, Header & Footer

1.3

16) Custom header
Center section: Type Chapter 2: Developing a
Professional Looking Worksheet
17) Custom footer

Left section: Type the following in 3 lines:

Last updated on
&[Date]
&[Time]
18) Custom footer

Right section: Insert Page number

2.6 Printing Workbook
Once you have completed the workbook settings, it is time to print out the worksheet (since there’s only one worksheet with the table). Always remember to use the Print Preview command to check how your worksheet will look like before you initiate printing command. If you think that the layout is not properly align or there’s something missing or incorrect, you can make the correction on the worksheet after you use print preview command.

After you have already checked and confirmed the table, information, formatting and worksheet settings are correct, you can print out the workbook. Printing is simple and there are two ways to do it: Print button on Standard toolbar, or press CTRL+P.

Summary

Microsoft Excel workbook is a virtual spreadsheet book file that can include multiple worksheets. Each worksheet contains cells that make up rows and columns. When you start working on Excel workbook, you should plan out the content for the number of worksheets you will use, data to enter, layout, design, formatting, macros, charts and page setup.

After planning, you should begin adding worksheets to the workbook. The second step is to type in the raw data, text, and numbers and then include pictures (if necessary). After that, you should add functions and formulas to appropriate cells of the entire workbook. The functions and formulas may appear any part of the worksheet and span across multiple worksheets, if deem necessary. Moving on, formatting stage comes into the picture. Formatting is the fourth phase once you have most of the information placed into the worksheets. Information will be more useful and readable after you apply formatting. Charts and PivotTable and PivotChart (you will learn PivotTable and PivotChart in the later chapters) follows suit; and you may need to format the charts and tables along the way. After formatting, you will need to adjust the page setup settings of the worksheets or workbook (depending on the work you are working on). Printing comes last.

It is recommended to follow the stages (mentioned above) as closely as you can. Nevertheless, you are free to use Excel in any way you want, as long as you can achieve the results you want at the end of the day.

Chapter 3
Enhancing Worksheet with More
Formatting and Adding Cell
Reference
3.1 Template
3.1.1 Student Time Table Template
3.2 Cell Reference
3.2.1 Relative, Absolute & Mixed Cell References
3.2.2 3-D Cell Reference

3.2.3 Consolidating Cell Reference
3.3 Advance Formatting Cells
3.3.1 Conditional Formatting
3.4 Navigating In Excel

OBJECTIVES
In this chapter, student will learn:
?
?
?
?
?

To create and use template
To understand and use relative, absolute & mixed cell references To understand and use 3-D cell reference
To consolidate date through cell reference
To do more formatting on cells

3.1 Template
In Excel, Template is a workbook that has already being made with preferred formatting, minor content but contain no raw data. Template can be reused again and again, as many times as possible. In many cases, template does not change in design and it can be done so by locking and protecting cells. You can think of Excel template, for example like an ordinary empty form that you fill up your personal details for creating a bank saving account.

Now, we will look at how to create a useful student time table using Excel and then save it as a template.

3.1.1 Student Time Table Template
Please follow the instructions carefully to ensure that you create an accurate student time table template.

19) In cell A1, type in . We use the angle brackets
? < > to indicate that user can replace the content on this cell. 20) From cell B2 to C2, type in 1 and 2 respectively. Then you select cell B2 and C2 and then point to the fill handle. Click on the fill handle and drag to cell J2.

Figure 3.1: Drag fill handle

21) From cell B3 to J3, type in 9:00 – 10:00, 10:00 – 11:00, 11:00 – 12:00, 12:00 – 13:00, 13:00 – 14:00, 14:00 – 15:00, 15:00 – 16:00, 16:00 – 17:00, 17:00 – 18:00 respectively.

Figure 3.2: Duration entries

22) Enter Monday and Tuesday onto cell A4 and A5 respectively. Then you use the fill handle to copy the consecutive days up to cell A9.

Figure 3.3: Days copied with fill handle

Once you have completed these 4 steps of entering text and number constants, the second step is to modify the appearance of this time table, which is formatting. 23) Select cell A1 to J1 and then merge and center. Then change the font to Arial Black and change the size to 24. While you are still selecting the merged cell, access Format Cells

Border tab. Choose the thick line
(right column second last style) and then click on Outline button under Presets.
24) Select cell B2 to J2, align center the numbers, change font size to 16 and then bold them,
25) Select cell B3 to J3, align center the durations and bold them. 26) Select column heading B to J and then adjust the column width to 110 pixels. Adjust the column heading A to width of 120 pixels.

27) Select row heading 4 to 9 and then adjust the row height to 100 pixels. 28) Select cell A4 to A9 and then access Format Cells dialog box. On the Alignment tab, choose Center for Horizontal and Vertical. Then view Font tab and change the font size to 11 and bold the contents.

Figure 3.4: Formatted time table

29) Now we will add more borders to this table. First of all, select cell A2 to J9 and then access Format Cells dialog box. At Border tab, use the same border style and then click on Outline and Inside buttons.

30) Then select cell A2 to J3 and then access Format Cells dialog box again. At the Border tab, click on the button where the arrow is pointing at as shown in Figure 3.5. Then click Ok. The purpose is we want to remove the horizontal line that divides the numbers 1 to 9 and durations.

Figure 3.5: Remove horizontal line inside

31) Lastly, you type each alphabet of BREAK! into cell F4 to F9. At Format Cells dialog box, on the Alignment tab, choose Center for Horizontal and Vertical. Then view Font tab and change the font size to 22 and bold the contents.

Once you have completed all these steps, you should get a template design that looks like Figure 3.6.

Figure 3.6: Student time table template

Even though you may have completed this template design on the worksheet, we will need to modify a little bit more on the file. Access Page Setup and
then change the orientation to landscape. Make sure the paper size is A4. Next, we make sure that the margins settings are 2.5 for top and bottom, 1.9 for left and right, 1.3 for header and footer, and finally Center on Page Horizontally.

At Header/Footer tab, click on Custom Header. At the center section, click on edit Font button, change the font type to Batang, Bold and size 14. Then you type in My Time Table. After that, you click on Custom Footer. At the center section, you type in Last Updated: and then insert date and time.

You should have a print preview that looks like Figure 3.7.

Figure 3.7: Time table print preview

Before you save the file, you delete all other worksheets except the one with our time table template. Then you rename the sheet tab name to Time Table. Lastly, you save this workbook as a Template file type. The extension of an Excel Template is *.xlt. There are many kinds of template you can create and reuse them later such as accounting balance sheet, forms, mathematic formula worksheets, just to name a few.

3.2 Cell Reference
You have already learnt cell reference from previous chapter. Recalling back about cell reference, a column letter and a row number identifies a cell because of the intersection. You can identify an active cell reference from the Name Box. Now we will look at the 3 types of cell references and their definitions.

3.2.1 Relative, Absolute & Mixed Cell References
Cell
Reference
$A$10

Type of
Reference
Absolute
reference

Meaning

A$10

Mixed reference

$A10

Mixed reference

A10

Relative
reference

Both column and row references remain the
same when you copy this cell reference
because they are absolute.
This cell reference is mixed. The column
reference changes when you copy this cell
reference to another column because it is
relative. The row reference does not change
because it is absolute.
This cell reference is mixed. The row
reference changes when you copy this cell
reference to another row because it is
relative. The column reference does not
change because it is absolute.
Both column and row references are relative.
When copied to another row and column,

both the row and column in the cell reference
are adjusted to reflect the new location.

Table 3.1: Cell References

Next, we will see how to use these 3 types of cell reference in Excel. We will create a simple table of staff salary list. Please follow the instructions below to create the list. 1) In cell A1, type in Global Tri-Tower Corp.

2) In cell A2, type in November – Marketing Employees’ Salaries List. 3) In cell A4 to H4, type Staff ID, First Name, Last Name, Age, Gender, Base Pay, Commission Earned, Total Income.
4) Starting from row 5 onwards, please enter the data accordingly by following the table below.
MT01
MT02
MT03
MT04
MT05
MT06
MT07
MT08
MT09

James
Bob
Tony
Grace
Taphelo
Ireen
Muhammad
Osamah
Michelle

Brook
Arakton
Leong
Rabasha
Maseko
Tracey
Saeed Abu
Abdul Bashar
Jefferson

29
23
22
47
36
35
34
49
34

Male
Male
Male
Female
Male
Female
Male
Male
Female

4000
2100
2100
5000
3800

4500
3500
5000
4500

Table 3.2: Global Tri-Tower Corp employees’ salary list

5) Once you have completed this list, in cell A15, type Commission Rate. 6) Type in 8% into cell B15.

7) Now, we will begin the formulas and calculations for this list. In cell G5, type the formula of =F5*$B$15
8) In cell H5, type the formula of =F5+G5
9) Select cells G5 and H5 and then use the fill handle to copy down the formulas to row 13.
If you do not use absolute cell reference but relative cell reference in step 7, as you copy down the formula to the next few rows, you will get 0 values. This is because say for cell G6, the formula would be =F5*B16 which is completely wrong! 10) Now, we will do formatting to this list. First of all, we merge and center A1 to H1. Then we increase the font size to 24, change Arial font to Abbey Medium Extended font and bold the title. Adjust row 1 height to 70 pixels. Lastly we change the background colour of cell A1 from white to yellow. 11) Next, click on Format Painter (make sure A1 is selected) and then click and drag from A2 to H2. Change the font size from 24 to 16 on cell A2. 12) Display Drawing toolbar. Select A1 and A2, click on Shadow Style button on Drawing toolbar and click on Shadow Style 6.

13) Change the column titles from Arial font to Verdana font. 14) Now we will adjust the row and column height and length. First of all, adjust row 2 to 50 pixels. Adjust column A to 110, column B & C to 130, column D to 45, column E to 75, column F to 100, column G & H to 125. 15) Click on row 4 heading and right click to access Format Cells. Go to Alignment tab and tick Wrap Text option. Add Wrap Text feature to cell A15 as well.

16) Bold and add a bottom border to cell A4 to H4.
17) Add a currency to cell F5 to H13 by clicking on the Currency button from the Formatting toolbar. The currency can be any country’s currency format.
18) Add a green colour background to cell A15 and B15, change font colour to yellow, bold the cells, align left and lastly add Shadow Style 6. 19) Access Page Setup, change orientation to landscape and center on page horizontally and vertically.

After completed these 19 steps, you should produce a worksheet that looks like Figure 3.8.

Figure 3.8: Global Tri-Tower Corp salary list normal worksheet view

Press CTRL+` to switch the normal worksheet view to formula worksheet view or Formula Auditing Mode. A formula worksheet view should look like Figure 3.9 after you have adjusted the column width.

Figure 3.9: Global Tri-Tower Corp salary list formula worksheet view

The formula worksheet view is an important view to show Excel users of all the visible cells’ contents. If you select a cell, you will see what was typed onto the cell that appears on formula bar will be similar on the worksheet. The more important usage of Formula Auditing Mode is to keep track on formulas, dependencies of cell references and the locations of cell references of a particular formula on a selected cell.

Figure 3.10: Cell referencing colours of a formula

3.2.2 3-D Cell Reference
So far the cell references that you have seen and used so far are actually
“2-D” cell references. Why we call it as 2-D? Simple, the cells that you refer to have X-axis and Y-axis which are column and row. We will now look at what is 3-D cell reference. In Excel, a 3-D cell reference is indicated by worksheet name followed by cell reference. An exclamation mark ‘!’ separates worksheet name from cell reference. In order to do a simple 3-D cell reference in a workbook, the workbook must have at least 2 worksheets. E.g. the workbook has Sheet1 and Sheet2. In Sheet1, cell A1 has value of 20. Select cell B2 in Sheet2 and type in =Sheet1!A1 and press Enter key.

An alternative way to achieve this is by copying cell A1 and then you right click on cell B2 in Sheet2. After that, you choose Paste Special and then click on Paste Link button. Paste Link command automatically add a cell reference. In this example, the cell reference is a 3-D cell reference. If you make any changes to A1 in Sheet1, B2 in Sheet2 will immediately change. We will see how to use 3-D cell reference in the next topic – 3.2.3 Consolidating Cell Reference.

3.2.3 Consolidating Cell Reference
Once you have understood the idea of cell reference and 3-D cell reference, we will apply the concepts onto the previous worksheet (we hope that you have saved the file). To consolidate data, you combine the values from several ranges of data, which you will need to use different types of cell references.

1) We will start off by renaming Sheet1 to November. Then we delete other worksheets.
2) Right click on November sheet tab and then choose Move or Copy option. We chose move to end option and create a copy.
3) Create another copy of November sheet tab and move it to the end. 4) Rename the first copy to December and the second copy to Summary. 5) View December worksheet. Change the November in cell A2 to December and change 8% to 11%.

6) View Summary worksheet. Select F and G column heading, right click and choose delete.

7) Delete row 15.
8) Change cell A2 content to Marketing Employees’ Salaries Summary. 9) In cell F5 of Summary worksheet, you can follow one of the two steps to consolidate data.
a) Type =SUM(, then click on November sheet tab, click on cell H5, press and hold down Shift key, click on December sheet tab and then type ) to close the formula. Press Enter key.

b) If you know the cell references and worksheet names, you can type =SUM(November:December!H5).
10) Either method will produce a value of 8,760.00 in cell F5. 11) Lastly, add a Sum function at cell F14 and then add a top and double bottom border.
Recall back Chapter 2, the colon ( : ) that situates in between of 2 cell references indicates that this is a range of cell references. The colon applies the same concept to worksheet names. The formula above uses Sum function to add up the value of H5 of November with H5 of December. In other words, the Sum function adds up all the values of H5 cells from the range of worksheets, starting from November till December.

A word of warning though, doing this kind of consolidate data can cause circular reference if you are not careful. How it may happen, consider the following example. A workbook has 3 sheets whereby sheet1 and sheet3 cell A1 has value of 10 and 20. In sheet2 cell A1, you do a consolidate data formula of =sum(sheet1:sheet3!A1). If you look at the formula carefully, it is easily understood that cell A1 from sheet1 to sheet3 will be added up together. On closer inspection, if cell A1 of sheet2 contains this formula, it means sheet1!A1 + sheet2!A1 + sheet3!A3 and the value is placed onto sheet2 of cell A1. It does not make any sense at all! Thus, it is called circular reference.

Lastly, consolidate cell reference can be almost any kind of formulas as long as it involves more than 1 type of cell references. Consider the following formula that is placed onto cell C5 of Sheet1.

=A1 + Sheet2!A4 * Sheet3!$A$1
The formula above is an example of consolidate cell reference formula.

3.3 Advance Formatting Cells
We will look at a few formatting settings that may enhance your worksheets. As you know, we can use the buttons at the Formatting toolbar to do simple formatting on selected cells. Excel offers Format Cells feature to enhance the cells further. There are a few ways to access to Format Cells dialog box.

1) Format Cells can be easily access by pressing CTRL+1 keys together. 2) You also can access it by pressing Format Cells button on the Formatting toolbar.
3) You can access it from Format menu

Cells…command.

4) Lastly, you can right click on selected cell(s) and then choose Format Cells command.
Firstly, we look at the Number tab. It offers a lot of commands and functions that manipulate digits. There are 12 different types of categories as shown in Figure 3.11.

Figure 3.11 Number tab

The following table shows the general description of each category of Number tab.

Number Formats Comparison
Category

Description

General

Currency

No specific number format
Default of two decimal places; can also display
commas for thousand separators
Aligns currency symbol, displays two decimal
places and comma separators
Displays serial equivalent of date
Displays serial equivalent of time
Multiplies value by 100 and displays the result
with % sign
Displays decimal portion of value as a fraction
Displays the number in scientific notation
Text format cells are treated as text even when
a number is in the cell. The cell is displayed
exactly as entered

Accounting
Date
Time
Percentage
Fraction
Scientific
Text

Default Display
(Value – 1054.253)
1054.253
$1,054.25
$

1,054.25

11/19/1902
11/19/1902 6:00AM

105425%
1054 1/4
1.05E+03
1054.253

Table 3.4: Number formats comparison

So far, these comparisons just show the basic of the number formats. Certain categories have additional settings to allow you to choose more types for that particular category. When we use them in the correct situation, they can be a powerful formatting tool. The Text category is useful in certain situations such as displaying the exact input of a Malaysian IC inside a cell. The Special category is uncommon because it depends on what kind of work you are working on. Special formats are useful for tracking list and database values of different countries formats. The Custom format is a versatile category that allows user to create almost any kind of formatting to selected cell(s). The following table describes the format codes of Custom format.

Custom Format Table
Format Code Use
#
0
?
,
*
“text”

_
;
,
, =
[Color]

Displays significant digits, but not insignificant zeroes
Displays insignificant zeroes if a number has fewer digits than the number of zeroes specified in the format
Adds spaces for insignificant zeroes to line up decimals; also used for fraction formats with varying numbers of digits
Thousands separator
Repeats the next character in the format code to fill any blank spaces in a cell
Inserts any text within the quotes as part of the number format Displays a single character as part of the number format
Inserts a space character in the number format
Separates sections of a custom number format; each format can have four sections to format positive, negative, zero, and text values. Conditional operators that apply the custom format only if a numerical value meets the specified condition

Use one of eight colors (Black, Blue, Cyan, Green, Magenta, Red, White, and Yellow) to displays values; colors must be listed first in a section
Used as the last entry in a custom number format to display text; if this symbol is omitted from the custom format, any text entered is not displayed
Table 3.5: Custom format

Next, we turn our attention to the Alignment tab. This tab offers something that will turn a plain dull text orientation to something unique. This tab has text alignment, text control, text direction, and text orientation as shown in Figure 3.12. The text alignment is common feature that aligns text properly.

The text control has 3 functions, wrap text, shrink to fit and merge cells. •

Wrap text – This feature moves sentence/text that is too long onto the next line of the same cell. It is different from pressing ALT+ENTER to shift the text to the next line.

•

Shrink to fit – This feature shrinks/reduces the font size that will fit nicely according to column width.

•

Merge cells – This feature merge selected cells into one cell. It is part of the Merge and Center command.

Consider the orientation of degrees. It is useful to rotate a text from 90° to -90°. But you have to use this 180° degree of orientation with care because if you apply this effect to cells that have borders, you will see something unpleasant. For example, we apply 45° to Monday till Saturday cells of Student Time Table template, it will look like this (refer to Figure 3.13).

Figure 3.12: Alignment tab

Figure 3.13: 45° orientation
on cells with borders

The vertical text orientation can be very useful for Asian languages and text such as Chinese, Korean and Japanese writings. For example, we can change the B R E A K ! to Lunch Break for each cell, reduce font size, wrap text and then use vertical text orientation. It will look like Figure 3.14.

Figure 3.14: Vertical text orientation

Now we look at Font tab. The Font tab allows user to manipulate font in many
ways. This tab has a Preview section to display the changes of the font settings. The Underline section has 4 different types of underline, which is divided into 2 groups: accounting and non-accounting styles. You can change font colour on this tab as well.

Figure 3.15: Font tab

The fourth tab is the Border tab as shown in Figure 3.16. Naturally, this tab allows user to manipulate the borders of selected cells. The Presets section has 3 buttons that quickly add or remove borders to the cells. The Border section works together with Line Style. Border section allows you to customize the border appearance. The Line Style has various lines designs. To add borders to cells, choose a line style first, and then you click on the buttons either on Presets or Border. You can add colours to the borders as well. Figure 3.17 shows the content of Patterns tab. This tab allows user to add colours to cell background or patterns to cell background. A rectangle preview box shows the sample of the chosen colour shading or design pattern. In many cases, not many people use pattern with text because you can hardly read the text if the font size is too small.

Figure 3.16: Border tab

Figure 3.17: Patterns tab

The last tab is the Protection tab. This feature allows user to protect/lock cells of an Excel workbook. This tab allows the user to specify whether the selected cell(s) should be locked or hidden from view.

3.3.1 Conditional Formatting
So far, all the formatting that we have seen and used is just the normal formatting that we apply manually. In Excel, there is conditional formatting
that the cells will change its format the moment it meets certain condition. In other words, the condition is true and then the format change accordingly. Let’s have a look at the following example of how to use conditional formatting. Say, we want to apply red colour background to cells that contain number less than 50. We apply green colour background to cells that contain number greater than 89. Look at Figure 3.18 and type in those data into those cells.

Figure 3.18: Student marks list

Then, you select cell B2 to B4 and then click on Format menu Conditional
Formatting command. You should see the Conditional Formatting dialog box (Figure 3.19).
Operator

Preview format

Conditional format
Figure 3.19: Conditional Formatting

The left most drop down list is the type of condition format you want to apply to selected cells. The second drop down list from the left is the operator. It is available when the conditional format is Cell Value Is option. The fields to the right vary when you choose different type of operator. Those fields can contain text, digits and cell references. The “No Format Set” white box is to preview the format to use when condition is true. The Format button, when clicked, it will bring up the Format Cells dialog box with 3 tabs only: Font, Border and Patterns. The Add button allows you to add up to 3 conditions to selected cells. The Delete button is to delete 1 – 3 conditions from selected cells. Right now, we will add in condition format onto the cells. First of all, we change between operator to less than operator. Type 50 into the field. After that, we add another condition and change the operator to greater than and the value is 89. Next, click on the first Format button and then change the cell shading to red colour. Click Ok. Lastly, click on second Format button and then change the
cell shading to green colour and bold it.

After that, you type in 10, 50 and 100 into the three cells to see the results for yourself. Conditional formatting is useful when you want Excel to highlight cells that meets a condition.

3.4 Navigating in Excel
So far, you must have navigating in Excel using a few simple key strokes on the arrow keys and mouse clicks. However, there are times that it is more useful to navigate Excel workbook using the keyboard. The following table describes the navigation keys.

Navigating in workbook using the keyboard
To move

Press

Up one cell
Down one cell
Right one cell
Left one cell

The UP ARROW key
The DOWN ARROW key
The RIGHT ARROW key or TAB key
The LEFT ARROW key or SHIFT+TAB
keys
The HOME key
The END key and then the ENTER key
The PAGE DOWN key
The PAGE UP key
The CTRL+HOME keys
The CTRL+END keys or the END key and

then the HOME key
The CTRL+ARROW keys

To first active cell of the current row
To the last active cell of the current row
Down one page
Up one page
To cell A1
To last cell containing data in a
worksheet
To edge of the last cell containing a
value or to the edges of a worksheet

Figure 3.6: Navigation keys

Summary
Template is a very useful file that allows user to reuse it over and over again without the need to create the same worksheet design and formatting. There are 3 types of cell references and each of it serves a purpose. 3-D cell reference refers a cell from another worksheet. Consolidate data cell reference involves many different types of cell references to perform a calculation to become a formula. By learning and understanding Format Cells usage, one can manipulate and format the entire worksheet to look more presentable. Conditional formatting is useful because you set the conditions that you let Excel change the formatting automatically when conditions are met. Last but not least, navigating around a workbook using keyboard can be faster than using mouse clicks.

Chapter 4
Graphs and Charts
4.1 What is a Chart?
4.2 Chart Types
4.3 Creating a Chart using Chart Wizards

4.4 Choose Chart Type
4.5 Check Data Series
4.6 Complete Chart Options
4.7 Chart Location
4.8 Modifying Chart
4.9 Enhancing Chart
4.10 Moving and Resizing Chart
4.11 Multiple Data Series
4.12 Object Linking and Embedding

OBJECTIVE
In this chapter, student will learn:
Describe how a chart can be used to deliver a message
List several types of charts and describe the purpose of each Distinguish between an embedded chart versus a chart in its own sheet Use the Chart Wizard to create and modify a chart
Use the Drawing toolbar to enhance a chart by adding lines and objects Distinguish between data series in rows versus columns
Differentiate between a stacked-column chart versus a side-by-side column chart
? Create a Word document that is linked to a worksheet and an associated chart

?
?
?
?
?
?
?

4.1 What is a Chart?
A chart is a graphical representation of data in a worksheet. Charts are visually appealing and make it easy for users to see comparisons, patterns,
and trends in data. For instance, rather than having to analyze several columns of worksheet numbers, you can see at a glance whether sales are falling or rising over quarterly periods, or how the actual sales compare to the projected sales. Charts have the following elements: •

•
•

Category labels – descriptive text entries
Data points – numeric values
Data series: a grouping of data points

We will look into the three elements in a while. Next, we move on to the chart types.

4.2 Chart Types
There are many types of charts available in Excel:
•
•
•
•
•
•
•
•
•
•
•
•
•
•

Column
Bar
Line

Pie
XY (Scatter)
Area
Doughnut
Radar
Surface
Bubble
Stock
Cylinder
Cone
Pyramid

Each type has its own characteristics and usage. Therefore you have to choose and use the appropriate chart type to represent your data. For example, you use pie and exploded pie charts display proportional relationships; you use column charts display numbers rather than percentages; bar charts display numbers horizontally. No matter what type of charts you want to include into Excel workbook, remember to keep it simple. The following shows the design and looks of charts.

3500
3000
2500
Beverages
Food
Accessories

2000
1500
1000
500
0
1st Qtr

2nd Qtr

3rd Qtr

4th Qtr

Figure 4.1: Column chart

4th Qtr
3rd Qtr

Accessories
Food
Beverages

2nd Qtr
1st Qtr
0

1000

2000

3000

4000

Figure 4.2: Bar chart

3500
3000
2500
Beverages
Food

Accessories

2000
1500
1000
500
0
1st Qtr

2nd Qtr

3rd Qtr

4th Qtr

Figure 4.3: Line chart

Total Profit/Loss

RM1236.00,
24%

(RM530.00),
10%
RM432.00, 8%

1st Qtr
2nd Qtr
3rd Qtr
4th Qtr

RM3049.00,
58%

Figure 4.4: Pie chart

8000
6678

6000
4736

4000
2000

3232

2656

2363
1654
1263

0
0

2
Beverages

2146

1320
324

1142

233

3
Food

Accessories

Figure 4.5: XY (Scatter) chart

10000
8000
Accessories
Food
Beverages

6000
4000
2000
0
1st Qtr

2nd Qtr

3rd Qtr

4th Qtr

Figure 4.6: Area chart
Total Income

1st Qtr

2nd Qtr
3rd Qtr
4th Qtr

Figure 4.7: Doughnut chart

Beverages
8000
6000
4736
3232
4000
1st Qtr
2nd Qtr
3rd Qtr
4th Qtr

1263
2000
233
0
324
1142
1320
1654
2146
2363
2656
6678
Accessories

Food

Figure 4.8: Radar chart

8000
6000
4000
2000
Accessories

6000-8000
4000-6000
2000-4000
0-2000

Food

1st Qtr
2nd Qtr

Beverages

3rd Qtr
4th Qtr

Figure 4.10: Surface chart

7000
6000
5000
Beverages
Food
Accessories

4000

3000
2000
1000
0
1st Qtr

2nd Qtr

3rd Qtr

4th Qtr

Figure 4.11: Cylinder chart

7000
6000
5000
Beverages
Food
Accessories

4000
3000
2000
1000
0
1st Qtr

2nd Qtr

3rd Qtr

4th Qtr

Figure 4.12: Cone chart

7000
6000
5000
Beverages
Food
Accessories

4000
3000
2000
1000
0
1st Qtr

2nd Qtr

3rd Qtr

4th Qtr

Figure 4.13: Pyramid chart

4.3 Creating a Chart using Chart Wizards
Chart Wizard is a step by step instruction to guide user to create a chart. Now, we will begin to create a chart based on Chapter 2 Makanlah! Limkokwing, Cyberjaya – January 2006 Sales workbook. Please follow the instructions below to create a column chart. 5) Select cell A4 to E8 and then click on Chart Wizard button on Standard toolbar.

6) Choose the highlighted sub-chart type as shown in Figure 4.13. Then click Next.
7) In Step 2, view Series Tab. Then click on Series1 and then click on Name
field. While the cursor is blinking on the Name field, you click on cell B3. 8) After that, you click on Series2 and you will see that Series1 changed to Week1. You continue repeating the same instructions as above except you click on C3, then D3 and lastly E3 for Series2, Series3 and Series4 respectively. Once you are done, click Next.

9) In Step 3, you add a Chart Title that is exactly similar to table title. Type Stall for X axis and Value for Z axis.

10) Click on Legend tab and place the legend at the bottom. 11) Click on Data Labels and select Value option. Then click Next. 12) Place as object in Sheet1 for the last step of Chart Wizard. Choose any

one of the
available subtype, but
changes
depending on
chart type

Select any
of the chart
types or
click
Custom
Types to
create your
own

Description
about chart
sub-type

To quick

preview your
chart

Figure 4.14: Chart Wizard step 1

Once you have completed these Chart Wizard steps, you should get a chart that looks like Figure 4.14. It is very hard to read the chart because the whole chart is too small. Thus, we need to format the entire chart object.

Makanlah! @ Limkokwing, Cyberjaya
January 2006 Sales
10000
7854
6341 5654 7864
5643 67656876
4324 4567 4356 5889
Value 50005673 4215 4564 3244
3672 5535 4256
3573
1235
0
Malay
Western
Stall
Week1

Week2

Week3

Week4

Figure 4.14: Unformatted column chart

Usually, we do not want to place the chart as object onto an existing
worksheet but preferably place it on a new worksheet. It is all up to you to modify the chart to the way you want it to look like. A formatted column chart is shown in Figure 4.15.

M
akanlah! @ Limkokwing, Cybe rjaya J anuary 2006 Sale s
8000

6876
5889

6765
3244

4356

4256

5654
4564

5535

5643

4567

4215

3672

3573

5673

4324

5000
V
a
l 4000
u
e
3000

6341

6000

7854

7864

Best stall among the rest
7000

1235

2000
1000
0
Malay

Chinese

Week1

Indian

Stall
Week2

Week3

Western

Beverage

Week4

Figure 4.15: Formatted column chart

4.4 Choose Chart Type
Referring to Figure 4.13, there are 2 tabs, Standard Types and Custom Types. Standard Types contain all the chart types mentioned in 4.2 Chart Types. Each chart type has several sub-types. Each sub-type has its description and design. You need to choose an appropriate sub-type for your data. You use the Press and Hold to View Sample button to quickly preview how your chart may look like. If all you see is a blank screen, cancel and reselect data.

4.5 Check Data Series
Figure 4.16 shows Chart Wizard Step 2 of 4. In this step, we check the data series and make any necessary adjustment to the data that we previously selected. There are 2 tabs, Data Range and Series. The data range shows you the 3-D cell reference of the range of data you have selected before you click on Chart Wizard button. If you need to adjust the data range, you click on the collapse button and then view back the workbook and choose the data range again. The Series in section displays the values in bars depending on which series you choose, row or column. In Figure 4.16, we chose columns and it will display 4 different keys colours on the legends because we have 4 columns of data. Below the chart, the X-axis labels are based on the different row headings. If we have chosen rows, then we would have 5 different keys colours on the legend and then the Xaxis labels will be based on the different column headings. However, you may have noticed
that the legend keys names are not meaningful at all. Therefore, we need to change them into the correct names by viewing Series tab. By looking at Figure 4.17, we can see Series section, Name field, Values field and Category (X) axis labels section. The Series section shows you the name of the legends. If they are incorrect, you need to change them one by one by selecting the series and then type in / insert 3-D cell references to the Name field. Each of series has a range of values and it is usually 3-D cell references. Lastly, the category axis labels vary depending on the type of

chart you have chosen in step 1. Sometimes it is X-axis, sometimes it is Y-axis and sometimes it is Z-axis.

Preview the chart
before going further

Bars appear depends on
choosing data based on
columns or rows

The range of cells
we selected
before clicking on
Chart Wizard

Collapse button hides
the dialog and allows
you to select a
different range

Figure 4.16: Chart Wizard step 2 data range tab

Default series
names for legend
that need to be
changed

Label the series here
X-axis labels

Range of values of selected series

Cell reference of X-axis labels

Figure 4.17: Chart Wizard step 2 series tab

4.6 Complete Chart Options
Step 3 is the Chart Options. This step has 6 tabs (but it may varies from different chart type), Titles, Axes, Gridlines, Legend, Data Labels and Data Table. The Titles tab allows you to label the chart title and the axes. The Axes tab allows you to choose which axis as the primary and to show/hide values of Y/Z axis. The settings in this tab also vary depending on the chart type. Gridlines tab allows you to show/hide major and minor gridlines of category X, series Y and value Z axes. The Legend tab allows us to show/hide legends and adjust its placement. The Data Labels tab shows us to add labels to the chart. We can show series name, category name, value, percentage and bubble size; depending on what kind of chart we have used. The last tab is Data Table tab that either show or hide the data table below the chart. Usually, we do not add data table to charts.

4.7 Chart Location
This is the last step of Chart Wizard. You can either place the chart as new worksheet or place it on an existing worksheet. However, placing on existing worksheet may make the chart looks small and difficult to read.

4.8 Modifying Chart
Once a chart has been placed onto a new worksheet or as an object on an existing worksheet, sometimes you may want to modify it. There are many ways to modify a chart and we can use the Chart toolbar to modify it. The Chart toolbar has several buttons (from left to right): Chart Objects, Format Chart Area, Chart Type, Legend, Data Label, By Row, By Column, Angle Clockwise and Angle Counter Clockwise. It is much easier to modify the chart by using this toolbar rather than clicking and right clicking on chart labels to modify them. It is important to ensure that the data labels are readable if you include them on the chart because it would beat the purpose to place them there unreadable.

Figure 4.18: Chart toolbar

4.9 Enhancing Chart
No chart can be perfect after gone through the 4 steps of Chart Wizard. Therefore we need to enhance the chart appearance more presentable. You can enhance a chart by using the Drawing toolbar (shown in Figure 4.19) to add additional shapes and text to describe the chart. Bear in mind, do not over enhanced the chart with too many shapes because it would “block” some information on the chart. Hence, you have to add them with caution.

Figure 4.19: Drawing toolbar

4.10 Moving and Resizing Chart

Sizing handles indicate a chart
is selected and can be moved,
sized, copied, or deleted

Corner handles
indicate bar can
be edited

Figure 4.20: Moving and resizing chart objects

In many cases, we may need to move and resize certain parts of the chart. Referring back to Figure 4.14 and Figure 4.15, we moved and resized titles, data labels and the 3-D walls. Look at Figure 4.20, there are handles at chart corners to indicate that the chart can be resize and move around. Bars cannot be resized and moved but they can be deleted. Other type of charts such as pie charts, you can select a slide of the pie and move it out from other portions.

4.11 Multiple Data Series
The chart that we created just now has multiple data series selected. By selecting multiple data series, you see individual data points rather than totals. When we choose one set of data series from worksheet, it can be either row or column, the data points are plotted the same either way, but the grouping will be different.

If data series are in rows, the first row is used for category labels on the chart. The remaining rows are used for data series. The first column will be used as legend keys. If data series are in columns, the first column is used for category labels on the chart. The remaining columns are used for data series. The first row will be used as legend keys.

4.12 Object Linking and Embedding
So far, we only have charts embedded as objects inside Excel workbook. It would be better to have those charts appear in other applications such as Microsoft Word and Microsoft PowerPoint. There are two ways to place an object of one application to another application: embedded object and linked object.

An embedded object is stored in the document. For example, an Excel chart becomes part of the Word document. That chart can be edited as a picture object if you just use copy the chart from Excel and paste it into Word. Any changes made to this chart in Excel do not change in Word and vice versa.

A linked object is stored in its original source file but create an image file in the other application. To achieve this method, you copy the chart from Excel, when you want to paste it into the other application, you use Paste Special command (from Edit menu) and Paste Link it. Any changes made to the chart in Excel will automatically change the linked object in the other application. A linked object has an option to update and synchronize with its main document.

Figure 4.21 shows the Links dialog box of Microsoft Word. A linked object from one application to another application has update source, open source, change source and break link options. The method of updating the linked objects can be manual or automatic.

Figure 4.21: Linking object

Summary
Charts are graphical representation of data. There are many types of charts in Excel. Each chart has several sub-type charts. You select the chart type based on the message you are trying to convey. It is easier to create a chart by using Chart Wizard. Charts can be embedded on a separate chart sheet. Multiple data series may be specified in either rows or columns. Same data points, but different grouping gives different comparison. Object Linking and Embedding (OLE) creates a compound document. It contains objects from multiple applications. By embedding the object, it is stored within the compound document. By linking the object, it is stored as a separate file.

Chapter 5
Decision Making in Spreadsheets
5.1 PMT Function
5.2 FV Function

5.3 Goal Seek
5.4 Statistical Functions
5.5 IF Function
5.6 VLOOKUP & HLOOKUP Functions

OBJECTIVE
In this chapter, the student will learn:
? Use the PMT function to calculate the payment of a car loan or mortgage ? Use the FV function to determine the future value of a retirement account ? Explain how the Goal Seek command facilitates the decision-making process

? Use the AVERAGE, MAX, MIN, and COUNT functions
? Use the IF, VLOOKUP and HLOOKUP functions to implement decision making

5.1 PMT Function
Banks generally set repayments on loans and mortgages in equal payments over a fixed period of time. Within these regular payments the mix of interest and principal changes as time passes. A single formula can easily calculate the repayments on a loan of $x,000 at y% over z years. The real world is often more complicated and interest rates change at irregular intervals, often part of the way through a particular repayment period.

Let’s look at one example, a sum of $50,000 loan is borrowed and to be repaid over 1 year in equal monthly payments at an interest rate of 7%.
The Excel PMT() function is used to calculate monthly repayment, periodic payments such as car or mortgage payment. Its syntax is:

PMT(Rate, Nper, Pv, Fv, Type)
•
•
•
•
•

Rate – The interest rate per period for the loan.
Nper – The total number of payment for the loan.
Pv – The present value: the total amount that a series of future payment is worth now.
Fv – The future value, or a cash balance you want to attain after the last payment is made, 0 (zero) if omitted.
Type – A logical value: payment is due at the beginning of the period = 1; payment is due at the end of the period = 0 or omitted.

Referring back to the example above, the interest rate should relate to single repayment period and not the annual rate. In this example, it is 1/12th of 7%. Now, let’s build up a simple list to calculate a loan as shown in Figure 5.1.

Figure 5.1: PMT calculation

If you look carefully at the formula of B7, you see a negative ( – ) operator in front of PMT formula because by default Excel will display the repayment as negative amounts. Thus, by placing a negative operator will convert the negative amount to positive amount. With PMT function and a spreadsheet, we can make assumptions by placing some other possible data into the cells and let the application does its calculation. Let’s add in a few more assumptions as shown in Figure 5.2. We added more data into the table. We create 4 assumptions whereby we used the same data except that we have 2 different scenarios here whereby the first is payment with due date at the beginning and the second is payment with due date at the ending. The formula that we used in cell C8 is: =-PMT($B$7/$B$6,C$5*$B$6,$B$4,0,$B8)

Since you have already learnt cell references, you will notice that the formula used absolute cell reference and mixed cell references. When we copy this formula by using fill handle to adjacent cells, the cells will change, calculate and produce the correct value.

Figure 5.2: PMT assumptions

5.2 FV Function
In Excel, the FV function returns the future value of an investment based on an interest rate and a constant payment schedule.
The syntax for the FV function is:
FV( rate, nper, pmt, [pv], [type] )
•
•
•
•
•

Rate – The interest rate for the investment.
Nper – The total number of payment for the annuity.
Pmt – Payment, the amount of the payment made each period. Pv – The present value of the payment. It is optional. If omitted, the FV function assumes PV is 0.
Type – It indicates when the payments are due. It is optional. Type can be one of the following values: payment is due at the beginning of the period = 1; payment is due at the end of the period = 0 or omitted.

Let’s consider this example to use FV function. You want to know the future value of an investment where you deposit $10,000 into a savings account that earns 8.0% annually. You are going to deposit $1,000 at the beginning of the month, each month, for 3 years. Now, let’s build up a simple list to calculate the investment as shown in Figure 5.3.

Figure 5.3: FV calculation

You must have noticed that there is negative operator in front of PMT and PV parameters because you are making the payment, thus it has to be negative
values. Similarly to previous PMT function, we also can make assumptions for FV function. Let’s add some possible data into the list and enhance the worksheet. We create 4 assumptions whereby we used the same data except that we have 2 different scenarios here whereby the first is regular payment with due date at the beginning and the second is regular payment with due date at the ending. Figure 5.4 shows the new assumption list. The formula that we used in cell C8 is:

=FV($B$5/12, C$7*12, -$B$6, -$B$4, $B8)

Figure 5.4: FV assumptions

5.3 Goal Seek
Sometimes, when we create assumptions, we know the input values but not final values. Hence, we use functions and formulas. However, what if we do not know the input value(s) but we know the final values? How do we work back to know the input value(s)?

In Microsoft Excel, there is this function called Goal Seek. Goal Seek is part of a suite of commands sometimes called what-if analysis tools. When you know the desired result of a single formula but not the input value the formula needs to determine the result, you can use the Goal Seek feature available by clicking Goal Seek on the Tools menu. When goal seeking, Microsoft Excel varies the value in one specific cell until a formula that’s dependent on that cell returns the result you want.

Let’s look at Figure 5.5 to see how Goal Seek works. First of all, you type in the following text and number constants into the cells and then add the PMT function. The reason we have 2 columns, 1 before goal seeking and 1 after goal seeking is to show you how goal seek function does the calculation to get the result. To use goal seek: 1) Select cell C6, click on Tools menu

Goal Seek command.

2) Refer to Figure 5.6 to get data entries for Goal Seek dialog box. 3) Click Ok and then you will see Goal Seek does a very fast calculation and produce a result for cell C5 as shown in Figure 5.7.

Figure 5.5: Goal Seek calculations

Figure 5.6: Goal Seek dialog box

Figure 5.7: Goal seek result

Please take note that the Set cell field requires a cell reference with formula. The To value field must be a digit data. The By changing cell field must be a cell reference that is being used inside the formula of the Set cell field cell reference. After Goal Seek does it job, the new value will be shown on the worksheet.

5.4 Statistical Functions
There are statistical functions in Microsoft Excel, namely MAX, MIN and AVERAGE functions.
•
•
•

MAX – returns highest value from the parameter list
o Syntax: =MAX(number1, number2, number3…)
MIN – returns lowest value from the parameter list
o Syntax: =MIN(number1, number2, number3…)
AVERAGE – returns average value from the parameter list
o Syntax: =AVERAGE(number1, number2, number3…)

Figure 5.8 and Figure 5.9 show the usage of these functions along with SUM
function.

Figure 5.8: SUM, MAX, MIN & AVERAGE functions

Figure 5.9: Formulas of SUM, MAX, MIN & AVERAGE functions

So far, these functions calculate with a built-in mathematical formula. What if you want to keep track of the number of a value, data or cells appear in a range? We can use the following functions:

•

COUNT – Counts the number of cells that contain numbers and also numbers within the list of arguments. Use COUNT to get the number of entries in a number field that’s in a range or array of numbers.

o Syntax: =COUNT(value1, value2, value3…)
COUNTA – Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.
o Syntax: =COUNTA(value1, value2, value3…)
COUNTIF – Counts the number of cells within a range that meet the given criteria.
o Syntax: =COUNTIF(range, criteria)

Let’s have a look at Figure 5.10 to see how these 3 functions work. You build this list by typing in all the text and number constants except you leave cell D4, E4, F4 and G4 empty. Next, you refer to Figure 5.11 to enter the correct formulas into those cells.

Figure 5.10: COUNT, COUNTA & COUNTIF functions

Figure 5.11: Formulas for COUNT, COUNTA & COUNTIF functions

5.5 IF Function
So far, we have covered quite a lot of functions that involves with calculations. Now, we will look at IF function of Microsoft Excel. We use IF function to set conditions for decision making within a workbook. The following is the syntax of IF function: IF(logical_test, value_if_true, value_if_false)

It is easily understood by looking the syntax. The function’s parameter is a condition testing. The second parameter displays the value if the condition testing yields true result. The last parameter displays the value if the condition testing yields false result. The values can be digits, text or cell reference. To set good condition testing, we can use relational operators. Table 5.1 shows the relational operators of Microsoft Excel. Comparison

Operator
=
<
>
>=
70, 1, 0)
=IF(V4 >= $D$2, K8 * P4, 7)
=IF(Y6 + H2 =50, “Pass”, “Fail”) formula inside D2.
Then, you copy the formula down to row 3 till 6. If you want to highlight the student who failed the exam, you can use conditional formatting (refer to Figure 5.13 on how to set the conditional formatting). We highlight the cell if the cell content is the word Fail. If the content does not meet the conditional formatting condition, nothing happen to the cell.

Figure 5.13: Adding conditional formatting

As you may have notice, the value_if_true and value_if_false parameters are words. If you want to print out words as result, you need to include the double quotes ( “ ” ). If the values that you want to print out are digits, you just type the digit value. If you have learnt programming (C++, JAVA, Visual Basic, etc) before, you may have seen nesting structures. An example of nesting structure is nesting if…else structure whereby the else part contains another if…else structure, and this inner if…else is nested inside the outer if…else structure. IF functions can be nested inside one another. Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. Figure 5.14 shows an example of using nesting IF functions to display which stall (name) that has the highest and lowest sales among the rest. Figure 5.15 shows the formula of that worksheet.

Figure 5.14: Nested IF functions

Figure 5.15: Nested IF functions formula

5.6 VLOOKUP & HLOOKUP Functions
It would have been better if you can make decisions based on a table or list. In Microsoft Excel, two functions: VLOOKUP and HLOOKUP functions allow Excel to look up a value in a table and return back a related value. The VLOOKUP function searches for a value in the leftmost column of an array, an arrangement or list of items, and returns the value on the same row of another column to the right. The HLOOKUP function works similarly to the VLOOKUP function, but searches for values in the top row of table and returns the value in the same column based on the column index number.

The following is the syntaxes for VLOOKUP and HLOOKUP functions: VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

Figure 5.16: VLOOKUP function arguments

Figure 5.16 shows the function arguments of VLOOKUP function. The lookup_value field takes in value that is to be found in the first column of the table. The table array is the cell reference pointing towards the table in the workbook. The lookup_value will then search for a value from the table array for the closest/matching value. The col_index_num is the column number of the table from which the matching value should be returned. The first column of values in the table is column 1. The range_lookup is a logical value. True value or omitted indicates it will find the closest match in the first column (sorted in ascending order). A false value indicates it will find the exact match. Figure 5.17 shows the function arguments of HLOOKUP function. Only the row_index_num field is different from VLOOKUP. Instead of referring to columns, this field refers to rows. The first row of values in the table is row 1.

Figure 5.17: HLOOKUP function arguments

Let’s use these two functions and compare the differences. Figure 5.18 shows VLOOKUP function usage in getting student’s grade based on marks. By looking at the formula, the cell reference of table_array is absolute cell reference because when you copy the formula to the adjacent rows, the table_array cell reference will change according, thus it will produce incorrect result.

Figure 5.18: VLOOKUP function

Now, let’s look at Figure 5.19 to see how HLOOKUP function is used. In this example, we created a simple table showing 3 different computer input devices and 2 different connector types. Each device has a price except for
Joystick PS2 port. Below the table, we typed in 3 simple questions where user has to type in first and second questions’ answers into column A. The last question’s answer, namely cell A10 contains the HLOOKUP function with a nested IF function placed at the row_index_num parameter. The formula inside cell A10 is a combination of HLOOKUP and nested IF function. =HLOOKUP($A$6, $B$1:$D$3, IF($A$8=”PS2″, 2, IF($A$8=”USB”, 3)), FALSE)

Figure 5.19: HLOOKUP function

What these formula/functions do here is first of all, the loopup_value looks at the content of A6 which is the answer for the first question. The answer that we are hoping to get from user is Keyboard, Mouse or Joystick. Once the function gets the input, it cross check with the table array cell reference which is $B$1:$D$3. Once it found the exact match of from the first row of the table, it will then look at the third parameter which is the row_index_num. Since there are two types of connectors here, we asked another question which is “What kind of connector do you want?” to get either PS2 or USB as answer. Therefore, we use nested IF function as the third parameter to determine which input is entered into cell A8 so that the IF function will return back a row index number.

Summary
There are many financial functions available in Excel but we mainly look at two commonly used functions: PMT and FV. PMT focuses a lot on payment of loan type of money whereas FV calculates the future value of investment. Goal Seek function enhances decision making in a quicker manner. There are many statistical functions but we look at MAX, MIN, AVERAGE, COUNT, COUNTA and COUNTIF functions to get highest, lowest, average, number of times cell appears and number of times a particular data appears. To make decision that produces 2 different results, we use IF function. We can have IF function nested inside another IF function to produce greater decision making mechanism. Lastly, the VLOOKUP and HLOOKUP functions are also use to make decisions that involve getting data from table. Many functions can be mixed together to produce better decision making operations.

Chapter 6
List and Data Management &
Converting Data to Information
6.1 Database Concepts
6.2 List and Data Management
6.3 Data Form
6.4 Text Import Wizard
6.5 Data Validation
6.6 Sort
6.7 Filter
6.7.1 AutoFilter
6.7.2 Custom Filter
6.7.3 Advanced Filter
6.8 Outline
6.9 Subtotal
6.10 Name Command
6.11 Freeze Panes

OBJECTIVE
In this chapter, student will learn:
? Add, edit, and/or delete records in a list
? Use Data Form to quickly manage records
? Use the Text Import Wizard to import data in character format

? Include data validation on a list
? Use the Sort command; distinguish between an Ascending and Descending sort
? Use the AutoFilter and Advanced Filter commands
? Use the Outline command
? Use the Subtotals command

6.1 Database Concepts

Database is a collection of data stored in a computer in a systematic way to produce meaningful information when user retrieves them from the system. Basically in Excel, lists can be used as simple databases since the data are organized in tabular format that produce information to users. A record is single information stored in a row of a list/table. Most of the time, first row of a list/table contain field names, also known as column heading titles. A field is a unique information contained in a column for a record. These fields are the same for each record (row). A key is used to determine the sequence in which the rows appear. A primary key is a unique key that identifies a particular record within the list. There can only be one primary key for each record in a list. All in all, a database must have records, fields, keys and primary keys.

6.2 List and Data Management
A list is an area of a worksheet that holds similar rows of data. You may think that it is easy to create a list of information by keying in data, you are just partially correct. So far all the Excel data entries from the beginning till now are not validated by the application. We (human) ensure that we input the correct input but sometimes we fail to do so. By inputting incorrect data will produce incorrect results, hence garbage in, garbage out. To manage a list and data, we need to validate input so that the list can produce valid output. There are a few ways to manage such as verify spelling of field names and records to ensure user understands what kind of input should be entered. Sometimes, we may have to edit the list by inserting or deleting rows and columns. These are some ways that we can manage list and data.

6.3 Data Form
Sometimes, it can be more convenient to enter data/record into a dialog box instead of typing them onto Excel Worksheet. Microsoft Excel provides a form known as Data Form which is a dialog box to allow user to enter or display one complete row of information, or record, in a list at one time. Before you can use data form to add record to a new list, the list must have labels onto of each column. Excel uses these labels to create fields on the form.

Add new record
into list
Column labels
and fields of the
list

Delete current
record in display

Revert back to
original record
before you accept
modification

To scroll to
previous or next
record of the list,
will find the
previous or next
record of filtered
list too

To switch to
criteria form
Close data form

Figure 6.1: Data Form

Let’s create a simple database to use data form. First of all, you type in Name, ID, Gender, Age, Class Code, Course Code, Country and Enrol Date into row 1 starting from column A. After that, you select cell A1 and then click on Data menu Form command. You will get an information message box to inform you about the worksheet list and labels. Click Ok button and then you will
see the data form as shown in Figure 6.1. Next, you enter 3 different records (you are free to enter any relevant records) by using data form. Every time you have completed one new record in the data form, click on New button. The moment you click on New button, data form will clear every field and the single record you just entered is automatically stored onto the list on the worksheet. If you want to edit or delete records from the list, you can use the data form. First of all, you select any field on the list and then access the Form command from Data menu. It is easy to edit a record by just scrolling through the whole list to search for the record and then select the field(s) that you want to edit. The Delete button deletes the record that you are currently viewing.

The Criteria button allows you to switch to criteria form. This form allows you to filter the list for specified criteria on any of the fields.
Displaying
criteria form
To clear fields

To switch to
data form

Figure 6.2: Criteria data form

Whatever you entered into the data form as a record, before Excel accepts the record, it will check for data validation for the fields that you have specified with data validation criteria (you will learn soon). If there is an invalid input, in any of the fields, the record will not be added to the list. Moreover, say if you did not specify a particular format for the columns, the data added to the list will be based on the default settings of the cells.

6.4 Text Import Wizard
There are occasions that you may have some data/information stored inside a
file. You may want to transfer the data/information into Excel for analysis but it is troublesome if you resort to copy and paste techniques. Microsoft Excel has the ability to import data from external sources such as:

•
•
•
•

Importing data from databases and files
Importing data with Microsoft Query
Importing data from the Web
Importing data with Visual Basic for Applications

However, we will only look at importing data from files in at this point. We look at how to import text (*.txt) file into Excel. Microsoft Excel has Text Import Wizard that you can access it from Data menu

Import External Data command
Import Data
subcommand. Once you have clicked on Import Data command, you will get a Select Data Source dialog box as shown in Figure 6.3.

Figure 6.3: Select Data Source dialog box

Click on the drop down list of Files of type and select Text Files. Then you should search and locate for a text file that has organized data. For example, Figure 6.4 shows a text file that has several records organized in order. You select the text file and then click Open button.

Figure 6.4: External text file data source

After that, you will see the Text Import Wizard dialog box as shown in Figure
6.5. The Text Import Wizard can convert an ACSII text file into Excel workbook. The wizard can determine which type of file format of the opened text file. There are two formats: fixed width and delimited.

•

Fixed width is a format whereby each field uses the same number of positions in each record.

•

Delimited is a format whereby fields are separated by a specific character, such as a comma or tab.

Figure 6.5: Text Import Wizard step 1

Step 1 of Text Import Wizard, the wizard will inform you what kind of data type is your text file. In Figure 6.5, it discovers the file is a delimited type, and it recommends you to choose delimited option. The next part is to select which row to begin importing into Excel workbook. File origin checks the format of the text file since the content need not always be English language.

Figure 6.6: Text Import Wizard step 2

Step 2 of Text Import Wizard is to choose which type of delimiter the text file has. Figure 6.6 shows some common delimiter options and the data preview. Right now, there isn’t any delimiter selected; hence the records are still shown in original form. Once you have chosen semicolon option, you should see the data preview changed to a tabular style with vertical lines separating the each field, as shown in Figure 6.7. Then you move on to step 3.

Allows you to
treat the text
qualifier as a
normal text

Figure 6.7: Delimiter selected

The final step lets you to select each column and set the data format. Each option has different settings and properties.

Figure 6.8: Text Import Wizard step 3

After the last step of the wizard, you will get an Import Data dialog box (Figure 6.9). You will be asked on where you want to put the data, either on an existing worksheet or on a new worksheet.

Figure 6.9: Import Data dialog box

6.5 Data Validation
Garbage in garbage out, the term means whatever invalid input you entered into the system, it will produced invalid output by the system. It is important to enter correct and valid data into Microsoft Excel workbook every time. Microsoft Excel has Data Validation feature that can restrict user from entering a particular data depending on the validation settings.

The data validation command can be found from Data menu
Validation command.
Let’s continue from the previous student list to try out data validation. Please follow the following instructions to set up the validation criteria for each column. 1) Click on Column heading A and then access Data Validation command dialog box.

Figure 6.10: Data Validation dialog box20

2) Choose Text Length under the Allow option.
3) Choose between and minimum 3 and maximum 20.
4) Then view Input Message tab and disable it.
5) View Error Alert tab and then type in Name under Title and type in Please enter a name that has 3 to 20 characters under Error message. 6) Click Ok button.
7) At cell C100, type Male. At cell C101, type Female.
8) Click on Column heading C and then access Data Validation command dialog box.
9) Choose List under the Allow option.
10) Choose the source whereby the cells contain the Male and Female data. 11) Then view Input Message tab and disable it.
12) Click Ok button.
13) Click on Column heading D and then access Data Validation command dialog box.
14) Choose Whole Number under the Allow option.
15) Choose between and minimum 18 and maximum 55.
16) Then view Input Message tab and disable it.

17) Click Ok button.
18) Select Column heading E and F and then access Data Validation command dialog box.
19) Choose Text Length under the Allow option.
20) Choose less than or equal to and enter 7.
21) Select Column heading G and then access Data Validation command dialog box.
22) Choose Text Length under the Allow option.
23) Choose less than or equal to and enter 20.
24) At cell H100, type 01/07/2006. At cell H101, type 01/02/2007. At cell H102, type 01/04/2007.

Once you have completed these steps, any input onto those cells will be restricted according to the settings.
Now, let’s look at the components under Settings tab of Data Validation dialog box. The validation criteria have 8 allow types:
•
•
•
•
•
•
•
•

Any value – No restriction
Whole number – Digits with out decimal places only
Decimal – Digits with decimal places
List – Create a drop down list on cells with contents based on selected range Date – Date inputs only
Time – Time inputs only
Text length – Any value but you can set the length restriction Custom – Allows you to customize the validation criteria

The validation criteria have 8 relational data types:
•
•
•
•
•
•
•
•

Between – 2 fields: minimum and maximum
Not between – 2 fields: minimum and maximum
Equal to – 1 field

Not equal to – 1 field
Greater than – 1 field: minimum
Less than – 1 field: maximum
Greater than or equal to – 1 field: minimum
Less than or equal to – 1 field: maximum

The second tab of Data Validation dialog box is the Input Message tab. This part allows you to decide whether you want to include input message when cell is selected or not. The last tab of Data Validation dialog box is the Error Alert tab. This part allows you to decide whether you want to include error message after user entered invalid data or not. There are 3 styles of error message:

•
•
•

Stop – Excel will not accept invalid input
Warning – Excel will warn user about invalid input and ask user whether to accept, edit or reject the invalid input.
Information – Excel will display a message to inform user about invalid input and user can choose whether to accept the input or reject it

6.6 Sort
Sorting data is one of the most important computing tasks. When there is an enormous amount of information, it may not be easy to read unless they are sorted in order. Microsoft Excel has sort commands that will arrange records in a list according to the value in designated fields.

Microsoft Excel allows you to sort text, digits and date fields. It can sort the list in ascending and descending order. The sort command and sort a list up to three fields. When you choose to sort a list on more than one field, choose the most important field as the primary sort key.

Figure 6.11 shows the Sort dialog box. The Sort command is under Data menu. The My list has section, there are two options to choose. If the list has column headers/titles, select Header row, or else you choose No header row in which Excel will use the column heading letters as the header row for the list.

Figure 6.11: Sort dialog box

Now, let’s try sorting the student list that we have entered a few more new records by importing records from a text file. You select the whole list except row 1, e.g. select cell A2 to H11. Access Sort dialog box. We sort by Country first, followed by Age and Gender comes last. All the sorting levels are sorted in ascending orders. Figure 6.12 shows the list before sorting whereas Figure 6.13 shows the list after sorting.

Figure 6.12: Before sorting

Figure 6.13: After sorting

6.7 Filter
Sometimes, by looking at specific records from a huge list can be a pain to our eyes. It would have been better if we can extract out certain records from the list and display them onto the worksheet. Microsoft Excel provides this solution through the Filter command. Filter command searches for specified criteria and hides the other records that do not meet the criteria. In the end, it displays a subset of records from the bigger list. There are three ways to filter list in Excel:

•
•
•

AutoFilter – You select existing criteria from the headers of the list Custom Filter – You customize the criteria to display more specified records Advanced Filter – You specify more specific criteria to display more certain records in which you can display them on a new location or existing place

6.7.1 AutoFilter
The AutoFilter is the easiest filter command to use. You simply select any or all the headers of the list and then click on AutoFilter button from Standard toolbar or access Data menu
Filter command
AutoFilter subcommand. AutoFilter allows you to set
criteria for fields using the drop-down list for the chosen column. Figure 6.14 shows the AutoFilter drop-down list buttons and available criteria options.

Drop-down list button

Criteria available from
drop-down list

Figure 6.14: AutoFilter

Let’s filter the list by selecting BBA criteria from Course Code drop-down list header. You will get a filtered list as shown in Figure 6.15. If you have used AutoFilter command to filter the list, the header drop-down list button with blue colour indicates that that column is currently filtering the list. The row numbers in blue colours mean those rows of records are a subset of the original list. You can filter the list even further by choosing a criterion from any of the drop-down list buttons of non-blue arrows.

Figure 6.15: Filtered list using AutoFilter

Referring back at Figure 6.14, there are 3 default criteria: All, Top 10 and Custom. The All option displays all the records of the list. The Top 10 option allows you to filter the list to display the top 10 records according to the selected field. It is usually used for list with values/digits that have many different values. Let’s look at Figure 6.16, Figure 6.17 and Figure 6.18 to see how Top 10 filter option works.

25) You created a list as shown in Figure 6.16. Then you enable AutoFilter. 26) Click on the drop-down button of Total column and then choose Top 10 option.
27) You will get the Top 10 AutoFilter dialog box as shown in Figure 6.17. 28) Just click Ok button since we want to display the best 10 product sales. 29) Excel will filter the list and display the 10 highest selling products based on the Total column.

30) This list is not sorted in any order (as shown in Figure 6.18). Therefore you can do the sorting after the filtering.

Figure 6.16: Original product list

Adjustable
value

Show either
Items or
Percentage

Show either top
(best) or bottom
(worst) records
Figure 6.17: Top 10 AutoFilter

Figure 6.18: Top 10 items

6.7.2 Custom Filter
Custom Filter option is found under the AutoFilter option. The Custom Filter command allows user to customize the filtering criteria based on logical and relational operators (of normal English). Figure 6.19 shows the Custom AutoFilter dialog box. Filtering operation

Text or
numbers or
choose one of
the options
from the dropdown list

And will
combine both
criteria. Or
will use one
of the criteria

Wildcards

Figure 6.19: Custom AutoFilter dialog box

The box on the left, the filtering operations of equals, or does not equal, contains, or does not contain are used to filter text values. The other filtering operations are used with digit values. The wildcards: ? and *; they are used with text inside the boxes on the right.

Figure 6.20: Using Custom AutoFilter

Let’s put Custom AutoFilter to work. We want to apply a custom filter on the Course Code column of Student list worksheet. Please refer the custom filter settings in Figure 6.20. We choose the Or logical operation and then choose
BSEM and BBA options from the drop-down list. Figure 6.21 shows the result of the custom filter.

Figure 6.21: After using Custom AutoFilter

6.7.3 Advanced Filter
The last type of filter command in Excel is Advanced Filter. Advanced Filter is an extension of AutoFilter. It allows user to create more complex criteria by using the logical and relational operators (And, Or, Equal, Greater than, Less than, etc). On top of that, Advanced Filter allows user to choose whether the filtered results to place on the original list or to place on a new location on the worksheet so it can leave the original content intact. However, Advanced Filter command requires you to set up criteria range and an area on the worksheet to store the criteria. Let’s see how we use Advanced Filter by using the Student list example.

Figure 6.22: Building criteria list

Before you want to use Advanced Filter, let’s build the criteria list by typing in the text constants of row 13 to 15 as shown in Figure 6.22. Row 13 is required because we need to include the exact column heading titles of the list (excluding the formatting style) to allow Excel to identify which column that the criteria belong to. Row 14 and 15 are some criteria that we want to filter out from the rest of the list. After that, you click on Data menu Filter command

Advanced Filter subcommand. The Advanced Filter dialog box
will appear as shown in Figure 6.23.
Cell reference pointing
to the list/table
Choose your
filtered list
location

Cell reference pointing
to the criteria range

If Copy to another
location is selected,
you specify where you
want to copy the
filleted list to.

Displays only unique
rows that you’re your
criteria and to exclude
duplicate rows

Figure 6.23: Advanced Filter dialog box

You follow the settings shown in Figure 6.23 and then click Ok button. The filtered list will be placed at Cell A21 of Student List worksheet as shown in Figure 6.24.

Figure 6.24: Advanced filtered list

6.8 Outline
Sometimes, when a list has grown too big, it can be very difficult to trace records. Microsoft Excel provides a command that you can group similar records together; the command is Outline. When you use outline, you can show and hide outlined rows or columns details. Outlining data is only done on a list that has been arranged in an order. Certain list, before you want to outline it, it is better to include a blank row below or a blank column to the right of the same fields. Let’s try the Outline command on the Student list example to see how it works.

31) You select cell A1 to H11.
32) Click on Data menu

Sort command.

33) Sort by Class Code in ascending order. Click Ok.
34) Right click on row number heading 4 and insert a blank row by choosing Insert command. You do the same for the remaining fields, the last row of BBIT and BIB. You should get a list like Figure 6.25.

35) At cell A4, cell A8, cell A13 and cell A14, you type BBA students, BBIT students, BIB students and BSEM students.
36) Next, you select row 2 and 3, click on Data menu
command
Group subcommand.

Group and Outline

37) Next, you select row 5, 6 and 7, click on Data menu
Outline command
Group subcommand.

Group and

38) Next, you select row 9, 10, 11 and 12, click on Data menu Outline command
Group subcommand.

Group and

39) Lastly, you select row 14, click on Data menu
command
Group subcommand.

Group and Outline

outline symbols allow you to show
Your worksheet should look like Figure 6.26. The
and collapse the entire level of the outline when you click on it. The expand button and collapse button are used to show and hide the grouped records. When you click on the “1” button, it will collapse all levels and only display the first level outlines as shown in Figure 6.27.

You can add up to 8 levels of outlines on a single worksheet.

Figure 6.25: Blank rows beneath Class Code

Outline symbols

Figure 6.26: Outlined list

Figure 6.27: Collapsed list

6.9 Subtotal
The Subtotal command in Microsoft Excel quickly calculates digits/numbers that are grouped together and then add the results onto the list. Using subtotal is easy; it follows the same concepts as outlining the list. You have to group records together in the list. To demonstrate how the subtotal works, let’s use the example worksheet shown in Figure 6.16. You may have noticed that the list has already sorted in order. Next, follow the instructions below.

1) You select any cell on the list, click on Data menu

Subtotals command.

2) The Subtotal dialog box will appear as shown in Figure 6.28.

3) At each change in option, you choose Category.
4) Use function option, you select Sum.
5) Add subtotal to option, you select Total only.
6) Tick Replace current subtotals and Summary below data. Click Ok.

Figure 6.28: Subtotal dialog box

Figure 6.29: List with subtotal

The subtotal function quickly adds up the numbers, place the result (at the bottom) on the worksheet and add outlines to the worksheet. In this case, the subtotal function adds subtotals to each category and adds a grand total value at the bottom of the list. Figure 6.30 shows the subtotal function arguments dialog box. The syntax for subtotal is: Subtotal(function_num, ref1)

The function_num parameter is a number from 1 to 11. Each number has its specific function. For example, 9 means Sum function. The ref1 is the ranges of cell reference to be used with the function_num parameter for calculation.

Figure 6.30: Subtotal function arguments dialog box

6.10 Name Command
Sometimes, it is much better to name cells with a proper name that you can remember rather than the cell references such as A100, AB2045, etc. You can give name to cell or a range of cells easily from the Insert menu

Name command
Define subcommand.
When you add any new row(s)/column(s) onto worksheet, the named cells automatically adjust by themselves. Names are always considered as an absolute cell reference. On top of that names can be used in formulas for
example:

A1 is named as SALES and contains the value of 1000
B1 is named as EXPENSES and contains the value of 250
You can type in the formula:
=SALES – EXPENSES
instead of
=A1-B1
There is one disadvantage of defining names for cells: it will make the column width bigger when you view the worksheet in Formula Auditing Mode.

6.11 Freeze Panes
When you are working on huge Excel workbook/worksheet say, the list expands beyond to column M and row 25; then you may find it troublesome to scroll through the list all the time to view at certain fields of the same record. Microsoft Excel provides a solution to this problem. Freeze Panes command is a feature that allows user to freeze a portion of the worksheet. A frozen portion of the worksheet will remain static to display that particular portion of data. To use Freeze Panes command, you access it from Window menu. Before you freeze the worksheet, you must know what portion of the windows you want to remain unchanged as you scroll. Then you select on a cell and then choose Freeze Panes command. For example, if you have chosen cell C5, the rows above C5 (row 1 to 4) and the columns to the left of C5 (column A and B) will remain frozen. The other side of C5 is scrollable. As you scroll the worksheet, you can see the difference.

Summary
Database is a collection of data stored inside a system that when you retrieve them back, it can become useful information. Managing list and data is an important task in Microsoft Excel especially when the list or data is humongous. We can easily enter data into a list by using Data Form. You can import external data from other files such as text files into Excel through the Text Import Wizard. It is very important that we can validate our input
into the list by using Data Validation because of garbage in garbage out. When a list becomes too big, to organize it properly, we can use sorting to arrange the list in order so that we can read it properly. Sometimes, we need to look at specific records from a huge list. So we can use the filter actions to either quickly filter the list or we create a complex but powerful filtering criteria to display only the records that we want to refer at. A big list can be easily managed when the records are grouped together. We can group a list by using outline command. The subtotal function also group similar records together with additional of calculating specific values of a set of records grouped together. The subtotals of the grouped records are then displayed on the list. It is useful to name cells with meaningful names so that we know which cell contains a particular value. Last but not least, working with huge list is much easier by using Freeze Panes feature because we can easily scroll around the list to see only certain data without scrolling back and forth too much.

Chapter 7
Data Analysis
7.1 Data Analysis Techniques
7.1.1 Sorting
7.1.2 Calculating
7.1.3 Summarizing
7.1.4 Filtering
7.1.5 Formatting
7.1.6 Charting
7.2 COUNT, COUNTA, COUNTIF Functions
7.3 IF Function
7.4 AND & OR Functions
7.5 Goal Seek Command
7.6 Lookup Functions
7.7 Rank Function
7.8 PivotTable & PivotChart
7.8.1 Data

7.8.2 Field Types
7.8.3 Layout

OBJECTIVE
In this chapter, student will learn:

? To learn techniques of analyzing data
? To use COUNT, COUNTA and COUNTIF functions, IF function, AND and OR functions, Goal Seek command, Lookup functions, Ranking function, PivotTable and PivotChart to analyze data

7.1 Data Analysis Techniques
In many organizations, data is more than enough. To be more precise, data is too much to handle and manage! By having enormous amount of data, we have to organize them in order first. Once we have them in proper stacks, groups and relationships, only then we can analyze the data to turn it into information. By having meaningful information, we can make decisions easily, quicker, more effective and more efficient. Now, we will look at some data analysis techniques that decision makers commonly used.

7.1.1 Sorting
Sorting is a technique that we arrange the lists by one or more fields. The arrangement of the list can be in ascending or descending orders. By arranging the list, it is easier to find an individual record rather than an unsorted list. This is because in an unsorted list, everything is mixed up and human eyes tend to focus more the entire record rather than on an individual field on the list until it may tire off and then ended up missing it. In sorted list, since we know the records are gathered in one group, we look at the more significant field to search for the item.

7.1.2 Calculating
Calculating is the second technique to analyze data. We may have digits here and there on a list but they can joint together to produce a better result.
Calculations may involve the use of arithmetic operators ( +, -, *, /, % ). Calculating also can manipulate data in an individual record. E.g. we can extract out the age of a person by looking at his/her date of birth.

7.1.3 Summarizing
Summarizing technique is a fairly common data analyzing technique used. It is effective when you want to display records within groups, at the same time involving some kind of calculations. Outline, subtotal and PivotTable are some examples of Microsoft Excel functions that you can use to quickly summarize lists.

7.1.4 Filtering
Filtering technique just displays records that meet specific criteria that we specified or chosen. This technique helps to isolate certain records and we can focus on the subset of the entire list with less distraction. AutoFilter, Custom Filter and Advanced Filter are some commands that you can use for filtering lists.

7.1.5 Formatting
Formatting technique plays important role for data analyzing. When you use formatting properly, it can bring your attention to cell(s) by the way it is displayed. The best example

of formatting is the Conditional Formatting command that applies formatting based on cell value if the condition is true.

7.1.6 Charting
The last technique for data analysis is charting. By having and including charts as a graphical representation of data, we can see the data in a more interesting and attracting manner. Charting is usually used as a display of final results for data analysis.

7.2 COUNT, COUNTA, COUNTIF Functions

The COUNT, COUNTA and COUNTIF functions that we saw in Chapter 5, those functions are usually used for summarizing lists.
•

o Syntax: =COUNT(value1, value2, value3…)

•

COUNTA – Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.
o Syntax: =COUNTA(value1, value2, value3…)

•

COUNTIF – Counts the number of cells within a range that meet the given criteria.
o Syntax: =COUNTIF(range, criteria)

You can refer back to Figure 5.10 and Figure 5.11 to see how these three functions work and differ from the other.

7.3 IF Function
IF function is a one of the many decision making functions. As we have seen the IF function usage in Chapter 5, it evaluates a logical test first. After that, it will return one value if true and another different value if false. On top of that, we have shown you how to use nested IF functions. That is, you place an IF function as the either true or false value of the previous IF function. This nested IF functions can become a very powerful logical test tool for decision making.

7.4 AND & OR Functions
We have seen the AND and the OR operators/operation in other functions such as Custom Filter and Advanced Filter. The AND function returns true if all arguments are true value. The OR function returns true if any arguments are true. Let’s see how we can use both functions in the following scenario.

University A are offering two types of courses: Diploma and Degree. Students who enrol Diploma courses, they need to obtain at least 50% for both coursework and final exam in order to pass a particular module. Students who enrol Degree courses, they need to obtain at least 50% by accumulating the marks for both coursework and final exam. Now, let’s build a simple worksheet based on this scenario. You can refer to Figure 7.1 to type in the text constants and number constants. Cell E3 to E10 have formula and function.

Figure 7.1: Demonstrating AND & OR functions

We added a list data validation for column B. Column C and D have data validation of decimal numbers using between data. The minimum digit is 0 while the maximum for each is based on the percentage shown in row 2. We added data validation just to restrict user from entering incorrect data. We use the following formula and functions in column E: =IF(B3=”Diploma”, IF(AND(C3>=30, D3>=20), “Pass”, “Fail”), IF(B3=”Degree”, IF(OR(C3>=50, (C3+D3)>=50), “Pass”, “Fail”), “Nil”))

What happens here is we use nested IF functions to determine whether user chooses Diploma or Degree for B3. If it is Diploma, then we used an IF function to determine whether the Coursework is more than 30 and whether the Final Exam is more than 20. If both marks exceeded the number, then it will print Pass or else it will print Fail. However, if the user chooses Degree course not Diploma course, the outer IF function test condition becomes false, thus it will run the false value which is this part: IF(B3=”Degree”, IF(OR(C3>=50, (C3+D3)>=50), “Pass”, “Fail”), “Nil”) This is another nested
IF function as the false value of the outer IF function. Now, we determine whether user chooses Degree or not. If it does not match, it will print out the word Nil. If it matches with the word Degree, then it will run and check the inner IF function. This IF function’s test condition uses OR function to check its parameters. If C3>=50, then it is considered as true. If C3+D3 is greater than 50, then it is true. Either one is true will yield true result in which Pass will be printed out or else Fail will be printed.

7.5 Goal Seek Command
We have covered Goal Seek command in Chapter 5. Just to recall back, Goal Seek allows us to find out an unknown input value by keying the known final value. Goal seeking is a method to find a specific value for a cell by adjusting the value of one other cell. When goal seeking, Excel varies the value in a cell that you specify until a formula that’s dependent on that cell returns the result you want.

7.6 Lookup Functions
LOOKUP functions return a value either from a one-row or one-column range or from an array. There are two syntax forms of LOOKUP function: vector and array. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or onecolumn range. The vector syntax form is: =LOOKUP(lookup_value, lookup_vector, result_vector)

The lookup_value is a value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. The lookup_vector is a range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values. It is important that the values in the lookup_vector must be placed in ascending order such as:

-3, -2, -1, 0, 1, 2, 3…
a, b, c, d…

FALSE, TRUE
Otherwise, LOOKUP may not give the correct value. Uppercase and lowercase texts are equivalent. The result_vector is a range that contains only one row or column. It must be the same size as lookup_vector. If LOOKUP cannot find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value. Let’s look at the following example of how to use the vector form of LOOKUP function.

Figure 7.2: LOOKUP vector form

You type all the text constants and number constants into the worksheet. You type in the following formula into cell A9:
=LOOKUP(A7,A2:A4,B2:B4)
You get #N/A because Excel treats an empty cell as zero value. Since zero value is less than the smallest value from the lookup_vector, thus it will produce the not available data. You can try the following input from table 7.1 on cell A7 to see the different results in cell A9.

A7
3.98
3.99
4
4.6
10

A9
#N/A
Pen
Pen
Marker
Duster

Table 7.1: LOOKUP vector values

If you look carefully, you may notice that you can use the LOOKUP vector to make decisions. For example, you can create a simple Excel worksheet to determine which item you can purchase with an amount of money you have in your wallet. By entering a value into a cell, the LOOKUP function can search for the closest value from the list and then returns a value of the matched lookup of the same row.

Now, let’s look at the next LOOKUP function. The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array. The vector syntax form is: =LOOKUP(lookup_value, array)

The lookup_value is a value that LOOKUP searches for in an array. The lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. If LOOKUP can’t find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value. If lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value. Array is a range of cells that contains text, numbers, or logical values that you want to compare with lookup_value.

The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array. If the array covers an area that is wider than height (such as more columns than rows), then LOOKUP searches for lookup_value in the first row. If the array is taller than its width (more rows than columns), LOOKUP searches in the first column. With HLOOKUP and VLOOKUP, you can index down or across, but LOOKUP always selects the last value in the row or column. We have covered HLOOKUP and VLOOKUP functions in Chapter 5. You can refer back to Chapter 5 to refresh back your memory on how to use HLOOKUP and VLOOKUP functions.

7.7 Rank Function
Rank function returns rank of a number in a list. This function requires 2 arguments and 1 optional. The following syntax shows the rank function:
=RANK(number, ref, order)
The number is the number for which you want to find its rank. It can be a cell reference. The ref is an array, or a reference to, a list of numbers. Nonnumeric values are ignored. The order is a number. If order parameter value is 0 or omitted, the rank in the list is sorted descending. If order parameter value is nonzero, the rank in the list sorted in ascending order. Figure 7.3 shows how rank function is used.

Figure 7.3: Rank function

Ranking is useful when you want to see what records are most favored in a huge. You may think ranking is similar to Top 10 of AutoFilter except ranking does not remove other records.

7.8 PivotTable & PivotChart
When you are working with huge list, there are times that you want to create a summarized report of table and chart quickly based on grouping. PivotTable report and PivotChart report are the main component you should use in Microsoft Excel. 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. PivotTable works in such a way whereby it computes summary statistics of grouped categories. A PivotChart report provides a graphical representation of the data in a PivotTable report. You can change the layout and data displayed in a PivotChart report just as you can in a PivotTable report.

Let’s see how we can use PivotTable and PivotChart features to our advantage. Please follow the instructions below to complete the list before you can use
PivotTable and PivotChart.
1) Create a list based on Figure 7.4.

Figure 7.4: University A template

2) Now, we will begin adding data validation onto each column starting from row 3 till 20. Set a text length data validation that accepts a length between 3 and 30 characters for column A.
3) At cell B30 and B31, type in Diploma and Degree.
4) Starting at cell C30 onwards, type in Architecture, Biology, Business, Chemistry, Communication, Graphic Design, IT, Medicine and Physics. 5) Add a list data validation for column C based on cell C30 till C38. 6) Add data validation for column D that accepts decimal numbers between 0 and 60.

7) Add data validation for column E that accepts decimal numbers between 0 and 40.
8) Type in the formula of =D3+E3 into cell F3. Then you copy the formula until row 20.
9) Refer to Figure 7.5, type in the text and number constants into those cells. 10) Type in the formula of =VLOOKUP(F3, $E$31:$G$40, 3, TRUE) into cell G3. Then you copy the formula until row 20.
11) Type in the formula of =IF(G3=”F”, “Fail”, IF(B3=”Diploma”, IF(AND(D3>=30,
E3>=20),
“Pass”,
“Fail”),
IF(B3=”Degree”,
IF(OR(D3>=50, (D3+E3)>=50), “Pass”, “Fail”), “Nil”))) into cell H3. Then you copy the formula until row 20.

Figure 7.5: VLOOKUP table data

12) Select any of the cells on the list.

Figure 7.6: Source data

13) You type in the data by following Figure 7.6. Once you have finished, proceed on to step 14.
14) Click on Data menu and choose PivotTable and PivotChart Report command.

15) At step 1 of the PivotTable and PivotChart Report Wizard, you choose Microsoft Excel list or database to analyze, and choose PivotChart report (with PivotTable report) option.
16) At step 2, you just click Next button since Microsoft Excel automatically selects the entire list. The range of the list is $A$2:$H$20. 17) At step 3, you put the PivotTable report and PivotChart report on new worksheet.

18) Once you have completed the 3 steps, you will see a blank PivotChart as shown in Figure 7.7.
19) Rename Chart1 to PivotChart and Sheet1 to PivotTable.
20) View PivotTable worksheet.
Before you begin modifying the blank PivotTable, you must understand the components of PivotTable which have data, field types and layout.

7.8.1 Data
•

Source data – The underlying rows or database records that provide the data for a PivotTable report. You can create a PivotTable report from a Microsoft Excel list, an external database, multiple Excel worksheets, or another PivotTable report. Figure 7.6 shows the source data for our PivotTable and PivotChart reports.

•

Field – A category of data that’s derived from a field in the source list or database. The Name, Type of Course, Field of Study are some examples of fields. The field contains names from the source data. E.g. Type of Course has Diploma and Degree course.

•

Item – A subcategory, or member, of a field. Items represent the unique entries from the field in the source data. For example, Diploma, Degree, Architecture, Biology, A, A+, B-, F, etc are items.

•

Summary function – The type of calculation used to combine values in a data field. PivotTable reports usually use Sum for data fields that contain numbers and Count for data fields that contain text. You can select additional summary functions such as Average, Min, Max, and Product to calculate the items,

•

button is a command to update a PivotTable report with
Refresh – This
the most recent data from the source list or database.

7.8.2 Field Types
•

Row field – You can refer to Figure 7.9 to see where row field is located. A PivotTable report that has more than one row field has one inner row field (Field of Study is the inner row field), the one closest to the data area. Any other row fields are outer row fields (Type of Course is the outer row field). Items in the outermost row field are displayed only once, but items in the rest of the row fields are repeated as needed.

•

Column field – You can refer to Figure 7.9 to see where column field is located.

•

Page field – It allows you to filter the entire PivotTable report to display data for a single item or all items.

•

Data field – Data fields provide the data values to be summarized. Usually data fields contain numbers, which are combined with the Sum summary function, but data fields can also contain text, in which case the PivotTable report uses the Count summary function. If a report has more than one data field, a single field button named Data appears in the report for access to all of the data fields.

7.8.3 Layout
•

Drop areas – The blue outlined regions you see when you finish the steps of the PivotTable and PivotChart wizard. To lay out a PivotTable report, you drag fields from the field list window and drop them onto the drop areas.

•

Field list – A window that lists all of the fields available from the source data for use in the PivotTable report. If a field is organized in levels of detail, you can click or to show or hide the lower levels. To display the data from a field in the PivotTable report, drag the field from the field list to one of the drop areas.

•

Field drop-down list – A list of the items available for display in a field. If the field is organized in levels of detail, you can click or to see which lower-level items are selected for display. A double check mark means that some or all of the lower-level items are displayed.

•

Data area – The part of a PivotTable report that contains summary data for the row and column fields. For example cell D7 shown in Figure 7.9 is data area.

•

Indented format – Figure 7.9 shows a classic format of PivotTable. An indented format looks like Figure 7.10.

Now we will begin modifying the blank PivotTable.
21) First of all, you click and drag Type of Course field and drop it onto Row field.
22) Then you click and drag Field of Study field drop it onto Row field but place it to the right of Type of Course field.
23) Next, you click and drag Grade field and drop it onto Column field. 24) Lastly, you click and drag Pass/Fail field and drop it onto Data field. 25) Insert a row above Page field. Then you type in University A End of Semester Summary Report.

26) After that, you format the PivotTable to make the appearance looks as similar as possible to Figure 7.9.

27) Then you switch to PivotChart tab.

28) You have to modify the chart a little bit. Right click on the Y axis and format it.
29) At Scale tab, you make sure the settings for Minimum is 0, Maximum is 5, Major unit is 1, Minor unit is 0.1. Then click Ok.
30) Right click on any Field list and then choose Hide PivotChart Field buttons. You should get a PivotChart that looks like Figure 7.11

Figure 7.7: Blank PivotChart

Figure 7.8: Blank PivotTable

Data field
Column field

Row field
Figure 7.9: Modified PivotTable

Figure 7.10: Indented format

Figure 7.11: PivotChart

Summary
Data analysis is an important task to produce useful information for decision making. There are many data analysis techniques such as sorting, calculating, summarizing, filtering, formatting and charting. Count functions are used to keep track of frequencies. IF function can allow us to make decision out from two or more possible outcomes. AND & OR functions are logical functions to make decisions as well as goal seeking and lookup function. PivotTable and PivotChart reports can quickly summarize huge amount of data.

Chapter 8
Automating Repetitive Tasks
8.1 Macro
8.2 Recording Macro
8.3 Running Macro
8.4 Buttons

OBJECTIVE
In this chapter, student will learn:
? To understand and use macro to automate tasks
? To add personal macro workbook
? To add buttons with built-in macro to run tasks

8.1 Macro
If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions that are stored in a Microsoft Visual Basic module and can be run whenever you need to perform the task. For example, if you often enter long text strings in cells, you can create a macro to format those cells so that the text wraps.

Macros can be displayed with Visual Basic Editor (VBE). We can use VBE to create, edit, run and debug (fix) Excel macros. Macro appears as statements in VBE code window. We will not look at macro coding in VBE at this level.

8.2 Recording Macro
How do we automate task with a macro? We record out tasks with macro. Recording macro is just like using the video recorder player to record a TV program onto a video tape. The video tape can play back again and again to see whatever video has been recorded down.

Before you want to start recording a macro, you must plan out carefully what task you want to be use to repeat over and over again. After planning, you begin recording the macro. You click on Tools menu

Macro command
Record new macro

subcommand. You will see the Record Macro dialog box appears as shown in Figure 8.1. Every macro must have a unique name. The macro name cannot have any blank space and it must begin with a letter. You can include a shortcut key to quickly invoke this macro at any time. You may include a useful description to describe a macro. The Store macro in section, you have 3 options to choose from.

•

Personal Macro Workbook – If you want a macro to be available whenever you use Excel, select Personal Macro Workbook. A workbook that opens automatically whenever Excel is opened. Any macro stored in this workbook can be used by any open workbook.

•

New Workbook – Store new macro in new workbook.

•

This Workbook – Store new macro in active workbook.

Figure 8.1: Record Macro dialog box

While recording a macro, every single step, mouse clicks and keystrokes will be recorded down into that macro. If you want the macro to run relative to the position of the active cell, record it using relative cell references. On the Stop Recording toolbar (Figure 8.2), click Relative Reference so that it is selected. Excel will continue to record macros with relative
references until you quit Excel or until you click Relative Reference again, so that it is not selected. Once you have finished performing your tasks, you click on stop recording macro button.

Stop recording

Relative Reference

Figure 8.2: Stop Recording toolbar

8.3 Running Macro
Once you have completed recording a macro, you should try running it. There are a few ways to run macros:
• Shortcut keys
• Play macro (ALT + F8) command to select any existing macros • Invoke macro using buttons
If you want to run macro using shortcut keys, you have to specify the shortcut key when you create new macro. Enter a letter in the Shortcut key box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key letter you use cannot be a number or special

character such as @ or #. The shortcut key will override any equivalent default Microsoft Excel shortcut keys while the workbook that contains the macro is open. If you play macro by pressing ALT + F8 keys, you will see Macro dialog box as shown in Figure 8.3.

Run selected
macro one
line of code at
a time

Edit macro

using VBE

Assign macro
with shortcut
key or edit
description

Figure 8.3: Macro dialog box

Select the macro that you want to run and then click on Run button. The moment you clicked on the Run button, Excel quickly runs all coding line of the macro within few seconds. If you cannot see what tasks ran, you can use the Step Into button to run the macro one line of code at a time.

If you want to run a macro from a button, refer to 8.4 Buttons topic.

8.4 Buttons
There are two types of buttons that you can add into Microsoft Excel workbook. One is to add buttons onto worksheet and the other is added to toolbar as a customize button. Adding buttons onto worksheet is simple. Display the Form toolbar. Click on the Button icon and then draw out a button size on the worksheet. Once you have drawn out a button, Excel immediately opens up Assign Macro dialog box. You can assign a macro to the button now or later. It is recommended to rename the button to a meaningful name by right clicking on the button and choose Edit Text. To move a button, you have to right click on the button to get the selection border active. Then click on the border and the move the button around.

Button

Figure 8.4: Forms toolbar

Macros recorded

and stored inside
workbook

Button border
indicates button
is selected

Drawn button
on worksheet

Figure 8.5: Assign Macro to button

Another type of button placed on worksheet is a clickable graphic object. You use the Drawing toolbar to draw out shapes, objects or graphical text first, and then you right click on that object and choose Assign Macro option. Then you assign a macro to this object; just like assigning a macro to a button. Figure 8.6 shows a drawn arrow shape is currently being assigned with a macro.

Assigning macro
to drawn shape

Figure 8.6: Assign Macro to shape/object

The second way to assign macros is to assign them to buttons on customized toolbar. First of all, access Customize command from Tools menu. Create a new toolbar by giving a meaningful name. Then click on Commands tab. Select Macros under Categories. Click and drag out one of the command items onto the new toolbar. After you close Customize dialog box, when you click on the button on the new toolbar, Assign Macro dialog box appears to allow you to assign a macro to that button before you can use it.

Create new

toolbar

Adds
custom
toolbar to
workbook

Added new
toolbar

Figure 8.7: Customize toolbar

Click and drag
command onto
new toolbar

Figure 8.8: Adding custom macros button

You can modify the button such as giving a proper name; change the button image, etc. Figure 8.9 shows a custom button is about to be modified.

Figure 8.9: Customizing custom macro button on new toolbar

Summary
Macro is a series of commands recorded and stored inside workbook that you can use it to automate tasks quicker. Macro can be created by using Visual Basic Editor or creating a new macro to prepare for recording. While recording a macro, any actions, tasks, commands, keystrokes and mouse clicks performed, they are recorded and stored inside a macro. Each macro has a unique name, can be assigned with shortcut keys, and has description and store in any one of three locations. Macros can be run in several ways: shortcut keys, buttons on worksheet and buttons on toolbar. Adding buttons to worksheets are easy and they are flexible. You can draw out shapes and
add macros onto the shapes. Toolbars are easy to add and customize with macros.

BIBLIOGRAPHY
Robert T. Grauer, Maryann Barber. 2005, Exploring Microsoft Excel 2003 Revised, Revised edition, Prentice Hall, New Jersey
H. Albert Napier, Philip J. Judd, Benjamin Rand. 2002, Mastering and using Microsoft Excel 2002, first edition, Course Technology, Massachusetts Philip A. Koneman. 2001, Projects For Microsoft Excel 2000 Advanced, first edition, Prentice Hall, Upper Saddle River, New Jersey

WEBSITES
Excel Tutorial – http://www.usd.edu/trio/tut/excel/
TECH on the Net – http://www.techonthenet.com/excel/index.php Meadinkent.co.uk – http://www.meadinkent.co.uk/excel.htm
Tutorialized.com – http://www.tutorialized.com/tutorials/MS-Excel/1 FunctionX – http://www.functionx.com/excel/index.htm

‹ Previous Post 5 Ways to Overcome Cultural Barriers at Work

Next post › Challenges that Human resources management face

Computer application in business

More about Computer application in business

Can’t wait to take that assignment burden offyour shoulders?