How to create a Table with Microsoft Excel
Using spreadsheets such as Microsoft Excel should not be an intimidating task. Using the next steps, you can create a simple table summarizing your data and adding several features to make it more meaningful for your purposes.
First, a simple rule: Use columns to segregate the same information to make it readable and rows to keep data of the same kind.
Example:
Please don’t worry now about how to create the table; we will get to that later. For now, see that each column has a label; below it should contain data pertinent to that label. For our example, we will use a simple grading table for a teacher or professor.
Step 1 – Open Microsoft Excel
Find your Microsoft Excel icon on your desktop, or search for it on your computer, or connect through the available Microsoft 365 online.
Step 2 – Select New, or you can use a template; for our example, we use New.
Note: Microsoft Excel icons have several icons similar to other Microsoft products, with the exception that it has several others pertaining to this application.
Step 3 – Let’s create our table. For safety reasons, I will recommend saving with a name just to make sure that if there is a problem of any kind, you do not lose your work.
- Go to File (upper tab)
- Save As (you will see on your right folder suggestions or you can browse a location)
- Select your location or documents
- Name it; for example, FirstTable
I like to save my work even before any data is entered to make sure that if there is a problem or if I get called and shut down my computer, I will not lose my work.
Step 4 – Start creating the columns!
Type on each cell address the following:
Columns Data
- A1 – Name
- B1 – Last Name
- C1 – Date of Birth
- D1 – Age
- E1 – Exam 1
- F1 – Exam 2
- G1 – Exam 3
- H1 – Total
- I1 – Average
Your table should look similar to the one previously shown.
If you want to center your column labels, you should use the following icon:
You select all cells from A1 to I1 and then press the center icon.
To open more space in your columns, just place your cursor between the columns and drag to open or close as you need more or less space.
Step 5 – Start adding the data to your table; for purposes of this example, we will use imaginary data.
Here is my data; as you can see, I have included similar data under each column.
Step 6—Adding the results of the exams (rows).
On the previous illustration, please note that I have included a formula, =sum(E3:G3). This formula tells Excel that you want to add the values within those columns.
- To obtain the result, you should place your cursor under Total, Cell H3, and type =sum. Excel will automatically open the parenthesis for you; select the range. Please be careful not to include H3 since it will create a circular reference, and it will not work. In addition, please be careful not to add the age column because it will affect the result of your grades.
- You can also do it manually by creating the formula: =E1 + F1 + G1 This will yield the same result.
- To add all values, you can do it in various ways:
o Use the same procedures one by one; effective but not efficient if the table has hundreds of entries.
o Copy and paste down all values; prefer this method.
First, place your cursor on Cell H3, press copy (see icon above), then highlight the range (H3, H4, H5, H6) and press paste (select the first paste icon)
Step 7 – Calculate exams average (rows)
- Place the cursor on Cell I3 and write the formula =AVERAGE(E3:G3) this tells Excel to calculate the average of those three cells.
- You could also write the following and get the same result: =(E3+F3+G3)/3, which is the mathematical expression to get the average, but again if you are working with several numbers, this could lead to inefficiencies or, worse, errors.
- Then, copy and paste the numbers as we explained earlier.
- Please note that some of our numbers have decimals; if you want the same format for your averages, then you need to press the comma icon.
Step 8 – Calculate Exam Averages (columns)
- Please place the cursor on E8 and write the formula =AVERAGE(E3:E6); this tells Excel to calculate the average of the column.
- Use the comma icon to format the values.
In summary:
- You have created a simple table with students’ results on three exams
- Totals were calculated using the Sum formula
- Average were calculated for each student using the Average formula
- Calculated average for each exam using the Average formula
Thank you so much!!