Note: There is a back button > located at the bottom of this tutorial to return to the Accessibility Zone.
Note: There are 2 MS Excel tables linked towards the bottom of this tutorial that you can download to your own computer.
One is a functional Excel budget template, and the other is the completed "Weekly Food Expenses" table used in the tutorial learning exercises below.
This practical tutorial provides step by step instructions to create your own Excel table and, using the above same page links, will also act as a reference to many main functions of Excel. For example, if you only want to learn how to merge cells to create a centred title, simply follow the link titled “merge cells”.
Let’s get started.
Using the keyboard, type data into the active cell and then press either Enter or TAB.
To correct or change data in a cell press F2 within the active cell. The active cell will now be in edit mode where we can change the text. Pressing Enter or Tab will stop edit mode.
By following the steps below, we will learn how to set up a table, input data, create formulas to automatically calculate totals and cost percentages. We will also cover some essential steps to format the table for display purposes.
Move the active cell to A3 and type “Weekly Food Expenses”. Pressing Enter at this point will move the active cell directly beneath A3 to A4. Pressing TAB instead will move the active cell to the right, (B3 in this case).
Weekly Food Expenses will be the title of this worksheet.
We will now create our column titles to include days of the week.
Move to cell B4 and type MON.
Press Tab. The active cell is now C4.
In cell C4 type TUE and then press Tab.
In cell D4 type WED and then press Tab.
In cell E4, typeTHU and then press TAB.
In cell F4 type, FRI and then press Tab.
In cell G4 type WEEK and then press Enter. Now pres Home which will move the cursor all the way over to the left column A.
Now we will enter our row titles to include daily meals.
In cell A5 type Breakfast and then press Enter.
In cell A6 type Lunch and then press Enter.
In cell A7 type Supper and then press Enter.
In cell A8 type Total and then press Enter.
In cell A9 type Percentage and then press Enter.
Now the table has been set.
Let’s input the dolloar amounts for each meal.
Note: Do not use the dolloar symbol. Simply type the number. We will learn how to represent cell data as dollar amounts below.
In cell B5 type 3 and then press TAB.
In cell C5 type 3 and then press TAB.
In cell D5 type 7 and then press TAB.
In cell E5 type 5 and then press TAB.
In cell F5 type 4 and then press Tab.
In cell G5 type 8 and then press Tab.
In cell H5 type 0 and then press TAB. The numbers we enter in an Excel table are referred to as values.
We have now entered the breakfast cost values for the week.
At this point go ahead and enter the numbers to represent the cost amount for lunch in cells B6 to H6.
Now enter numbers to represent the costs for supper in cells B7 to H7.
Excellent! Now that wee have our value amounts in place, let’s learn how to use Excel to calculate the totals.
Before we move into the learning exercise, here are some commonly used formula symbols:
A couple points about the formula: The word SUM indicates that a tabulation will be performed. A colon in between B5 and H5 indicates that all cells in between will be added. The cell range is contained between parenthesis. These are the open bracket and close bracket located by using Shift 9, and Shift 0.
Now move to cell I6.
This is the end of the row for lunch totals. It’s column title is Week.
We will use another formula to calculate the weekly llunch total.
Type the following formula:
=SUM(B6:H6) and then press Enter.
Now, thanks to the formula, the weekly lunch total is reflected in cell I6.
We will enter a formula in cell I7 to calculate the weekly supper total.
We could enter the following: =SUM(B7:H7)
OR we can simply copy the contents of cell I6 and paste it into cell I7. MS Excel is able to determine that if you copy a formula and paste it in the cell below, the cell coordinates will need to be slightly different, and it will make the change for you.
Now the weekly supper total is reflected in cell I7.
Cell I8 should indicate the total combined weekly cost of breakfast, lunch and supper. In other words, the grand weekly food expense total.
So type the following formula in cell I8:
=SUM(I5:I7)
Now we will create formulas along the 8th row to reflect the daily totals.
Now that we have all the totals, we can use them to calculate cost percentages for each day of the week.
To verify that our row of percentages adds up to 100%, move to cell I9 and type the following formula:
Now that all of our daily totals are reflected as percentages along the 9th row, let’s learn how to have them displayed properly as percentages.
Here's how you do it:
If you are new to Excel, and not yet comfortable with all the formatting options available, you can use the AutoFormat option to give your spreadsheets
a professional look and make them easy to read.
To auto format an Excel table:
To calculate the total expense for Monday's breakfast, lunch and supper:
Move to cell B8 and type the following formula:
=SUM(B5:B7) and then press Tab.
Press left arrow once to review that in fact the total for Monday is now reflected in cell B8.
Using Excel formulas to calculate percentages.
A percentage is obtained by dividing a portion of the grand total, by the grand total and then, of course, multiplying this figure by 100 OR simply moving the decimal point right 2 places.
We can use an Excel formula to automatically calculate percentages.
Learning exercise
Using our Weekly Food Expenses example, we know that our grand total is located in cell I8.
We know that our dayly totals are listed along The 8th row from cell B8 to cell H8
So if we move to cell A9 we are now in the row titled “Percentages”.
Move right one cell to B9. We are now in the Monday column.
Now type the following formula:
=SUM(B8/I8) and then press TAB.
This formula will calculate the cost percentage for Monday's meals based on the weekly total expense.
The slash is used in this formula as a division sign.
We will learn how to format this row to display as percentages later.
Let's continue along the 9th row and enter formulas to calculate the cost percentages for the rest of the days of the week.
so, in cell C9 type the folloing formula:
=SUM(C8/i8)
Now in cell E9 type the following formula:
=SUM(E8/I8)
Now in cell F9 type the following formula:
=SUM(F8/I8)
Now in cell G9 type the following formula:
=SUM(G8/I8)
Now in cell H9 type the following formula:
=SUM(iH/I8)
=SUM(B9:H9)
Formatting cells to Display as Percentages.
To display a range of cells as percentages with the percentage sign to the right of the numeric value:
Now the range of cells will be displayed as percentages.
The focus will be on a list titled categories.
Formatting cells to display as currency.
To display a range of cells as currency with a dollar sign to the left of the numeric value:
Now the numbers in these cells will be displayed as dollar figures.
The focus will be on a list titled categories.
Merging cells to create centred titles.
Often times we wwill want to have a specific text title displayed across a range of columns. To do this we must merge cells.
In essence merging cells removes grid lines and allows several cells to combine as one cell.
The merge cells option is found within the Format Cells, align menu.
By selecting a range of cells and then merging them, several cells become one cell.
Now the cells will be merged as one cell.
Use the spacebar to check merge cells.
Learning Exercise
Using our Weekly Food Expense table, we will merge cells A3 to I3.
Move to cell A3. Our title "Weekly Food Expenses" is there.
Now select cells A3 through I3 by using the Shift Right Arrow keystroke repeatedly.
This will highlite cells A3 to I3.
Now that the cells we want to merge have been highlighted, we will access the "Format Cells" with the Control 1 keyboard command.
Move the focus to the "align" tab by using the Control TAB keyboard command.
Press TAB until the "merge cells option. Ensure that it is checked, and then TAB to OK and press Enter.
Now the Weekly Food Expenses title will reside in a cell that spans across the entire table instead of only above the first column.
Formatting Excel tables for display purposes.
After creating a basic table, simple formatting might be required so that the data is properly and easily displayed. For example some longer words might not be entirely visible, and titles might not be centered etc.
Now the table will be formatted with all words shrunk to fit so that they are visible, titles will now be centred and the table grid will be properly aligned.
Essential Excel JAWS keystroke commands.
JAWS will announce the data in the current cell.
JAWS will announce the coordinates of the active cell.
Set column titles to row range.
Move to the row within the table where the column titles are located and use the above keystroke command.
This will set the column title association to each cell within the table and will allow JAWS to speak the title of the cell while you navigate the table.
This will, of course, offer valuable contextual reference to each cell.
Set row titles to column range.
Move to the column within the table where the row titles are located and use the above keystroke command.
This will set the row title association to each cell within the table and will allow JAWS to speak the title of the cell while you navigate the table.
This will, of course, offer valuable contextual reference to each cell.
This keystroke opens a list box of all cells with data.
This is often useful when you open an unknown Excel table for the first time in order to get a sense of the data within the table especially if there is a lot of informaition to take in. Press Escape to close the all cells with data list box.
Hold the Insert key and tap the F1 key 3 times
This will, as with all commonly used applications, open the JAWS help topics for the current application.
Excel tables for download.
It is highly recommended that you save these Excel files, as opposed to simply opening them, since some Web browsers will open these files within the browser window. You should instead save them to your computer and then open them so that they will open in MS Excel and not as a Web page.
Weekly_Food_Expenses.XLS
Excel_Budget_Template.XLS
Contact the author of this tutorial if you are still having trouble or need more information