Creating Basic Tables in Excel: A Beginner’s Guide

Welcome to this beginner's guide to creating basic tables in Excel! A table is one of the most basic and useful features, which can help you organize and analyze data quickly and easily. In this post, I'll walk you through the step-by-step process of creating basic tables in Excel, using clear and concise instructions that a newbie can follow. So let's get started with creating basic tables in Excel!

Hi, I'm Lindsay and I'm an Excel Guru! Creating basic tables in Excel is what I do every. Single. Day. This is my life, and love it! I've worked as an Office Supervisor looking after budgeting, assets, SOPs, etc. and nearly everything I did included a spreadsheet table and you'll find that once you start creating basic tables, you'll think of a million things that you can toss in a table. So let's get started with creating basic tables in Excel!

Before we get started, if you need a little refresher on the basics of Excel, check out my Introduction to Excel post. It lays out the basic terminology.

Step-by-Step Guide to Creating a Basic Table in Excel

Before we get started on how to create a table in Excel with multiple columns, let's make sure that your data is set up properly. Open Excel. If you already have data entered that you want in a table, open that worksheet. If you still need to enter the data, open a blank worksheet and enter your data with the following rules in mind.

• List the headers in Row 1 going across the columns as needed
• Do not leave any blank rows or columns
• Blank cells (vs rows or columns) are acceptable only if there is no data to report.

Selecting data to include in the table

The first step in creating a basic table in Excel is selecting the data that you want to include in the table. This can be any data you want to organize in a tabular format, such as sales figures, expenses, or inventory. In the example, the data notes trades students by location, age, and if they've completed their journeyman certificate.

To select the data, click anywhere within the data and in the Home tab and click on Format as Table. Choose the table style you want to use.

Converting the selected data to a table

Once you have selected the table view that you want, Excel will automatically select the range of cells that you highlighted in the previous step, so all you need to do is make sure that the “My table has headers” box is checked if you want to include the column headers in the table. Press ok.

Press ok. Excel automatically applies the template you chose and accentuates the header along with adding a filter pull-down box. This will allow you to easily filter the data.

Formatting the table headers

After converting the data to a table, the next step is to format the table headers. This step is optional because the table format you chose will differentiate the header already, but changing it even further can make your table easier to read and more visually appealing.

To format the table headers, simply click on the header row of the table and then use the formatting options in the “Home” tab of the Excel ribbon to change the font, font size, color, and other formatting options. In the example below, I changed the font colour to yellow, and the cell colour to black for contrast.

Adding data to the table

With the table created and formatted, you can start adding data to the table. To add a new row to the table, simply click on the last row of the table and start typing. Excel will automatically add a new row to the table and format it to match the rest of the table.

To add a new column to the table, simply click on the last column of the table and start typing the column header. Excel will automatically add a new column to the table and format it to match the rest of the table.

Formatting the table as needed

Finally, once you have added all of the data to the table, you can format it as needed to make it easier to read and more visually appealing. This can include changing the font, font size, and color of the text, as well as adding borders or shading to the cells.

That's it! If you have been following these instructions, you just created your first table in Excel, congratulations!

Tips for Working with Basic Tables in Excel

Now that you know how to create a basic table in Excel, let's look at some tips for working with tables. These tips will help you make the most of your tables and save time when working with data.

Resizing columns and rows

Sometimes the data in your table may be too long or too short to fit in a column or row. To adjust the column or row size, simply click and drag the boundary between the column or row headers (see red arrow) until the desired size is achieved.

Another way to adjust the width of a column (or row) is to right-click on the column or row (in this case I right-clicked on the “E” which highlighted the whole column E and opened up the menu. Click on Column Width and another window will open where you can type in a number higher (to make the column wider) or lower (to make the column less wide).

B. Adding and deleting rows and columns

If you need to add or remove rows or columns from your table, simply right-click on a row or column header and select “Insert” or “Delete.” You can also use the “Table Tools” tab to add or delete rows and columns.

Applying table styles

Just like when we first set up the table and chose a table style, you can change it at any point if you want a different look. Click anywhere within the table and go to the Table Design ribbon. Choose a new style under table styles.

D. Sorting and filtering table data

Sorting and filtering can help you organize and analyze your data more easily. The little arrow in each header cell is a way to easily sort and filter your data. In the example below, if I unclicked New York, you wouldn't see any row that have New York as the City, the would be filtered out. You'll notice you also have the option to sort by acsedning or descending order, or even colours if you've done conditional formatting (a topic for another day).

If your table header does not have the arrows, Click on the “Filter” button in the “Data” tab and select the criteria you want to use.

Using formulas to manipulate data in the table

Excel's formula functions can be used to manipulate data in your table. For example, you can use the SUM function to add the values in a column, or the AVERAGE function to calculate the average value. I've written a blog specifically for formulas.

Tables, Tables, Tables!

At the end of the day, creating basic tables in Excel is a simple and effective way to organize and analyze data. By following the step-by-step guide and utilizing the tips and tricks provided, you'll be creating tables for all sorts of things like budget tables, shopping lists, etc. You can make a table out of almost any sort of data. Remember to experiment with different formatting options and functions to make your tables more visually appealing and informative.

Good luck and happy spreadsheet-ing!