Learn Excel as you go – Part 5 – Tables

Updated: Jan 24

In the previous part, while doing the Insert Toolbar, we came across Tables and Pivot Tables. In this section, we will deal with Tables only, in detail. In the upcoming tutorial, Pivot Tables will be discussed.


To practice Excel you need a data sample. Use this website to generate fake data of any fields and length you wish. https://mockaroo.com/


Benefits of Making a Table:


As discussed in the previous part, the following are the main benefits of converting your data to a table:


1. Drop-down filters put on every column

2. Column Headings are Always Visible

3. Automatic Totals – Even When You Filter!

4. Banded Rows – to Make Your Data Easier to Read

5. Dynamic Named Range is created whenever you create a Table

6. Using formulas become easy

7. Tables automatically expand when you enter data in the next row/column

8. Use a Form to speed up data entry in Excel

9. Graphs/charts will automatically update to include all of your data

10. Formatting will be consistent for all records within a Table

11. You can revert a Table to a normal data range (and still keep your formatting and layout!)

We will discuss the Tables in detail. Suffice here to say that whenever you have a medium to a large amount of data or huge data, and it is changing all the time, always convert the data into the table.


How to convert the data into the Table:


I have fake data on car models, makes, and manufacturing year. It is composed of 1000 rows and is continuously expanding.

Needless to reiterate here that all data must have a heading row, Excel will always take the first row as the heading row. The best way to handle this amount of data is to convert it to a Table. Just click anywhere in the data, and press Ctrl+A to select the whole data. Then under Insert Tab, click on the Table:

You will see this dialogue appears:

As you have selected the whole data, by using Ctrl+A, you can see the range of your data in the “Where is the data for your table” field. You can also see that “My table has headers” is also selected. Leave it as such. Now just click OK and all of your data is automatically converted into a Table.

Format the Table according to your colors and bands


You can see that the table [1] is formed with alternate bands of rows. This makes your data easy to read, and you will not confuse a cell belonging to one row or a column with the other if the data is huge. You can change the color, pattern, and the likes of it directly in the Format Tab.

Just play around with these controls to see what happens. This is all just for the looks and ease of use of the Table and is not much to do with data handling.


Naming the Table


As you can see that [2] arrow is pointing towards a box labeled “Table1”. This is the name of the table. You can change this name to anything related to the data, for example, I will change it to “car-models”. The name is very important as we’ll see later. Name is used to point, in a formula, towards the data.


Resizing the Table


In the above image [3], you might have noticed “Resize Table”. This does not mean to increase or decrease the physical size of the Table, but it refers to the size of the data a table holds. If you want to take some columns or rows off of the table manually, you can do it by clicking here and change the range of the data this table is composed of.

Checking the name of the Table


You can check the name of your table also in the Name tab. Click on Formulas in the main menu, and then click on “Name Manager”:

You will see the following dialogue box:

You can see the name of the table along with the range of the cells on a particular sheet used to make this table. Notice that it is grayed out and cannot be changed. You can change this range in the Design Menu as discussed above.


We will discuss the “Name Manager” feature later in our other series of Excel. Please go on following this series and you will learn more than you might expect.

How you can use your Table and what benefits will it give you in the handling of the data? This is the question, isn’t it? As I have mentioned previously that whenever you have a large amount of data, always convert it to a table. As you will learn in the following paragraphs that data handling will become much easy and hustle free if you do that.


A Totals Row is automatically Added


In addition to the first row being visible even you scroll and data filters put on every column, there is a “Totals Row” added automatically. You can argue so what, using freeze panes and putting data filtering can also be done easily by myself. Yes, you are right, but these are just one or two of the automatic tasks, as examples, which is done when you convert Range to a Table. More automation is coming ahead and I will explain every one of it.


Right-click on any cell of the table and you will see “Table”, hovering over it will open up a sub-menu, under which you can see the “Totals Row”:

Click on it and you will see an extra row has been added at the end of the table with the designation “Total”. Now if you click on any cell of this “Total Row” you will see a small arrow show up, click on it and you can see several functions listed (as I have chosen the car year column):

You can use any of the functions here for this particular column. For example, if I want to see which is the lowest year in my Car Model Year, I will select “Min” and it will show me 1992 and so on. If you don’t want to display any function, just select “none”, or if you don’t want a “Totals Row” altogether, just repeat the procedure of showing it and click on it again and this row will disappear.


As we will proceed, I will show you different functions to us in Excel, so if this Totals Row is not making sense right now, it’s okay, because you may not be familiar with a lot of Excel functionality.


Adding a New Column or a Row to a Table


Now it is very convenient to add a new column or a row in a table.


Suppose I want a new Column at the end of my Table with the name of “Car Engines”. What I need to do is to just type these words in a cell to the right of the last column in the topmost, i.e., header row.

You can see that at first, it seems like I am out of the range, but wait. When you finish writing the new column header, just press Enter and see the magic. A new column is automatically added in the full range of the Table (till as many rows the table has).

Not only this, but a data filter arrow is also added to this column as well. If you compare the data range in the table with that of before, you will see that now the “I” column is included in that range. You can see the data range by clicking on “Design” and then clicking on “Resize Table”.


Similarly, I can also add a row just by start entering the data at the end of the table. In this Table, I have 1000 rows, so if I start writing 1001 in the first column and Just Press the “Tab” key to go to the adjacent column, a new row will be added automatically and will be included within the Range Size of the Table.

A point of caution, if you have enabled the “Totals Row”, you have to remove it before you can add more rows. As “Totals Row” is treated as the last row of the table.


You don’t have to adjust the data range each time you remember to add a new column or more commonly to add more rows. When you will be referring any of your formulae to this Table, you will understand the benefit and relief it can give you.


Inserting or deleting a column or a row in between the data


If you are working with just a simple data range you can Insert a Column or a Row within the range anywhere by just using Insert on the Home Menu (keyboard shortcut for this is Alt+I then C for columns and Alt+I then R for rows):

It is also that simple in the table, but with a different set of commands, which can also be found under the Insert in the Home Menu. Instead of selecting Insert Sheet Rows or Columns, you Select Insert Table Rows or Columns. A new column or a row will be inserted within the data and the table range will be automatically adjusted accordingly.

It’s as simple as that.


Likewise, if you want to delete any column or row, you have to use Delete Table Rows or Delete Table Columns instead of Sheet Rows or Columns. Just click on a cell of a column or a row that you want to delete, and in the Home Menu on the far right, you can find Delete Command. Do as required:

The best part yet


We have not yet started with the formulas, but I am just counting the benefits of a table, so I will use a simple formula to show.


Suppose in the last new column which we have added, I want to use a simple formula such that if the car model year is earlier than 2000 write “Old Car”, otherwise “New Car”. The Formula will be starting with an equal sign:


=if(c2<2000,”Old Car”,”New Car”)


As you can see Car years column is “C” which is why I have used this. In a previous part, I also told you that no need to write the cell reference, i.e., C2 manually, just click on the cell and its reference will be automatically selected. So, we will do just the same while writing this simple formula in the last column. Look at this image:

After writing “=IF(“, as I clicked on C2 i.e., the second cell under Car Model Year Column instead of C2, Excel wrote, “[@[Car Model Year]]”. This is what I wanted to show you. You don’t need to write the cell reference in the old fashion. If you are writing a formula within the Table, just write the heading of the column from which you want to pick data (in this particular format) and the right data will be picked by Excel of that same row. Press Enter and you will notice 2 amazing things happened. First, the result of the formula will be displayed, as expected. But the thing which you didn’t expect is that this formula is automatically applied to all the rows of this column. It is just one of the examples of automation I was talking about earlier. You don’t have to copy the formula down to your 1000 or maybe more rows. You don’t have to worry about checking for the correct references for each of the formulas, and you don’t have to update the formula if any other change is made elsewhere in the table. All these changes will be made automatically for you. If it is just a range, all this you have to do manually.

Similarly, as you will be entering new data in your table, i.e., a new row. Just go on entering the data and don’t worry about the formulas in any of the cells of that new row. Formulas will be automatically copied down from above for that row.


Inserting a Slicer


Another big advantage of using a table is Slicer. It works the same as Data Filter but with style. Just click on any cell within the Data and go to Design Menu and Click “Inert Slicer”:

You will see this, if it is put on the Table, just drag it and put it out from the Table, because of two reasons, i.e., when you will use it, it may hinder your view, and secondly, when the data will be sliced, it will also disappear with the sliced data.

Now select any of the columns within the slicer, for example, I selected the color as shown in the above image. Click OK and you will see this:

Again, move it out of the table and place it on a plain sheet. Now play with it, click on any color of the car and the data will be sliced (or filtered) according to your selection of the color. Pretty isn’t it. Go on playing and selecting different colors and see the magic. It’s so convenient and easy to use slicers instead of a data filter.

Right-click on the slicer and click Slicer Settings

As you can see below image as there are multiple slicer settings. Most of them are self-explanatory and not difficult to understand. Just go and work with them.

Remember the name of the slicer can be used to refer to this particular control in formulas and VBA and not for display purposes. It is better to leave as default name given by Excel, otherwise, choose a name which not only you can remember well according to the slicer but can be validly used for reference.


If you no longer need a slicer, just click on it and press Delete on the keyboard. You can always get it back by repeating the above procedure. You can get as many slicers as you want for every column of your table. You can also place these slicers on any sheet of this workbook.


You can change the slicer settings, its colors, etc., here. This menu will only appear when you click and select the slicer.

Referring to the Table or a value in the table outside the table


From outside the table, for example, on another sheet you want to take a value of a cell in the table, the method is simplified and you don’t have to worry about selecting the correct reference. Let me show you how.


Suppose you have data on another sheet of your customers having cars from this table. You want to put the color of the car from the table (I am just using simple formulas to explain). That is how you will reference the car color from the Table:


“=car”

As you remember your table name, as soon as you started to enter the car, the name of the table showed up. Just press the Tab key and the name of the table will be put in the formula. After that put a bracket “[“, and you will see all the column headings in the table:

Just click on the color (as this is what we want) and either press the Tab key or Double click the color and you will see that it is being inserted there. Just end the formula with a closing bracket “]”, and press Enter. The value corresponding with the row number will be inserted here. In this case row number 2.

Of course, you can put more complicated formulas to get the values.


Please keep in mind that with such a reference call, the corresponding row data will be picked. For example, if you are putting a formula in row 2 and calling data from the table, row 2 data of the asked column will be picked.

Although you can get a specific row data unrelated to the corresponding row number, that is a little bit complicated and may be dealt with later.


That’s it for this part. We will continue in the next parts with Forms, Graphs, and Pivot Tables. Go on following this series and you will enjoy how easily you can learn Excel, bit by bit. Thank you very much for your support.

Learn Excel as you go – Part 6 – Pivot Table

Learn Excel as you go – Part 4 – The Insert Toolbar


40 views0 comments
 

If you are looking for comment without logging in, just Scroll UP a little and leave your comments. Thank you!

2019 Vividnstylish.com. All rights reserved.

Send me an email to james1967harry@gmail.com or find me on social media:

  • Facebook
  • Twitter
  • Instagram
  • YouTube
  • LinkedIn
  • Pinterest
  • Tumblr