Microsoft Excel with Jaws!

Excel 20XX Basics:

Microsoft Excel is a very powerful and widely applied spreadsheet tool in today's business. Excel has been using by millions of individuals, and it works with Windows and MAC platforms. Excel serves multifold purposes: to manage data records, to analyze data, to create forms and to consolidate results, to find Profit Breakeven for project planning, and for corporate and personal budgeting etc.! It is a spreadsheet software that allows to get organize, store, analyze, and interpret data for personal as well as for business purposes; furthermore, Excel allows as to create, edit, save, and print records or present data in table, pie, charts, and line or in many other type of modern graphics presentation mode. JAWS is a screen reader widely used by blind people in the world for working with computers. If you have previously used Excel 2010, and JAWS 13 or above, you will have no problems working with this lesson. However, if you have more experience with older versions of Excell and JAWS, or if you are new to Excel and JAWS you should first familiarize your self with the Excel and JAWS interfaces.

This is synthesized approach for learning basic Excel and JAWS skills for all visually impaired people for getting done tasks. In this lesson you will learn, basic Excel skills such as how to create, enter data, edit, check spelling, save, print reports & charts using JAWS as your screen reader. Furthermore, you will be able to learn how to format data, calculate totals & summaries thru formulas, highlight data that meets certain conditions through the use of keyboard shortcuts at your own pace from your own convenient place.

What is Excel?

You have learned a simple definition of what is Microsoft Excel in my previous article, and now I will present concise definition of what is database! A database is a collection of information organized to provide efficient retrieval when needed. The data collected could be in any number of formats (printed, electronic, audio, or graphic. It could be an organization of names in an address book or as complex as a database that provides information in a combination of formats in tables, queries, reports, views, and other forms or objects. The data are organized to model version of reality in a way that supports processes requiring information, such as modelling finding records from a phone book, address book or Census Bureau data. A group of bits are a byte; a group of bytes are a field; a group of fields are a record and a group of records are a file or database. Therefore, in Microsoft Excel the columns are fields; rows are records and records are a file or database. That's set for now, later I will talk about database concept in detail when I will introduce the Microsoft Access lesson. Now I will illustrate how to use Microsoft Excel for personal as well as for business purposes by defining the following given case or problem for practice, and here is the problem:

The GZM America Inc. makes eye glasses and has 10 employees. The owner of the company Mr. TuTu wants you to develop contacts address book and maintain accurate records of the employees in Microsoft Excel. In addition of that Mr. Tutu wants you to provide weekly timesheet form for the employees. Mr. Tutu wants you to create 10 years sales forecast starting 2016 using simple three moving average technique without adding any seasonal factors and wants to see minimum, average, and maximum sales for the year. On January, February, and March of 2016 sales were $100,000; 150,000; and 200,000. Finally, he wants to see income statement for each year to plan for tax purposes and help share-holders and investors understand what's going on with GZM America Inc: the Eye Glass company!

1. The first problem is to create database of employees.
2. The second is to create weekly timesheet form for the employee.
3. The third sales forecast using the given data and indicate minimum, average and maximum sales.
4. The last one is to generate income statement for each past and future year.

To create database first let's identify the data needs to be collect and maintain for the GZM Inc's employees. When collecting data, it's depend the purpose needs to be satisfy; therefore, in this case the data need is for business purposes to retain and maintain the GZM America Inc's employees record and here are the identified data fields or variables to be collect; you may add or discard some of the data fields depend your purpose! You have identified the following data fields from the given problem by Mr. Tutu; and here are the data fields or variables: Employee-ID, First name, Middle initial, Last name, Address, City, State, Zip code, Home phone, Cell phone, E-mail address, Hired date, Status, Hourly rate, Weekly worked hours, Total payment, and Memorandums. Now you have identified the data field needs to be collect and let's create the database file!

When you open Excel 201x, the Excel Start Screen appears. This Excell start screen let's you create a new workbook, choose a template, and access your recently created or edited workbooks. Now, let's create the database for the GZM America Inc's employees!

1. Press the windows logo key (the windows logo key is as same as the start menu!); at the edit dialog box type excel, when MS-Excel icon pops up, JAWS will say, "Microsoft Excel 201x" then press the enter key. (Excel will load up in rows and column grid.)
2. Create your database in excel by entering data from the following three tables, or copy the tables one at a time and paste it into Microsoft Excel spreadsheet!
1. Press the letter 't' to move forward from table to table, and press 'shift + T' to move backward !
2. Pres 'CTRL + Alt + right arrow' to move forward column by column. 'CTRL + Alt + left arrow' takes you backward!
3. Pres 'CTRL + Alt + Home' to go to the end of the table; and pres CTRL + Alt + End' to go to the beginning of the table.
3. Press 'insert + T' to make sure that you are in your instructional materials and locate your pointer or curssor at the top first field in the table which is the 'EmployeeID'!
4. Press 'Shift +right arrow' repeatedly until you hear JAWS say, "End of table" then pres 'CTRL + C' to copy the data table.
5. Pres 'Insert + F10' to list the running applications you have.
6. Press 'Read line (Insert + up arrow)' if that says, "Book xx excel maximized" then press the enter key, if not move by down arrow and click on excel window.
7. Pres 'CTRL + V'' to paste the data from the klickboard to the excel workbook.
8. Pres 'F12' to save the file; and name it as: 'GZM-EDB' It will be saved in default folder!
9. Aftewr you save the data go back to your lesson's screen, and follow the instructions under the next table which is 'The GMZ America Inc Employees' records' data table.

Formatting is an essential activity to create presentable data document. Probably you should spend 60% of your time formatting the document to prepare a report for presentation purposes! Microsoft Excel is quite simple to enter, format and handle data; in addition, excel grid nature let's us store & manage thousands of data points with ease. Built in features like find, highlight, go to, pivot table, copy, paste, styles etc. it has many tools that help to maintain data effectively and efficiently.

Now let's format the data you copied from the data table to the Excel workbook. As visually impaired person you will use keyboard shortcuts to format the document. Therefore, you are lucky enough to have knowledge of keyboard shortcuts that make you more productive. Because knowledge of keyboard shortcuts in excel improves productivity! In the next table, I will present Keyboard Shortcuts for basic Excel tasks.

10. Make sure that you are in excel worksheet by pressing the read title keys!
11. Turn on the JAWS vertual ribbon by following the instructions below:
1. press the JAWS or insert key + F2, then press down arrow when JAWS says, "Setting center" press the enter key
2. Press CTRL + Shift + D to load up JAWS default setting
3. At the edit dialog bax type "Ribbon" then down arrow when JAWS says, "Vertual Ribbin" check it and click OK.
12. Press CTRL + Home to go to the start of worksheet. CTRL + Home takes you to cell "A1" from any place in the worksheet!
13. Press CTRL + right arrow and JAWS will say, "XFD1" (XFD1 is the last cell address in this row and version of excel 2016), depend the version of excel there are more than 65 thousand columns.
14. Press CTRL + left arrow again, JAWS reads cell content first, then cell address (A1). press right arrow then left arrow just for practice and JAWS will say, "B1" and "A1". Do you notice that row-1 doesn't have any usable information?
15. Press down arrow JAWS will say, "EmployeeID A2" and that is the first field or column header in the workbook. There are 19 fields or column header in this row. Press right arrow until JAWS says, "Memo" and that is the last field. Press the home key JAWS will say, "EmployeeID A2". The focus is now on cell a2 on the EmployeeID
16. Press CTRL + right arrow the pointer will go to the last data range in the row and JAWS will say, "Memo S2"; again press CTRL + right arrow the pointer will go to the end of this row because from the Memo field to the end of the row, there is no data within any cell; thus, JAWS will read the last cell address as, "FDX2"
17. Press CTRL + left arrow your focus will land on cell address "S2" and JAWS will say, "Memo S2"; again press CTRL + left arrow the pointer will move and JAWS will say, "EmployeeID A2"; however, if you press the home key from the end of the row means from "XFD2" the pointer will go to the beginning of the row to "A2". Make a note of the cursor, focus, or pointer movement and JAWS response! In this context the words cursor, focus, or pointer means the same thing.
18. press CTRL + down arrow the focus will move downward to "a12" and JAWS will say, "10 A12" means employeeID #10 and A12 is the cell address. JAWS first reads the cell content then the cell address! Press CTRL + down arrow again, the pointer will go to the end of the column and JAWS will read the last cell address in this column as: "A1048576", in Excel 2016. Do vice-versa and learn JAWS responses!
19. Practice how to navigate the worksheet as I instructed above, in this worksheet the data ranges are from A2 to S12! Watch carefully, how JAWS interacts with Microsoft Excel.

1. Go to the Excel Worksheet and move up to cell A2 which is the cell address where the column heading in row 2 and the row heading column A intersect. While the focus is in A2, press the following hotkeys to set row and column titles.
1. Press INSERT + ALT + CONTROL + C to set the column titles to the current row 1.
2. Press INSERT + ALT + CONTROL + R to set the row titles to the current column A.

1. Load up Microsoft Excel and open the GZM-EDB file.
2. Locate the focus on cell A2 - this is the cell where the column heading and the row heading intersect. (At this intersection (cell A2), you will set the read column titles and row titles together.)
3. Trigger the vertual ribbon menu by pressing the ALT key then move at the upper ribbon by right arrow to the Formulas tab. Also you can use the keyboard shortcuts: CTRL + F3, or ALT + M keys to go to the formulas tab.
4. At the Formulas tab move by DOWN ARROW key to the lower ribbon until JAWS says, "Define Names" at that point press the enter key; again at "Name Manager Button" press the spacebar. (Depend the Excel version, the keyboard shortcut key sequence is ALT, M, N.)
5. Focus is on the New Name dialog boxand press spacebar; and enter the required field which is: "TitleRegion1..xx (The xx is the cell address of bottom right end.)". Then TAB to explore the options.

With focus in the Name field, and if you are setting both row and column titles at the same time, you can type the required label: "TitleRegion1..xx (The xx is the cell address of the bottom right end.)" exactly as it is. The number on the end refers to the position of the last cell address in a worksheet. If you work on the second worksheet you write the required label as: "TitleRegion2..xx", for the third worksheet: "TitleRegion3..xx" etc.

1. Excel keyboard shortcuts in action:
1. Ctrl + F3 Define name; open Name Manager dialog box

All data: numbers, text, charts & drawings go into the spreadsheet grid. The active spreadsheet grid shows rows & columns; however, a workbook or a file can contain many sheets. Therefore, when working with data make sure you are working with the intended data and worksheet; to verify that you are working in the right spreadsheet grid, press the JAWS key + T (It reads the working spreadsheet title), also press the read status line which is JAWS + PGDN keys to help verify status of document. If you would like to see more rows or columns you can use RIGHT / LEFT/ UP/ DOWN and TAB keys, or USE the scroll bars to the left or AT bottom. To access other worksheets, use the keyboard shortcut CTRL + Page Down to go to the next worksheet, or CTRL + Page Up to go to the previous worksheet, or click on the sheet name. Now follow the instructions below and practice your excel data formatting skills; don't forget, that you are working with GZM-EDB excel 20xx file!

• Call Microsoft excel program and open the GZM-EDB file. Use the proper JAWS read keys such as: read title bar, read status line, read line ... to appropriately manage your task.

When the width of the column adjusts itself to hold the contents of the cell, we call it AutoFit. If you have a usable sight, you can use the mouse and double-click to AutoFit when the cursor is between columns at the resize column cursor. If you want to resize the column for the address field in column G, instead of the constant column width content in column A. To achieve this, use the AutoFit Column Width button and it is located on the Home tab of the Ribbon in the Format menu. The AutoFit Column Width button bases on the region selected. Therefore, the column width will be adjusted to fit the contents of the selected region whether it is a single column, or multiple columns. The AutoFit Column Width Tool Resizes on Selected Cell, column, or region Contents.

1. Locate the cursor on cell A2.
2. Press CTRL + Shift + space bar to select the entire data range; or press Ctrl + Shift + 8 to include the entire region.
3. Type Alt + H to select the Home tab on the Excel ribbon, and type O to select the Format menu in the Cells sub-menu, Type I to select 'autofit column width', or
4. Trigger the vertual ribbon menu by touch and release the ALT key quickly. Move by DOWN ARROW to the CELL SUB- MENU, FORMAT BOTTON, then to AUTOFIT option.

1. Instead of CTRL + SPACEBAR keys to highlight the entire column A, press SHIFT + DOWN ARROW, until JAWS says, " 10, A12" or press CTRL + SHIFT + DOWN ARROW.
2. Alt, O, C, W is the keyboard shortcut to open the Column Width window; therefore, type Alt + H to select the Home tab on the Excel ribbon, and type O to select the Format menu in the Cells sub-menu, Type W to select COLUMN WIDTH , focus will land on the edit box and type 5 for column with and click on OK, or
3. >Trigger the vertual ribbon menu by touch and release the ALT key quickly. Move by DOWN ARROW to the CELL SUB- MENU, FORMAT BOTTON, then press the enter key on Column Width, type 5 on the edit box and click the OK button.

• When ever change you make to the document, press CTRL + S TO SAVE IT; THUS, NO WORRY IN CASE OF POWER FAILER .

NOTE: in Microsoft Excel, from 19 fields that we have in our GZM-EDB file, the EmployeeID, FirstName, MiddleInitial LastName, Address, City, State, Position, Status and Memo fields are text type; however, the ZipCode is zip code type. The BirthDate and HiredDate are date type. The HomePhone and CellPhone fields are phone type. The e-mail field is link type. The HourlyRate and TotalPayment fields are currency columns and the WeeklyWorkedHours is time type column. In addition Excel provides Social Security Number (SSN) data type. (Field name or column name means the same thing; row name or record name means also the same thing.) Now let's group and assign them according to their data type.

1. Press CTRL + HOME key to go to start of worksheet and focus lands on Cell A1.
2. Press the F5 key or CTRL G (F5 or CTRL G keys are known as "Go to dialog box" keys.) then type h2 at the reference edit box and press ENTER. Focus lands on H2 which is the Address field.
3. Hold down Shift while you press the right arrow when JAWS says, "ZipCode" The (ZipCode field is on K2)" at that point press the CTRL + SHIFT + DOWN ARROW once, and the data range from H2 to K12 will be selected.
4. Press the hotkey CTRL + X TO CUT THE selected DATA RANGE TO THE CLICKBOARD.
5. Press F5 or CTRL G, at the reference edit box type E2 and press the ENTER key. Focus lands on E2 which is the BirthDate field.
6. Press CTRL + SHIFT + = (THE = SIGN), the selected data range moves to E2 and the BirthDate column is pushed to the right.
7. Press F5 or CTRL + G, at the reference edit box type K2 and press the ENTER key. Focus lands on the position field.
8. Press CTRL + SHIFT + DOWN ARROW to select data range from K2 to K12. Again press CTRL + X to cut the selected data to the clickboard.
9. Press F5 or CTRL + G, at the reference edit box type I2 and press the ENTER key. Focus lands on the BirthDate field, at that point press CTRL + SHIFT + = (the = sign) the selected data will be inserted and the BirthDate field will be pushed to the right.
10. Press F5 or CTRL + G, at the edit box type O2 and press the ENTER key. Focus lands on the Status field.
11. Press CTRL + SHIFT + DOWN ARROW once to select data range from O2 to O12. Again press CTRL + X to cut the selected data range to the clickboard.
12. Press F5 or CTRL + G, at the reference edit box type J2 and press the ENTER key. Focus lands on the BirthDate field, at that point press CTRL + SHIFT + = (the = sign) the selected data will be inserted and the BirthDate field will be pushed to the right.
13. Press GO TO command: (F5 or CTRL G), at the reference edit box type B2 and press the ENTER key, focus lands on FirstName column which is Cell B2.
14. Press the insert command which is CTRL + Shift + = (= sign), the insert menu will pop up, press UP ARROW key when JAWS says, "Entire column" press the ENTER key. Now, a blanck field or column has inserted The FirstName column is pushed to the right and Cell B2 is now a blanck new field.
15. Type on the blanck field "SSN" as column B2 field name, then press the ENTER key. When the ENTER key is pressed, focus lands on next Cell Address within the vertical column like: B3, B4, B5 ... Now enter the following SSN for B3 to B12: {Cell (B3 = 100-000-0010, Cell B4 = 100-000-0011, Cell B5 = 100-000-0012, Cell B6 = 100-000-0013, Cell B7 = 100-000-0014, Cell B8 = 100-000-0015, Cell B9 = 100-000-0016, Cell B10 = 100-000-0017, Cell B11= 100-000-0018, Cell B12 = 100-000-0019.}

Let's move to the next step now, which is defining the data type and formatting the spreadsheet grid. Before we define data type of each field let's do important step that is to leave at least three preferably more blank rows above the table, and these rows can be used for Documentation, Advanced Filtering and Database functions. Also, the data alignment must be understood; by default, text fields in Excel are left-aligned, numbers are right-aligned. Leave the data alignments at their default setting. Changing their default setting doesn't help to tell, if the contents of a field or a cell is text or numeric value at a glance. If a default alignment altered, a cell reference may look like numbers but are text; thus, if you do not have good enough reason, don't change its' default setting.

1. Open the GMZ-EDB file; however, if it is already open, switch to the Worksheet and presst CTRL + HOME to go to the top of the worksheet.
2. Pres CTRL + SHIFT + = (= sign), and the insert menu pops up. press up arrow until JAWS says, "Entire row" and press the ENTER key. You have inserted one more blanck row and focus is on Cell A1.
3. Repeat instruction #2 and you will have three blanck rows. Press CTRL + S to save the change you have made.
1. Press (F5 or CTRL G, at the reference edit dialog box type B4, and press the ENTER key, and focus lands on b4 at the SSN colum.
2. Press SHIFT + DOWN ARROW to select data range from B4 to B14. JAWS will say, 颼-000-0119. B14.' JAWS first reads the cell content then the cell address.
3. Press CTRL + #1 and JAWS will say, "NUMBER TAB"; however, if JAWS doesn't say "Number Tab", press CTRL + TAB repeatedly after you hear the words "Number Tab" press the TAB key once.
4. Move by DOWN / UP ARROWS, when JAWS says, 'Special", press the TAB key once again. Press DOWN/UP ARROWS, when JAWS says, 'Type Social Security#', press the ENTER key, and window will be closed and focus will land on SSN field, Now column B range of B4/B14 has defined as SSN data type.
1. Press GO TO Dialog Box command (F5, or CTRL G) at the reference edit box type I4, and press the ENTER key, focus lands on I4 the ZipCode colum.
2. Press SHIFT + DOWN ARROW to select data range from I4 to I14. JAWS first announces that cell content then tells the cell address 'I14.' JAWS first reads the cell content then the cell address.
3. Press CTRL + #1 and JAWS will say, "NUMBER TAB"; however, if JAWS doesn't say "Number Tab", press CTRL + TAB repeatedly after you hear the words "Number Tab" press the TAB key once.
4. Move by DOWN / UP ARROWS, when JAWS says, 'Special", press the TAB key once again. Press DOWN/UP ARROWS, when JAWS says, 'Type Zip Code', press the ENTER key, and window will be closed and focus will land on ZipCode field. Now column I range of I4/I14 has defined as Zip Code data type.
1. Press GO TO command Dialog Box (F5, or CTRL G) at the reference edit box type L4, and press the ENTER key, focus lands on L4 the BirthDate colum.
2. Press SHIFT + RIGHT ARROW once, to include column M (HireDate) field. Again press SHIFT + DOWN ARROW, from cell L4/M14 will be selected.
3. Press CTRL + #1 and JAWS will say, "NUMBER TAB"; however, if JAWS doesn't say "Number Tab", press CTRL + TAB repeatedly after you hear the words "Number Tab" press the TAB key once.
4. Move by DOWN / UP ARROWS when JAWS says, 'Date', press the TAB key once. Press DOWN/UP ARROWS, and JAWS will read different date formats and press the ENTER key at the right choice for you. Window will be closed and focus will land on BirthDate field, Now column L, and M range of L4/M14 have defined as Date data type.
1. Press GO TO Dialog Box command (F5, or CTRL G) at the reference edit box type N4, and press the ENTER key, focus lands on N4 the HomePhone colum.
2. Press SHIFT + RIGHT ARROW once, to include column O (CellPhone) field. Again press SHIFT + DOWN ARROW, from cell N4/O14 will be selected.
3. Press CTRL + #1 and JAWS will say, "NUMBER TAB"; however, if JAWS doesn't say "Number Tab", press CTRL + TAB repeatedly after you hear the words "Number Tab" press the TAB key once.
4. Move by DOWN / UP ARROWS, when JAWS says, 'Special", press the TAB key once again. Press DOWN/UP ARROWS, when JAWS says, ''Type Phone Number', press the ENTER key, and window will be closed and focus will land on HomePhone field, Now column N, and O range of N4/O14 have defined as Phone Number data type.
1. Press GO TO Dialog Box command (F5, or CTRL G) at the reference edit box type Q4, and press the ENTER key, focus lands on Q4 the HourlyRate colum.
2. Press CTRL + SHIFT + DOWN ARROW to select data range from Q4 to Q14. JAWS will read the content of cell address and 'Q14.' JAWS first reads the cell content then the cell address.
3. Press CTRL + #1 and JAWS will say, "NUMBER TAB"; however, if JAWS doesn't say "Number Tab", press CTRL + TAB repeatedly after you hear the words "Number Tab" press the TAB key once.
4. Move by DOWN / UP ARROWS, when JAWS says, 'Currency", now instead of pressing the TAB key, press the ENTER key, and window will be closed and focus will land on SSN field, Now column Q range of Q4/Q14 has defined as Currency data type.
1. Press GO TO Dialog Box command (F5, or CTRL G) at the reference edit box type R4, and press the ENTER key, focus lands on R4 the WeeklyHoursWorked colum.
2. Press CTRL + SHIFT + DOWN ARROW to select data range from R4 to R14. JAWS will say ''R14'. JAWS first reads the cell content then the cell address.
3. Press CTRL + #1 and JAWS will say, "NUMBER TAB"; however, if JAWS doesn't say "Number Tab", press CTRL + TAB repeatedly after you hear the words "Number Tab" press the TAB key once.
4. Move by DOWN / UP ARROWS, when JAWS says, 'Number', press the ENTER key, and window will be closed and focus will land on WeeklyHoursWorked field, Now column R range of R4/R14 has defined as WeeklyHoursWorked data type.
1. Press F5, at the reference dialog box type 'S5' and press the ENTER key. Focus lands on cell S5 below the field name.
2. type '=sum(q5 * r5), then press the ENTER key. Focus will land on S6.
3. Press UP ARROW once, and press the hotkey CTRL + C to copy the formula into the click board.
4. Press DOWN ARROW key once; again press CTRL + SHIFT + DOWN ARROW; column S from S6 to S14 will be selected. Press CTRL + V and the formula will be copied to the selected area. Press CTRL S to save the change.
1. Press CTRL + HOME to go to cell A1.
2. Press SHIFT + RIGHT ARROW twice and then DOWN ARROW once without releasing the SHIFT key.
3. Press CTRL + 1 to open format command cell, then press CTRL + TAB repeatedly until JAWS says, "'Alignment Tab".
4. Press the TAB key when JAWS says, "Merge Cell Unchecked", check it and click ok. Focus will land on the merged cell A1/C2.
5. Type "The GZM America Inc Employees' Records." Press the TAB key and save the file.
6. press the F5 key, at the reference edit dialog box type A15 and focus will land on A15.
7. On column A type 'Totoal' as a label for row 15.,
8. On column Q, type "=sum(q4..q14)" and copy this formula, and paste it into cell address R15 and S15.
9. If any specific cell address obscured, Focus to that specific Cell Address and apply the AutoFit format (ALT + H, O, I.)

1. Locate the cursor on Cell A4
2. Trigger the vertual ribbon menu by pressing the ALT key then move at the upper ribbon by right arrow to the Formulas tab. Also you can use the keyboard shortcuts ALT + M keys to go to the formulas tab.
3. At the Formulas tab move by DOWN ARROW key to the lower ribbon until JAWS says, "Define Names" at that point press the enter key; again at "Name Manager Button" press the spacebar. (Depend the Excel version, the keyboard shortcut key sequence is ALT, M, N.)
4. Focus is on the New Name dialog box, and press the spacebar; and enter the required field which is: "TitleRegion1..xx (The xx is the cell address of bottom right end), in this case the XX is T15. Then TAB to explore the options.
5. Focus should remain on A4, then press CTRL + ALT + INSERT + C, and JAWS will read the column headers.
6. Locate the cursor on cell A5, again press CTRL + ALT + INSERT + C, now JAWS will read column and row headers. Whenever field or column names or headers are relocated, do not forget to redefine Excel accessibility and JAWS usability for column headers.

Rrows or columns can get hidden by using the Hide command, also a row height or a column width can get assigned to 0 (zero) to hide them. Rows and columns can come to forth either by applying the Unhide keyboard shortcut or click commands. Specific rows and columns can be hidden, or all hidden rows and columns can be visible at the same time. To hide rows or columns you just need to select range of data (cells) in the rows or columns you want to hide, then press Ctrl + 9 to hide rows, or Ctrl + 0 (Zero) to hide columns. CTRL + SHIFT +(shortcut 9 is if you want To unhide rows, or CTRL + SHIFT + 0 (ZERO) is if you want to unhide columns. First you need to select the cells that surround the rows or columns that need you want to unhide. For example, to unhide rows 5 & 6, first select cell B4 and B7, cells that surround or cover the hidden rows, then press Ctrl + Shift + 9 to unhide the rows. Select Cells That Surround Hidden Rows or Columns, then press CTRL + SHIFT + 9 to Unhide rows, or pres CTRL + SHIFT + 0 (ZERO) to unhide columns. However, if the first row or column is hidden, it requires different process to make it visible. For example, if column A through column J are hidden, first press the GO TO DIALOG BOX (F5 or CTRL G), at the reference edit box type "A1..I1 and press the ENTER key, then go to the ribbon HOME TAB, cell format, HIDE/UNHIDE, UNHIDE COLUMNS will unhide them. If row A1 through row A5 are the hidden rows, press F5, type A1..A5 at the reference edit box and press the ENTER key; trigger the ribbon, go to the HOME TAB, CELL FORMAT, HIDE/UNHIDE, UNHIDE ROWS WILL UNHIDE THEM.

You don't present raw data to communicate your audiance, rather your goal is to interpret what the data mean and communicate about the data story with business decision makers, with your boss and with others. Spread-sheets are about making it easy for users to get correct information and presenting information in meaninful way; therefore, most relative information must be contracted from a huge range of data, so it can be visible and and informative. Hence, to achieve the needed objectives, I will show you how to hide / unhide Excel columns/rows in Microsoft Excel. But first, let me introduce you to more technics how to select range of data in a worksheet.

You've learned how to create a document in Excel, and how to define data type, size, name manager, (For accessibility), how to navigate excel workbook, How to use the virtual ribbon, keyboard short cuts, formulas, how to select and format cell, column, row, spreadsheet and workbook, how to group data range for serving a purpose, ... above all you've learned how to manage Microsoft Excel program with JAWS (The widely used screen reader by blind people). However, Excel service infinite purposes. What you can do with Excel there is no real limit, The sky's the limit. ! It is an art, it's up to your creativity and imagination. Now, let's apply some keyboard shortcuts to format the fields and let's change text fonts for the GZM-EDB database, and present the data in table and pivotTtable format for report purposes; therefore, follow the steps below! But first, let me present some keyboard shorcuts.

1. Toggle bold formatting = Ctrl + B
2. Toggle italic formatting = Ctrl + I
3. Toggle underline formatting = Ctrl + U
4. Open Style dialog box = Alt + ' (apostrophe)
5. Apply outline border to selected cells = Ctrl + Shift + & (ampersand)
6. Remove outline border from selected cells = Ctrl + Shift + _ (underscore)

Load up Microsoft Excel and call the GZM-EDB database. Change font-size to 12 points, and font-family to Tahoma. Make the fields name 15 points in size, centered, bold and underlined. Highlight for the total cell addresses. Finally, view employs' number, first name, last name, hourly pay, weekly worked hours and weekly pay; first in table format, then in pivot table! Lastly, show the first name and total payment in pie graph and print each report.

1. Load up Microsoft Excel then call the GZM-EDB file.
2. Go to A4, and press JAWS tel me cell format key which is JAWS + F. (JAWS will tell you the cell format info.)
3. Highlight the entire data range, you may press CTRL + A to select it.
4. Pres CTRL + SHIFT + F, and control box for the font tab will pop up. Focus will land on font TAB. Press the TAB key once.
5. Press down arrow when JAWS says, "Tahoma", press the TAB key once, and focus will be at font style list box, press down arrow once JAWS will say, "Regular". then press the TAB key once and focus will land on font size.
6. Press down / up arrow to change the font-size to 12 points, then press the ENTER key and focus will be at your data sheet. If you want, you may explore the other options in the font TAB.
7. Go to A4, pres JAWS + F, to get the cell format info. and save your work.
8. Make sure focus is on cel A4, select row 4 by pressing CTRL + SHIFT + RIGHT ARROW. Now cell A4 to cell T4 is selected.
9. Press CTRL + SHIFT + F to change font-size for the selected fields. Focus is on font-TAB, press the TAB key three times and focus will land on font-size. Change font size to 15 points and press THE ENTER KEY. Focus is back to row 4. Row 4 is still highlighted.
10. Make sure the TABLOCK key is not on! Press CTRL + B to highlight, and CTRL + U to underline the selected fields.
11. Double check the fields format by pressing JAWS + F keys and JAWS will read all fields format info for you.
12. Select cell Q15, S15, R15 and highlight them (These are totals for hourly rate, weekly worked hours and total pay).

Before printing an Excel workbook, it's important to know exactly what related information is going to be printed. If there are more than one worksheets in the workbook, one must know if the entire workbook or only active worksheets need to be printed. However, in your working case you have a single spreadsheet data and the requested information to be print include: first name, last name, hourly rate, weekly worked hours, and total payments. They need to be printed in table, pibot table and in graph format. Therefore, fields that are not related should be hidden. Before hiding the unrelated data range, it must be selected. Even if you want to select nonadjacent cells or cell ranges, select the first cell or range of cells, and then hold down Ctrl while you select the other cells or ranges. You can also select the first cell or range of cells, and then press Shift+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press Shift+F8 again. However, You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without canceling the entire selection. Practice the following instructions!

1. Locate focus on A4, select A4 to B4; (Hold down the SHIFT key, then press RIGHT ARROW key once) to select EmployeeID and SSN fields to be hidden first. Press CTRL + 0 to hide them.
2. Press GO TO (F5), type D4 at the reference box and press ENTER key, focus lands on MiddleInicial field, and press CTRL + 0 to hide that column.
3. Press GO TO (F5), at the reference box type F4 which is the address field, and press the ENTER key. Focus lands on F4, then select F4 to P4. Hold down the SHIFT key and press RIGHT ARROW 10 times. Now press CTRL + 0 to hide the columns.
4. Press again the GO TO (F5) type T4 at the reference box and press ENTER, focus will land at the Memo field. Press ctrl + 0 to hide the column.
5. Press GO TO (F5) and type C4 at the reference box and press ENTER, focus will come to FirstName field.
6. Pres CTRL + A to select all visible data range from cell C4 to S15, and press CTRL + T. Hoo, hoo ... you have an amazing good looking table.
7. Pres CTRL + HOME to go to top left of the spreadsheet. Select C1 to S3 (Hold down the SHIFT and press RIGHT ARROW FOUR TIMES THEN TWO TIMES down arrow. cell C1 to S3 have selected.
8. Press GO TO CELL FORMAT (CTRL+ 1), again press CTRL + TAB until JAWS says, "Alignment tab" at the Alignment tab press the TAB key and check the merge cell box and press ENTER.
9. Focus is on the merged cells, type now "The GZM America Inc" and press ENTER.

1. Press CTRL + P, focus will land on PRINT TAB.
2. Press just the TAB key once, and focus will land on the first control which is the PRINT BUTTON. NOTE: if focus lands on other control box, JAWS will read it for you and follow the instruction accordingly, (Where focus lands differ depend MS Excel version.) The TAB key will move focus from one control box to the next.
1. The PRINT BUTTON: This may, or may not the first option in the control dialog boxes. Therefore, first make all needed setting adjustment and selections then Press SPACEBAR on print button to print your document.
2. NUMBER OF COPIES SPIN BOX: At this SPIN BOX specify number of copies you want to print, then press TAB to go to next control box.
3. PRINTER GROUP BOX: Press UP/DOWN arrow to select the right printer name and model you want to use, press TAB for the next control box.
4. PRINTER PROPERTY: This is a link and it will take you to a set of printer functions and this is where you can save your selections for default setting, press TAB for next control box.
5. SETTING GROUP: Press UP/DOWN arrow to select, and the options are: 'Print entire workbook, Active spreadsheet, Active selection'. Select 'Active Spreadsheet' then press TAB for the next control box.
6. PAGES SPIN BOX: Pres UP/DOWN arrow to select starting page number for your document, press TAB for next control box.
7. TO EDIT SPIN BOX: Press UP/DOWN arrow to select ending page number for the document, press TAB for the next control box.
8. PRINT ONE SIDED or TWO SIDED: Press UP-DOWN arrow to select, and press TAB for next control box.
9. COLLATION COMBO BOX: Press UP-DOWN arrow to select, if one copy contains multiple pages, you can switch between Collated and Uncollated. For example, if you print 10 copies, collated prints the entire first copy, then the entire second copy, .... Uncollated prints 10 copies of page 1, 10 copies of page 2, etc press TAB for the next control box.
10. ORIENTATION COMBO BOX: Press -UP/DOWN arrow to select, you can switch between Portrait Orientation (more rows but fewer columns) and Landscape Orientation (more columns but fewer rows), press TAB for next control box.
11. PAGE SIZE COMBO BOX: Press UP-DOWN arrow to select needed page size, press TAB for next control box.
12. MARGIN COMBO BOX: Press UP-DOWN arrow to adjust the page margins, the predefined margins are: 'Normal, Wide or Narrow', or press ENTER the 'Customize' or 'Show Margins', and you can manually change the page margins, press TAB for next control box.
13. SCALE TO FIT COMBO BOX: Press UP-DOWN arrow to select 'Fit Sheet on One Page', or 'Shrink the printout to one page wide or one page high', or 'Click Custom Scaling' Options to manually enter a scaling to fit the printout to a specific number of pages by percentage or width and height.
3. Finally, go to the PRINT BUTTON and press the SPACEBAR or ENTER key to print your document. Now you have a nice hard copy of the GZM America Inc employees' needed records in table format.

Pivot Tables are very helpful and it has been using to present information in a matrix form. Pivot tables have a lot of advantages in presenting data and allows us for simple and quick data analysis. It has never being much easier to change summary from total, differences from previous records, calculated fields in pivots ... A pivot table stores duplicate of data in a pivot cache, and a pivot cache explores data with rapid pivot data matrix. A pivot cache is a copy of the source document; hence, you can remove the source data from your excel workbook, if you want.

Building pivot tables are incredibly fast; however, the source data must be organized in a tabular layout. The source data should not contain blank rows or columns, and it needs no subtotals. Each column should have a unique field name and every field should contain a value in every row. Columns should not hold repeated records of data (i.e. (Employee Id, last name, first name, address etc.)

When you create a pivot table make sure the pivot table is reading the data correctly, and make sure the pivot table is processing the data as you wish. To do this in your working case which is the GZM-EDB file as a source data, simply use the Last Name text field as a Value field and it will display the total record count, that is, the total number of rows in the source data. This number should be 10 because you have ten records; and if it is, you're good to go. Nevertheless, if the number is not 10, go back to the source data and correct errors according to the rules above for pivot tables. Now, let's create a pivot table and add First Name to row label, add Last Name to value type for count records purpose, add Hourly Rate as currency to values, add Total Wage as total of the row to values, and here are the steps to follow, but first a couple of keyboard shortcuts:

1. ALTGo to PivotTable & PivotChart Wizard = + D, P, or (ALT, Insert, Tables, Pivot.)
2. Select entire pivot table without Report Filters = Ctrl + Shift + *
3. Select entire pivot table without Report Filters = Ctrl + A
4. Add or remove checkmark for selected field in PivotTable Field List = Spacebar
5. Ggroup selected pivot table items = Alt + Shift + right Arrow
6. Ungroup selected pivot table items = Alt + Shift + Left Arrow
7. Select next item in PivotTable Field List or Items List = Down Arrow
8. Select previous item in PivotTable Field List or Items List = Up Arrow
9. Select last item in PivotTable Field List or Items List = End
10. Select first item in PivotTable Field List or Items List = Home
11. Open field list for active cell = Alt + Down Arrow
12. Hide selected item or field = Ctrl +
13. Open Calculated Field dialog box at selected data field = Shift + Ctrl + =
14. Show Pivot Table dialog box = Alt + L
15. move selected field into Row area = Alt + R
16. move selected field into Data area = Alt + D
In Pivot Table Wizard Shortcut Action
17. move selected field into Column area = Alt + C
18. Move selected field into Row area = Alt + R
19. Move selected field into Data area = Alt + D
20. Move selected field into Page area = Alt + P

1. Microsoft Excel 2016, Insert a Pivot Table:
1. Open the GZM-EDB file, if it is open go to cell a4 which is the first field in your data set. Press CTRL + A to select all data range.
2. Press ALT + d, p, or (On the Insert tab of the ribbon, click the PivotTable button.)
3. Depend the version of your excel, in the Create PivotTable dialog box, check what controls you have and click NEXT.
4. If a second dialog box appears, explore its controls and click on OK or Next. You don't need to make any adjustment, but experiment with it for your own benefit.
5. The third dialog box will appear, and go over all controls just for practice, then click on FINISH or OK.
2. Create Pivot Table, Use right click to Drag fields
1. Focus is on cell A3. Right click (Press Application key), and list of options will pop up.
2. Move by UP/Down arrow, when JAWS says, "Hide field list checked." press THE SPACEBAR or THE ENTER KEY to uncheck it. Focus lands on cell A3 again.
3. Right click again (Press Shift + 10), list of options will pop up.
4. Press UP/DOWN arrow when JAWS says, "Show field list", press THE SPACEBAR, or THE enter KEY, and dialog boxes will appear and JAWS will say, "Type word to search FOR".
5. Press the TAB key and focus will land on list box for the fields name, and JAWS will say, "EmployeeID". (NOTE: you may explore the control dialog boxes at any time for your own confidence.)
6. Move by UP/DOWN arrow when JAWS says, "First Name", right click (Press Application key), list of option will pop up.
7. Move by UP/DOWN arrow, when JAWS says, "Add to row label" press the ENTER key. (Add First Name to row label.) Now, the first name field will be inserted at column A as row label area. Focus will stay at first name field.
8. Move by DOWN arrow and JAWS will say, "LAST name", at that point right click (press Application key", then move by UP arrow when you hear JAWS say, "Add Values to" press the ENTER key. (Add First Name to column values area.) Focus wil stay at last name field.
9. Drag the Hourly Rate field to values area. (As the same you were doing above.)
10. Drag the Hours Worked, and Total Wage fields to values area.
11. Now press Shift + F6, and focus will land on cell A3, at the new inserted pivot table with its fields' header and calculated data.
12. Press JAWS key + NumPad5, to learn where your focus is, and JAWS will say, "Row labels". The first Name field is the row lable and it is now on cell a3.
3. Naming the Work Sheets:
1. Do the JAWS read title window (hold down the JAWS key and hit T), JAWS will first tell the running application then will say, "Sheet two".
2. Hold down CTRL key and hit the PGDWN key to go to the next WorkSheet. Focus will land where you left off which is on the Employee records cell A4. (CTRL + PGDWN moves downward from one WorkSheet to the nex, CTRL + PGUP moves bakward from one WorkSheet to the nex within the WorkBook.)
3. Again do the JAWS read title window (hold down the JAWS key and hit T), JAWS will first tell the running application then will say, "Sheet one". Have you realised that you have WorkSheet one and WorkSheet two?
4. Press ALT + H, at the HOME TAB, press DOWN arrow when JAWS says, "Cell submenu", pres ENTER, at the FORMAT Button, pres Enter again.
5. Move by UP arrow when JAWS says, "Rename Sheet", press ENTER, focus will land on type new name edit box.
6. Type "Employees' Record Table Sheet1", and press ENTER, focus will move to the data sheet! (Document the changes you have made. Tell what is the content of the sheet, and what purpose or type of information can be contracted from this data sheet). Do read title window to verify that the sheet one name has changed.
7. Press CTRL PageUP to go to previous Worksheet. Press the read title window (JAWS + T) to verify that you are working on Worksheet two.
8. Press ALT + H, at the HOME TAB, press DOWN arrow when JAWS says, "Cell submenu", pres ENTER, at the FORMAT Button, pres Enter again.
9. Move by UP arrow when JAWS says, "Rename Sheet", press ENTER, focus will land on type new name edit box.
10. Type "Employees' Pivot Table Sheet1", and press ENTER, focus will move to your data sheet. (Do not forget to update your documentation along your way. Proper documentation will help to simplify access of the Work Book.)Do read title window to verify that the sheet two name has changed.
4. Sort Data in a Pivot Table:
1. Open the GZM-EDB file, if it is open go to Employee's Pivot Table Sheet.
2. Go to cel Cell C4, which is the first data item in your HourlyRate field.
3. Right click (Shift + F10, or Aplication key).
4. Press DOWN arrow when JAWS says, "Sort submenue", hit the ENTER KEY.
5. Move by DOWN Arrows, from the list of commands, click on 'Sort highest to lowest, or Z to A', now your pivot table is sorted in HourlyRate field in descending order; save your Work Sheet.
6. To sort by a row go to cell A4, and follow the same instructions above; save your work and make a note of all the change you have.
5. Sort Automatically everytime the report is updated:
1. Right click (Shift + F10, or the Application key) and hit the ENTER key on Pivot Table options.
2. On the Layout TAB, find the autofit checkbox and check it
3. Press CTRL + TAB and explore each TAB and control dialog boxes for your own benefit.

NOTE: The type of chart that can be selected for analysis and reporting depends upon the type of data that needs to be analyzed and report, and what the data is for. Therefore, one needs to really learn and know when and what data chart to use. In your case, you want to show the employees' Total Wage variable in terms of percentage, and it is just two columns, and you will use pie chart. If you want to apply different chart to satisfy for different use of data, you will follow the same procedure that I am showing to you.

1. Open MS Excel and navigate to the GZM-EDB Employees record spreadsheet and go to cell A4.
2. Hit the ALT key; at the INSERT TAB, go downward when JAWS says, Tables press ENTER, at PIVOT TABLE BUTTON hit ENTER.
3. When JAWS says, create pivot table edit box , hit ENTER (The default setting is accepted). Focus lands on Cell A3.
4. Press SHIFT + F10, move by UP/DOWN arrows when JAWS says, Hide Field Uncheck hit ENTER.
5. Press Shift + F10, then move by arrow when JAWS says, Show Fields hit enter.
6. Press TAB, at the list of field names move by DOWN arrow, at First name right click (Shift F10).
7. Move by DOWN/UP arrow; add First Name to row label, (hit ENTER.)
8. Press DOWN arrow at Total Wage, right click.
9. Move by arrows and add Total Wage to value. Name the Worksheet as "Employees Pie Sheet1".
10. You have two fields only. Type First Name at Cell A3.
11. Press Shift + F6; focus lands on cell A3 at the pivot table!
12. Hit the ALT key; at the INSERT TAB, press DOWN arrow, when JAWS says, Charts Submenu , press ENTER, again press ENTER At the RECOMMENDED CHARTS BUTTON.
13. hit TAB; at the Charts Name list box press DOWN arrow; at the Pie option hit ENTER.
14. Now, you have a very nice looking Pie. Press Insert + CTRL + ALT + C, and JAWS will describe the Pie chart for you. You can move now by UP/DOWN arrows to read the Pie info as many as you like! NOTE: INSERT + CTRL + ALT is JAWS read the chart for me command.

Even though, it is not that hard to create a timesheet, there are many things to consider in the design of it. Does the timesheet includes exempted employees, overtime eligible employees, paid leave etc.? Before you design the timesheet, learn about the company policy, and figure out what are the requirements for the Layout of the timesheet.

The timesheet for the GZM America Inc should be simple straight forward. It must avoids problems with employees and the government. The payroll timesheet must calculate not only accurately, but it also must comply with all federal and state laws. For example, the timesheet needs automatically and correctly to calculate overtime for all hours worked over 40; and it should add the hours an employee's overtime pay using Excel timesheet. Beside of the company name, employee name, manager name and form completed date as title headers, it should include: Date, Day (Type Date; Size Default), Employee Worked: Y/N (Type Text; Size Default), Login (Type Time; Size Default), Logout (Type Time; Size Default), Login (Type Time; Size Default), Logout (Type Time; Size Default), Overtime Worked HRS: Type Number; Size Default), Tot. Worked Hours (Type Number; Size Default), Job Duties (Type Text; Size 40 Char) variables or fields.

The timesheet must calculate weekly totals, and it should summarize results in the proper column under the TOTAL HOURS section. The time worked in excess of 40 hours in a week as shown in the Total Hours Worked column must be tracked as overtime pay, and must be record in the Overtime (O/T Hrs) column.

According to the company's, the employee or immediate supervisor of the employee must complete the timesheet. At the end, supervisor must verify and confirm that the information recorded is complete and accurate. Follow the below instructions what and how the GZM America Inc. timesheet should be created!

The Layout of the Timesheet Labels:

1. Call Microsoft Excel and verify focus is on cell A1.
2. Merge row one column A to J. (Press CTRL + 1; ALIGNMENT TAB 'CHECK the Merge Checkbox' and hit enter).
3. Focus is on the merged cells. And type in it, as: "Timesheet: GZM America Inc; 1020 Aurora Ave. N; Shoreline, WA." and hit ENTER. (These merged cells should be protected. As I mentioned previously, the default value is locked; thus, you may leave it as it is!)
4. Make sure focus is on Cell A2. Select Cell A2 to B2 and merge them. Label it or type "Employee Full Name:" and hit TAB. Focus should land on Cell C2. (Merged fields A2 and B2 must be protected).
5. Merge Cell C2 to D2, (This where an employee puts his/her full name.) and stay there (On Cell C2:D2).
1. Press ALT H to trigger the virtual ribbon HOME TAB, move by Down arrow when JAWS says, Cell Submenu hit ENTER, at FORMAT BUTTON hit ENTER again.
2. Press UP arrow until JAWS says, "Lock Cells Check" hit ENTER to uncheck the "LOCK CELLS" then hit ESCAPE repeatedly until focus lands on the worksheet. Now you have allowed users to edit Cell C2/D2; thus, a person can enter his/her name!
6. Make sure focus is on Cell E2, and merge Cell E2 to J2. type in it, "The timesheet must be filled out correctly and must be submitted on timely fashion to GZM America Inc to be paid on time." And hit ENTER. (Merged cells E2 to J2 should be protected.)
7. Merge Cells A3 and B3. type in it, "Name of Manager: " and hit TAB. Focus lands on C3.
8. Merge C3 and D3, (These merged cell addresses are for manager name, and these are editable cells.) Therefore, uncheck the "Lock Cells".
9. An able user to edit cell E3 and F3. They may use them to write their manager's contacts.
10. Merge Cell A4 and B4, and type in it, 'Work Week Ended:' then hit TAB. Focus lands on Cell C4. (Merged cells a4 and b4 must be protected.)
11. Merge cells C4 and D4 ar editable cell; thus, uncheck the "Lock Cells"(Trigger the virtual ribbon, Home TAB, Cells Submenu, Format, Uncheck the Lock Cells.)
12. Merge Cell A5 and B5, and type in it, 'Date Timesheet Completed:' then hit TAB. Focus lands on Cell C5. (Cells A5 and B5 must be protected.)
13. Merge Cells C5 and D5. Uncheck the "Lock Cells" for editing purposes. Format this Cells Address as Date Type and follow the instructions below:
1. Press CTRL + 1. At the Number TAB: Genral listing, Date (Take the detailed date format= 'Monday March 02, 2017.') and hit ENTER.
14. Merge Cell E5 to J5, hit Enter.
15. Merge Cells A6 to B7, assign and type in it, 'Date' field. This field should be defined as DATE type, DEFAULT size, and must be protected.
16. Merge Cells A8 and B8; A9 and B9; A10 and B10; A11 and B11; A12 and B12; A13 and B13; A14 and B14. These field must be editable to the user (Select Cell A8 to B14, ALT H, Cells Submenu, Format, UNCHECK LOCK CELLS).
17. Type in Cell C6 "Employee Worked? (Y/N)". This field is TEXT type and DEFAULT size, and must be protected. It shows whether employee worked or not on the given date. be Cells C8 to C14 must be editable to the user (Select Cell C8 to C14, ALT H, Cells Submenu, Format, UNCHECK LOCK CELLS).
18. Type in merged cells A15, and B15, 'Total Weekly Worked Days', and this merged address must be protected.
19. In cell C15 type '==COUNTIF(C8:C14,"Y")' to automatically count worked days for the week. This cell should be protected.
20. Assign and Label for D6 as 'Login', e6 as 'Logout', F6 as 'Login', G6 as 'Logout', and these fields must contain TIME type data, and DEFAULT size, and must be protected. Cells D8 to G14 must be editable to the user (Select Cell D8 to I14, ALT H, Cells Submenu, Format, UNCHECK LOCK CELLS).
21. Type in cell H6 as 'Total HRS Worked', and in cell I6 as 'O/T HRS'. (Cells h6. Cell H6 and I6 should contain Number type of data and Default field size. Cells H6 and I6 should not be editable cells; thus, check the "Lock Cells"(Trigger the virtual ribbon, Home TAB, Cells Submenu, Format, check the Lock Cells Button.)
22. Label field J6 as: 'Job Duties'. Cell J6 should contain TEXT type of data, and 20 Width in size, and it should be protected. Cells J8 to J14 must be editable to the user (Select Cell J8 to J14, ALT H, Cells Submenu, Format, UNCHECK LOCK CELLS).
23. Merge Cell C7 to J7 (Just for making the Timesheet look good).
24. Type or Copy and Paste this formula {=((E8-D8)*24)+((G8-F8)*24)} to cell H8 to calculate automatically worked HRS for the day, and copy the formula from h8 to h9/h14. Daily total worked hours will be calculated for each employee. Cell H8 to H15 should be protected.
25. Now do not allow users to edit this formula (Select Cell H8 to H14, ALT H, Cells Submenu, Format, CHECK LOCK CELLS). If cell references in the formula don't give you the result you want, double check that you have correct reference type for the time type cells, if not try different reference types. cells H8 to H 15 must be NUMBER type.
26. Type or Copy and Paste this formula {=IF(H8>8, H8-8, 0)} to cell I8. This will calculate automatically O/T HRS for the day in cell I8, and copy the formula from I8 to I9/I14. Daily total o/T HRS will be calculated for each employee.
27. Now do not allow users to edit this formula (Select Cell I8 to I14, ALT H, Cells Submenu, Format, CHECK LOCK CELLS). If cell references in the formula don't give you the result you want, double check that you have correct reference type for time type cells, if not try different reference types. cells I8 to I 15 must be NUMBER type.
28. Type or put {=SUM(H8:H14)} formula in cell H15, copy and paste it to cell I15. Weekly worked hours on cell H15, and weekly overtime worked hours on cel I15 will automatically be calculated. Cell H15 and Cell I15 must be protected from editing by user (Select Cell H16 and I16, ALT H, Cells Submenu, Format, CHECK LOCK CELLS).
29. Merge cel F15 and G15. Type in the merged cells, 'Total Weekly HRS'. It should be protected.
1. Select Cells Submenu, then Format Button from the Home TAB of the virtual Ribbon.
2. Move by UP arrow and hit ENTER on Protect Sheet dialog box.
3. Enter 'GZM America' (As they are written.) on the 'Enter your new password' dialogbox. Confirm the password, finally save your work (CTRL ++ S)

Click here to download the GZM America Inc. Timesheet now!

The GZM America Inc. forecasts its sales of Eye Glasses once a year to facilitate business decision-making process. Sales forecasting help the GZM America to understand changes and accordingly formulate company strategies. Because of forecasting alone cannot assure the GZM America long-term sustainability and profitability of its business, the GZM America keenly observe the entire business environment to foresee threats arising not only from competitors but also from changes in the economic conditions of the nation.

The GZM America sales FORECAST can be described as a process of predicting sales trend in the company's sale as a whole or in part. Sales forecasting has many advantages. The GZM America Sales forecast helps decision makers, employees, creditors, investors even to the government in estimating how well is the company doing. It assists the employer in taking the steps required to achieve the objective what the company should do.

Sales Forecast helps not only the GZM America, but to any Business to formulate its' strategies based on the sales trend. For instance, if the sales trend indicates that the sales is growing at a fast pace, then it encourages employers to expand their business to gain market-share to improve their profits. Sales forecasting is also essential for individuals as it helps them in making prudent investment decisions.

The GZM America forecasts the business environment in which it is operating and accordingly formulates strategies. For instance, the GZM America is required to estimate the demand for Eye Glasses and accordingly maintain stocks, and makes other necessary adjustments to insure that the business is running smoothly.

1. Run Microsoft Excel, or switch to MS Excell.
2. Merge cells a1 to b2, and type 'The GZM America Inc.', then press ALT + ENTER (NOTE: ALT + ENTER puts focus in the next line within the cell). Again type 'Sales Forecast', Press ALT + ENTER. Now type Today's date, and hit ENTER. See example below:

3. Go to cell A4, and type 'Year' as row label. See example below:

4. Type 񟭐' in cell A5, and hit Enter (Enter key takes you to the next cell down).

5. Fill cells A6 to A14 while typing 񟭑, 2018 ... to 2025 (These are row headers for 10 years Sales Forecast)'.

6. Type in cells B4 to M4: 'Jan, Feb, ... Dec as column headers.'.

7. Type in cell N4 'Total', this field will hold total sale for each year.
8. Type in cell O4 'Min', (This field will hold minimum sale for the year.)
9. Type in cell P4 'Average', (This field will hold average sale for the year.)
10. Type in cell Q4 'Max', (This field will hold maximum sale for the year.)
11. Type in cell R4 'Range', (This field will hold differences between the maximum sale and minimum sale for the year: 'Max value - Min value = Range'.)

1. Now, go to cell b5, and type '+100' (That is $100,000 the given amount from historical data for Jan. 2016 sale.) Then hit TAB, and focus will land on cell C5.
2. Type in cell C5 '+150' (This is the given amount for February 2016 sale.) Hit TAB, focus lands on cell D5.
3. Type in cell D5 '+200', and hit TAB. (This is also the given amount for March 2016 sale of GZM America Inc.)

Have you noticed, how formulas work! In Excel formula has to start with an = (Equals sign). Functions and formulas are the most significant elements of Microsoft Excel. There are many ways you can set them up and start doing serious math. Excel has deep and sophisticated formula features. there are multiple choices to complete tasks, and you can use your liking method works best for you. Excel provides several ways to enter formulas and calculate numbers. For example, you can manually enter Excel formulas: =average( B5:d5) as it used above. Also you can achieve the same result by entering =sum(b5:d5)/3, or =sum(b5+c5+d5)/3, or =(b5+c5+d5)/3 at the right cell address. In addition, you can use formulas and functions from the virtual ribbon formula TAB. Click the Insert Function button under the Formulas tab to select a function from Excel's menu list. Even you can narrow your search a bit and choose a formula subset for Financial, Logical, or Date/Time for other purposes too.

12. Copy the formula in cell G5 (Go to G5, and press CTRL + c to copy it).
13. Select H5 to M5 (Go to H5, and hold down the SHIFT key while pressing the RIGHT arrow key until JAWS says, "M5").
14. Press CTRL + V to paste it. So far, cell B5, C5, and D5 have numbers from GZM America historical database. Cells E5 to M5 have contained calculated result for the sales forecast.
1. Enter &'=sum(b5:m5)' in cell N5 to get total forecasted sale for the year.
2. Enter '=min(b5:m5)' in cell O5 to get the minimum value for the year.
3. Enter '=average(b5:m5) in cell P5 to get average value for the year.
4. Enter '=max(b5:m5) in cell Q5 to get maximum value for the year.
5. Enter '=q5-o5' range in cell R5 to get the range means differences between the smallest and largest value in the year. You obtained the range by subtracting cell O5 (The minimum value for the year) from cell Q5 (The maximum value for the year).

15. Go to cell b6, type '=average(k5:m5)',, and hit tab focus lands on cell C6.
16. Enter '=sum(l5+m5+b6)/3' in cell C6, and hit TAB, focus lands on d6. Pay attention here, when you move from row 5 to row 6, how did you write the formulas in cell B6 and C6, why is it?
17. Enter '=sum(m5+b6+c6)/3' in D6, then hit TAB, focus lands on cell e6. Did you notice the completion of moving from row 5 to row 6 at cell D6? You jumped from row 5 column M to row 6 column E with great care without refering to other cell addresses at row 5 and row 6 column A.
18. Enter '=average(b6:d6) in cell E6, hit TAB and focus lands on F6.
19. Copy cell E6 and paste it to f6/m6.
1. Go to cell N5, and select cell N5 up to R5 and copy it. (Hold down the SHIFT key while pressing RIGHT arrow when JAWS says, "R5", press CTRL + C to copy it.
2. go to cell N6, and paste it (Press CTRL + V to paste it).
20. Go to cell b6 (Hit F5, write b6 on the reference dialog box, and hit ENTER). Select cell b6 up to cell r6, and copy it (Hold down the SHIFT key while pressing RIGHT arrow when JAWS says, "R6", release the SHIFT key and press CTRL + C to copy).
21. Go to cell b7, and select up to R14. (Press JAWS key + SHIFT + DOWN arrow, the read highlighted region keys to verify you have the right region selected. JAWS will say, "B7 to cell R14), then pres CTRL + V to paste. The worksheet will be filled with data.
22. Go to A4, press SHIFT + CTRL + SPACEBAR to select the region. Then press CTRL + T. Save your work now as GZM sales forecast table.

Woo, woo, you have a beautiful range of data table! Click here to download the GZM America Sales Forecast Table.

Charts or Graphics for Data Presentation:

Data presentation is a very important skill for data analyst person to convey messages. Experts say, a picture is worth a thousand words, to tell us that as Humans we don't do very well with large amounts of text and reading is time consuming and can easily overwhelm us. The presentation of qualitative and quantitative data in graphical format, gives advantages of to easily make sense especially if it is big data. Graphic presentation of data help to understand its relationship, identify its distribution, determine, and predict its future trends and tell meaningful and engaging stories to decision makers. Excel charts are used for data visualization and presentation. However, picking up the right excel chart is a big challenge. Using incorrect Excel chart for analysis, you may misinterpret data and make the wrong business decisions. If you use wrong Excel chart for presentation, stakeholders may misinterpret charts; thus, they may take wrong decisions. Selecting the right Excel chart for presentation requires real knowledge of excel charts or graphs, and how to select the right chart to the right purpose.

1. Line Chart or Line Graph:
2. A Pie Chart:
3. A Bar Graph:
4. Should You Use a Pie Chart or a Bar Chart?
5. Area Graph:
6. Charting the GZM America Sales Forecast for presentation:

Line Chart or Line Graph:

A line graph, also known as a line chart, is a type of chart used to visualize the value of something over time. For example, a finance department may plot the change in the amount of cash the company has on hand over time. The line graph consists of a horizontal x-axis and a vertical y-axis. Most line graphs only deal with positive number values, so these axes typically intersect near the bottom of the y-axis and the left end of the x-axis. The point at which the axes intersect is always (0, 0). Each axis is labeled with a data type. For example, the x-axis could be days, weeks, quarters, or years, while the y-axis shows revenue in dollars. Data points are plotted and connected by a line in a "dot-to-dot" fashion.

The x-axis is also called the independent axis because its values do not depend on anything. For example, time is always placed on the x-axis since it continues to move forward regardless of anything else. The y-axis is also called the dependent axis because its values depend on those of the x-axis: at this time, the company had this much money. The result is that the line of the graph always progresses in a horizontal fashion and each x value only has one y value (the company cannot have two amounts of money at the same time). More than one line may be plotted in the same axis as a form of comparison. For example, you could create a line graph comparing the amount of money held by each branch office with a separate line for each office. In this case each line would have a different color, identified in a legend.

The line graph is a powerful visual tool for marketing, finance, and other areas. It is also useful in laboratory research, weather monitoring, or any other function involving a correlation between two numerical values. If two or more lines are on the chart, it can be used as a comparison between them.

How to Create a Line Graph

Create a table. Draw the x- and y-axes on the page. On the top of the page, place a title that briefly describes the purpose of the chart.

Label each axis. If time is one of the factors, it should go along the horizontal (x) axis. The other numeric values measured should be placed along the vertical (y) axis. Each axis should be labeled with the name of the numeric system as well as the measurements being used. For example, you may label the x axis with "Time (Days)", indicating that each number written on the axis is a number of days. Divide each axis evenly into applicable increments.

Add data. Data for a line graph is usually contained in a two-column table corresponding to the x- and y-axes. With Excel you can also import /export data. Once you've added your data, your line graph will automatically reflect its values. If you want to call attention to any particular value, you can add a label with an arrow pointing to it.

Create a key. If you are comparing multiple items, you'll want to create a key that identifies what each line is by its color.

A Bar Graph

A bar chart or bar graph is a chart or graph that presents grouped data with rectangle|rectangular bars with lengths proportional to the values that they represent. The bars can be plotted vertically or horizontally. A vertical bar chart is sometimes called a Line graph. A bar graph (also known as a bar chart) is a diagram that uses proportional-width bars to compare data among categories. A bar graph may run horizontally or vertically. The important thing to know is that the longer the bar, the greater its value. Bar graphs display data in a way that is similar to line graphs. Line graphs are useful for displaying smaller changes in a trend over time. Bar graphs are better for comparing larger changes or differences in data among groups.

Bar graphs have three key attributes.

1. It's easy to compare sets of data between different groups at a glance.
2. The relationship of the data between the x and y axes is easy to see.
3. They are effective in presenting trends or changes over time.

Credit Simple Bar Chart

Bar graphs consist of two axes. On a vertical bar graph, as described above, the horizontal axis (or x-axis) shows the data categories. In the x-axis, they are years. The vertical axis (or y-axis) is the scale. The colored bars are the data series.

When to Use a Bar Graph

Bar graphs are an effective way to compare items between different groups. This bar graph shows a comparison of numbers on a quarterly basis over a four-year period of time. Users of Bar chart can compare the data by quarter on a year-over-year trend, and also see how the annual sales are distributed throughout each year. Bar graphs are an extremely effective visual to use in presentations and reports. They are popular because they allow the reader to recognize patterns or trends far more easily than looking at a table of numerical data.

Types of Bar Graphs

When presenting data visually, there are several different styles of bar graphs to consider.

Vertical Bar Graph

The most common type of bar graph is the vertical bar graph. It is very useful when presenting a series of data over time. The vertical bar chart below shows a series of quarterly data, categorized by year. The reader can easily see not only the trends of sales over the four-year period, but also how the sales compare during each quarter.

Data Comparison Bar Chart

One disadvantage of vertical bar graphs is that they don't leave much room at the bottom of the chart if long labels are required.

Horizontal Bar Graph

Converting the vertical data to a horizontal bar chart solves this problem. There is plenty of room for the long label along the vertical axis, as shown below.

Stacked Bar Graph

The stacked bar graph is a visual that can convey a lot of information. One of the disadvantages of a stacked bar chart is that may not show data in as clear a manner as intended, and if not read carefully, might even be misleading. For example, if a chart shows the population of Africa increased more than 100% during this time period while the population of Non-OECD Asia increased by about 50%. Perhaps lost in a chart is that the population of the Middle East also grows by more than 100%. The point is to make sure that you select the type of graph that best presents the data you want to emphasize. Create charts and graphs without having to make a spreadsheet first. Simply pick a chart and type your data directly into the bars. You can even drag the bars to represent the right values.

A Pie Chart:

A pie chart is a circular chart divided into wedge-like sectors, illustrating proportion. Each wedge represents a proportionate part of the whole, and the total value of the pie is always 100 percent. Pie charts can make the size of portions easy to understand at a glance. They're widely used in business presentations and education to show the proportions among a large variety of categories including expenses, segments of a population, or answers to a survey.

How Does a Donut Chart Compare to a Pie Chart

Donut charts are similar to pie charts in that their aim is to illustrate proportions. However, they contain a blank center that can contain a description or another layer of information.

Ring Chart

Another variation of a pie chart is known as a ring chart. A ring chart, also known as a sunburst chart, is a multi-layered donut chart with many rings to represent hierarchies of data in concentric circles.

How to Create a Pie Chart

Start with what you want to illustrate. The main topic will be the title of your chart.

Gather the data. Pie charts depict percentages. Make sure your percentages add up to 100% and get rid of any rounding issues that would make the total something other than 100%. SmartDraw allows you to write the percentage directly into a pie chart template or you can drag the edge of a wedge to make it bigger or smaller.

Consider creating a legend. If a wedge is too small to hold text, you can create a label and draw a line identifying the appropriate slice. Alternatively, you can create a key that shows what each color represents.

Area Graph

Area Graphs are Line Graphs but with the area below the line filled in with a certain colour or texture. Area Graphs are drawn by first plotting data points on a Cartesian coordinate grid, joining a line between the points and finally filling in the space below the completed line. Like Line Graphs, Area Graphs are used to display the development of quantitative values over an interval or time period. They are most commonly used to show trends, rather then convey specific values.

Two popular variations of Area Graphs are: grouped and Stacked Area Graphs. Grouped Area Graphs start from the same zero axis, while Stacked Area Graphs have each data series start from the point left by the previous data series.

Mr. Tutu the owner of the GZM America Inc president, wants you simply to inform the government, creditores, shareholders and to the general public using the proper charting technique for the companies sales forcast. The purpose is just to show how well the company is doing in the past, now and in future years. Even though there are many techniques to present data, it would be apposite to use line graph or pivot table as a proper charting technique for showing trend over time for the GZM America sales forecast. Thus, I will take you through series instructions how to do line charts and pivote tables, thus follow the below steps.

1. Download the GZM America sales forecast data now. You will be prompt with a message if you would like to 'OPEN', or 'SAVE' the file, and take the 'SAVE' option. It will be downloaded to your default folder.
2. The file name is 'gzm america sales forecast table.xlsx' and call it in Microsoft Excell.
1. Right click, and go to property.
2. Go to General Tab, and check the UNBLOCK BUTTON, then click OK. The file will be opened in unprotective
view, so you can edit it.
OR
1. Click File, Help, Options.
2. Click Trust Center.
3. Click Trust Center Settings.
4. Click Protected View.
5. Make selections that you want from the list of options:

How to Design Better Sorce Data Structure

1. The data should be organized in rows and columns, with each row containing information about one record.
2. Avoiding blank cells in column A helps with the setup of dynamic named ranges and avoiding blank cells in the data fields helps the Pivot Table to select the correct summary function. Hence, avoid blank cells in Column A.
3. The first row of the list should contain unique field name or column heading.
4. A field or column in the list should contain one type of data, such as time, dates, number, currency, or text.
5. A record or row should contain details for one record, such as a sales amount or a sales order (Depend the data purpose). If possible, include a unique identifier as a primary key for each row, such as a record number. <;/li>
6. The data range should have no blank rows and columns.
7. Remove any totals or subtotals in the data rows, the pivot table will do all the summary.
8. The data region should be separated from any other data region on the worksheet at least by one blank row and one blank column.

1. Put the months' name in column A as row headers.
2. Put years 2016, 2017 ... as a colymn or field headers and save your change.
• Click here, to download your source data file and see the change you have made to it.

1. While your in the Excel Pivot Sheet move to cell A3 which is the cell address where the column heading in row 3 and the row heading column A intersect. While the focus is in cel A3, press INSERT + ALT + CONTROL + C to set the column read titles to the current row A3.
2. Move to cell A4 which is the cell address where the row heading in cel A4 starts. While the focus is in cel A4, press INSERT + ALT + CONTROL + R to set the row read titles to the current column A.
• If the above instructions does't work for you, click here to see detailed instructions how to set row / column read in excel.
• Click to download the file and see the sales forecast in pivot table.

Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 1 (From Youtube). Uploaded by Excel Campus - Jon.
Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 2 (From Youtube). Uploaded by Excel Campus - Jon.
Introduction to Pivot Tables, Charts, and Dashboards in Excel (Part 3 (From Youtube). Uploaded by Excel Campus - Jon.

A pivot chart can be added After creating a pivot table. I will show you how to create a pivot chart, and that pivot chart can be connected to a different pivot table. This is a useful technique if you have setted up a pivot chart with specific formatting, and you can use a copy of that chart with a different pivot table.

• How to Create a Pivot Chart
• How to Create a Pivot Chart Before Pivot Table
• How to Change Pivot Chart Source Data

How to Create a Pivot Chart

To quickly create a pivot chart, you can use one of the following shortcuts.

1. Load up Microsoft Excel, and make sure you work with the GZM Sales forecast new file.
2. Select any cell in the pivot table, and hit F11, to create a chart on a new chart sheet
3. OR, press Alt + F1, to embed chart on the active sheet, the pivot chart will be in the default chart type. It can be changed to a different chart type, and adjust the chart's formatting.
• Click to download the file and see the sales forecast for GZM America in pivot line graph.
• This short video tutorial shows how to quickly create a pivot chart using keyboard shortcuts. By Contextures Inc.

How to Create a Pivot Chart Before Pivot Table

If you are using Excel 2013 or above, a pivot chart can be created directly from the source data, without creating a pivot table first.

1. Locate focus in any cell in the source data table
2. Trigger the virtual ribbon, click the Insert tab, and click Recommended Charts
3. Click on one of the charts samples that has a pivot table symbol at the top right, and the pivot chart will be inserted on a new worksheet, and its connected pivot table will be created on the same sheet.

How to Change the Pivot Chart

The source data cann not be changed in a pivot chart. To see the data source for the selected chart:

1. On the Excel Ribbon, click the Design tab, under PivotChart Tools, click Select Data, to open the Select Data Source window (You can see the address of the chart's data range, but you can't make any changes to the range).
Unlink the Pivot Chart: Although you can't directly change the pivot chart source, you can follow a few easy steps, to make the change. We'll make a copy of the original chart, and then unlink it from the pivot table, to create a static chart:
1. Right-click the pivot chart's, Chart area or border, and in the popup menu, click Copy.
2. On the Ribbon, click the File tab, and click New, then create a new blank workbook.
3. On the Ribbon, click the Home tab, and click Paste.
1. How to link the chart to a different pivot table.
2. Right-click the pivot chart's Chart area or border, and then in the popup menu, click Cut.
3. Switch to the workbook with the pivot table that you want to use as the chart's data source.
4. Paste the chart back into the workbook, and delete the old copy of the chart.
5. With the new chart selected, on the Excel Ribbon, click the Design tab, under Chart Tools.
6. Click Select Data, to open the Select Data Source window.
7. Click any cell in the pivot table, and click OK.
• This video shows how to unlink and change data source.
The static chart changes back to a pivot chart, and if you open the Select Data Source window again, you'll see the reference to the new pivot table.

How to Label Axes in Excel

When you create a chart in Microsoft Excel, labeling the axes will make it much easier for your readers to understand your data. Labeling the axes in any version of Excel only takes a few minutes, and the benefits for the reader are tremendous.

1. Method 1. Label Axes in Excel 2013
1. Open the GZM America file that contains the chart in which you want axes labeled.
2. Click on the location in the chart at which you want to label the axes.
3. Click on the "Charts Elements" button located near the upper-right corner of your Excel chart. The button contains the image of a plus sign.
4. Place a checkmark next to "Axis Titles." Horizontal and vertical axis text boxes will then be added to your chart. If you want to label your chart's secondary axes, click on the arrow to the right of "Axis Titles," then select "More Options" to designate the axes you want labeled.
5. Click directly on each of the axis text boxes within the chart, and enter the labels you want for each particular axis.
2. Method 2. Label Axes in Excel 2007
1. Open the GZM America file that contains the chart in which you want to label axes.
2. Click on any location in the chart at which you want to label axes. The "Chart Tools" menu will display.

The GZM America In. Income Statement

The income statement is known as the profit and loss statement, statement of operations, or statement of income, it is one of the major financial statements used by the GZM America Inc. The balance sheet, stockholders' equity, and the statement of cash flows are also the major financial statements for the GZM America; however, it will present just the income statement to show revenues, expenses, gains, and losses. The government, creditors, investors, company management, competitors, agencies, labor unions, and others pay attention to the profitability of the GZM America for the reasons of its ability to use borrowed and invested funds in a productive manner. Investors, lenders, and creditors may be hesitant to extend additional credit to the GZM America, If the bottom line of the income statement shows a net loss; on the other hand, if the bottom line of the GZM America income statement shows a net profit, it shows its ability to use borrowed and invested funds in a successful manner. Of course, yes, a company's ability to operate profitably is important to current potential lenders and investors. The general principle is, if the net amount of revenues and gains minus expenses and losses is positive, the bottom line of the profit and loss statement is labeled as net income. If the net amount (or bottom line) is negative, there is a net loss. The GZM America income statement shows its' profitability during the time interval specified in its heading. The format of the GZM America income statement will have the following elements in its income statements:

1. Revenues and Gains
1. Revenues from primary activities
2. Gains (e.g., gain on the sale of long-term assets, gain on lawsuits)
Expenses and Losses
1. Expenses involved in primary activities

What-if Analysis

Excel includes number of powerful tools to perform mathematical calculations, including what-if analysis. This feature helps to experiment and answer questions with data, even when the data is incomplete. In this lesson, you will learn how to use a what-if analysis tool called Scenarios, Data Tables, and Goal seek. Usually, experts use Scenarios and Data tables type of if-analysis for more advanced projects. The goal seek type of if-analysis assists to start from the desired result and works backward. The scenario and data table type of if-analysis allow to test multiple values and see how the results change.

Goal Seek

In creating a formula or function in Excel, we put various parts together to calculate a result. Goal Seek works in the opposite way: The Goal seek lets you start with the desired result, and it calculates the input value that will give that result. I will use a few examples to show how to use Goal Seek.

In the months of January, February, March, April, and May the GZM Inc has earned $57,000,000, $60,000,000, $70,000,000, $70,000,000, and $73,000,000 an average of $66,000,000; however, by the end of the month of June the GZM Inc must earn an average of $70,000,000 to cover all kind of expenses the company has. Therefore, how much does the GZM Inc Should earn on the month of June of this year to raise up to the needed average. we can use Goal Seek to find out how much money should be earned on the month of June to achieve the needed result. Hence, the decision maker can apply the right marketing technique to increase sales performance.

The above problem indicates that we don't know what the month of June revenue will be; however, we can write a formula or function that calculates the revenue for July. In this case, the revenue for each month is weighted equally, so all we have to do is average all six months' revenue by typing =AVERAGE(B3:B8) on cell B9. Once we use Goal Seek, cell B8 will show us the minimum revenue the GZM Inc will need to make on the month of June. Follow the next steps to complete the task!

1. Go to Excel and type "+57, + 60, +70, +70, +73, +0" and "=average(b3:b8)" on cell b3, b4, b5, b6, b7, b8 and b9.
2. Select the cell whose value you want to change. Whenever you use Goal Seek, you'll need to select a cell that already contains a formula or function. In this example, you will select cell B9 because it contains the formula =AVERAGE(B3:B8).
3. Go to the Data tab, click the Forcast, What-months' command, then select Goal Seek from the drop-down menu.
4. A dialog box will appear with three fields: Set cell: This is the cell that will contain the desired result. In this example, type 'B9' and pres Tab.
5. To value: This is the desired result. Type ྂ' then tab. The GZM Inc needs at least that to earn $70 million to cover the company s expenses.
6. By changing cell: This is the cell where Goal Seek will place its answer. In this example, you will type 'B8' because you want to determine the revenue the GZM Inc needs to earn on the month of June.
7. Finally click OK.

Scenario

Data Table