Excel essentials #1 Excel table basics
Once you’ve got your data entered into an Excel spreadsheet, point at any cell which refers to your data and press Control + T. If you have your data set right, one empty column on the side, and an empty row at the bottom, Excel probably got your data borders right with the crawling dashes. If you already have headers, just click OK.
Consistent visual structure
Dynamic ranges
Let’s try to calculate average population density in India. We could use AVERAGEIF function for the calculation. Notice the difference? No more cell names, now we have a structure that looks something like this table_name[column_name]. Why is this important? Keep reading.
Adding new records
We added fresh data, for example city Bhopal which has a population density of 3887 people per square km. Check out what happened to our result. The result changed automatically, but it is very important to make sure that new records are in your table, colored properly.
We took a simple example of AFERAGEIF function, but it is applicable to all Excel functions and objects. The same holds for VLOOKUP (and INDEX MATCH), Pivot Tables, Charts, all of them. We will make sure to keep this idea in mind in our future posts, since this concept is critical to understanding how much time we can save by using tables.
Table name
The first thing we do once we have formatted the table is to give it an appropriate name. The table in our example is called „city“, but you can name it however you like. Why is this important? We can use the reference to the table in all of our formulas. It is just like a variable. If we want to reference a particular column, after the table name, we can type the column name in square brackets e.g. [Population], just like in our AVERAGEIF example.
Total row
Need statistics for each column? No more correcting formulas by hand. On our brand new Design tab, on Table style options group, check the total row box. At the bottom we have got some numbers, and by default calculation is set to column sum. Of course, we can change that to any calculation from the list. Try clicking on some other cells in the last row and try it out.
So, here you go, more than enough to inspire you to start using tables properly. This may be a big step towards you becoming the „Excel guy“ in your office. This is probably one of the main distinctive factors between Excel enthusiasts and ordinary Excel users. As always, if you have any questions, comments, feel more than free to reach out to us.
To next Excel post!