Indzara Support

We use Excel’s Table feature extensively in all templates on indzara.com. This article will cover the basics of Tables that one needs to know before using the templates. This article is intentionally kept simple so that it is easy to follow by Excel beginners who have not used this before.

TOPICS COVERED

• What is a Table?
• Why do we use Tables?
• Components of a Table
• 3 Basic Data Entry Operations
• 5 Essential Tips to avoid mistakes

VIDEO DEMO

If you prefer video demonstrations, you can learn the contents of this article by watching the video below.

WHAT IS A TABLE?

Excel Table is a rectangular shaped dataset, which has one or more rows and one or more columns. Once we make a dataset a Table, Excel activates a lot of useful features.

PURPOSE OF TABLES

1. Stores information in an organized way
2. Makes it easy to retrieve information when needed
3. Acts as source of calculations

Here is an example Product Categories Table.

This is a simple table of just 1 column and many rows. We can use this to store our list of product categories.

COMPONENTS OF TABLE

The following Products Table has 4 columns and many rows.

The top row is the header row. This row houses the names of the columns so that we understand what each column represents. Columns are also referred to as Fields.

In this example, we have a list of products where each row represents a unique product. We are storing the product’s attributes such as Name, Description, Category and Starting Inventory. We can type text, dates or numeric data directly. We can also enter formulas to calculate if we need.

The appearance or the design of the table can vary. Excel provides many options to choose colors, borders and fonts as desired by the user. We can change them from the Table Styles in the Table Tools – Design ribbon.

I usually use a style that clearly shows the header as well as the border of the last row. This allows us to visually see the first & last row and thus know the boundaries of the table. I use the banded rows approach so that it’s easier to read data across. The colors used for header row may vary from one template to the other. For consistency, I am implementing Green colored headers for calculated columns and other colors for user entry columns.

DATA ENTRY OPERATIONS

Entering a new row of data

If you find that the table is empty and just has the header row and no data rows, then start typing in the first row following the header row.

Here, we want to start typing from row 3 in column A.

Once I have entered the first row of data, then start typing in row 4 to enter second row of data.

You can see that Excel automatically expands the boundaries of the Table. Now any calculation that is done somewhere else that uses this table as source will now include this new row of data automatically.

Inserting a row of data

Let’s say we want to enter a row of data in between the two rows entered already. We would right click on cell A4. This will open a menu as shown below.

We will choose ‘Insert – Table Rows Above’.

Now, you can type in the new inserted row.

We can insert multiple rows at once by just selecting multiple cells first before right clicking and choosing Insert – Table Rows Above.

Deleting a Row of data

Let’s say we made a mistake and we don’t need that second row anymore. If we want to delete that row, we can right click on cell A4.

This time, we choose ‘Delete – Table Rows’. This removes that row.

After that, our table looks as it did before, without that row.

We can delete multiple rows at once by just selecting multiple cells first before right clicking and choosing Delete – Table Rows.

TIPS

1. Ensure data is entered inside the Table

The most common reason why users find that the output of the template does not reflect the inputs is that the input data is not entered inside the tables correctly. To know if your data is inside the Table, click on the cell and see if Table Tools ribbon appears.

If it does, your data is inside the table. If not, it is not inside the Table and hence will not be reflected in any dependent calculations.

If you are inside the table and want to know the boundaries of the table, press Ctrl+A. Excel will select all the data cells inside the table and thus show us the boundaries.

2. When copying data from somewhere and pasting, always use Paste Special as Values.

When you right click inside the table (wherever you want to paste data), you may see this option under the Paste Options as shown above as Option 1. If not, then click on Paste Special and choose paste as values under Paste Values sub-menu (shown as Option 2).

Default Pasting method (Ctrl+V) will paste not only the values, but any formulas and formatting. This is not desired as 1) it may create links to external files and 2) it will overwrite the formulas and formatting expected in the template. Please avoid.

3. Avoid Blank Rows

It is recommended to not have any blank rows of data in tables. As we saw before, just select them and delete those rows.

4. Do not edit cells with formulas

Usually in the templates from indzara.com, we have formulas in some calculated columns. Please do not edit them. If you did it by accident, you can undo by pressing Ctrl+Z immediately.

5. Do not rename fields with pre-defined column labels

Depending on the template, some of them use pivot tables (another Excel feature) for calculations. In such cases, renaming a field that is used in a pivot table will break calculations. In our templates, we provide what we call as Custom fields which can be renamed without any concerns. You can add new columns if needed. But renaming other existing fields should be avoided. If you have the need to rename and are not sure about the impact, please contact me and I will help.