1.4 Creating an Excel Table
Analyzing related portions of data is easier if you create and use a table in Microsoft Excel. A table allows you to segregate data on a single sheet that you can then manage separately from the rest.
An Excel table is a named object that allows you to manage its contents independently from the rest of the worksheet data. It is a tabular structure that contains related data organized in a series of columns (fields) and rows (records).
This lesson focuses on how you can create a simple worksheet and later convert it into an Excel table.
By the time you complete this lesson, you should be able to:
1. Create an Excel worksheet.
2. Create an Excel table.
Creating an Excel Worksheet
A workbook is an Excel file that contains one or more worksheets to help you organize data. A worksheet is a collection of cells. You can create a new workbook from a blank workbook or a template.
The video in figure 1 explains briefly how you to create a new workbook:
Fig 1: Creating a workbook. Credit: Microsoft Support
You can use any of the following two methods to create a worksheet of workbook:
1. Start MS Excel.
2. Click Blank icon.
3. Type the data of figure 2 in the default worksheet (Sheet1):
The sample worksheet created is a range of cells with cell A1 and G4 as initial and terminal cells. This range of cells is written as A1:G4.
You may create a worksheet of a workbook from a template by doing the following tasks:
1. Click the Home tab.
2. Click New command.
3. Double-click any template.
4. Type your data as displayed in figure 2.
Creating an Excel Table
To convert a range of cells into a table, you need to explicitly format it as such. The video in figure 3 is a summary of how to create an Excel table.
Fig 3: Creating an Excel table. Credit: Microsoft Support
You can simply create a basic table by using any one of the following two methods:
1. Select a range of cells (A1:G4) in your data file (figure 4).
2. Select the Insert tab (figure 5).
3. Locate the Tables group.
4. Click Table command. This will insert a table with the default style (figure 6).
1. Select a range of cell (A1:G4) in your data file (figure 4).
2. Select Home tab.
3. Click Format as Table in the Style group (figure 7).
4. Choose a style for your table (figure 8).
5. Confirm or set your cell range in the Create Table dialog box (figure 8).
Whatever method you choose, Microsoft Excel automatically selects the entire block of cells including the headings. You must verify if the range is selected correctly.
7. Mark My table has headers option.
8. Click OK.
The Excel table with formatted template style is now displayed in figure 9.
Use MS Excel to do the following tasks:
1. Enter the following data in the default active worksheet.
2. Add 7 more arbitrary records to your data file.
3. Use Save command to save your file as mydata.
4. Convert mydata command to an Excel table.
5. Use Save As command to save your table as mytable.
You now have two saved files – mydata and mytable. Both files will be needed in subsequent exercises.