Convert Almost Every Data into a Table

Geschreven door:
Jan Bolhuis, 28 augustus 2017

Since we analyze a lot of data in Excel and a lot of (business) decisions are taken based on those analysis, it’s very important that you are sure that everything is 100% accurate.

One great simple tool to be a lot more safer, is to make Tables from all your data. Tables offers many advantages. Too many to mention for the purpose of this article (create awareness). But one of the most important (in my opinion), is that it makes your data dynamic.

You can also read this article on Linkedin Why You Should Convert Almost Every Data into a Table in Excel.

Want to see a step by step example? Watch this video Een Dynamisch Bereik Met Een Draaitabel.

What do I mean with dynamic?
Imagine you have a data set with 1000 rows and five columns which contains the revenue of all your 5 sales managers per region.

• Column A: date;
• Column B: name of the manager;
• Column C: region;
• Column D: gross revenue;
• Column E: commission.

Of course, you’re familiar with Pivot Tables and have summarized the revenue per sales manager per region, send it to the board and guess what? The board needs more information. They want to know how much commission they have to pay to the individual sales manager.

You add an extra column (F) with the formula ‘Commission * Gross revenue’, go back to the Pivot Table, right click the mouse and choose refresh. Voila, your Pivot Table is updated without manually chancing the source data.

You also add 100 extra rows with new data? No problem when your data is in a Table Format. Just refresh the Pivot Table and your data is updated.

What if you didn’t make use of the Table Format?
If you don’t make a Table of your data, you have to manually update the source data of your Pivot Table. Every time when you add a new (extra) column or row.

And this involves inherent risks, because there is always a big change that you will forget to modify your source data with possible negative consequences for you or your company.

Tables are not only safer but you get also rid of the boring task to manually update your source and saved some time.

https://youtu.be/r_Zj397wfiM
Click on the image for the related screenshots.

Preparation: make sure your data has an header for each column!

• Click in a cell within your data;
• Go to Insert, Table (Excel is polite and shows you a short cut (Ctrl + T) to be faster next time);

A pop up will appear. Verify if the check box ‘My table has headers” is ticked. If not, check all your columns and provide them with a header. Otherwise, tick it manually.

• Click OK and your data is converted to a table.

Give your Table a name (recommended)
Once you have discovered the many advantages of tables, you will use it almost every day. The default name of a table is Table1, the second table Table2, the third Table3 and so on.

Therefore it’s recommended (not mandatory) to give your tables a decent name. Like Revenue, EmploymentDate, Customers or whatever you like.

After you have created a table, click in a cell within the table. An extra tab will appear (Design). Click on the Design-tab and in the left corner you will see the default name (TableX – X stand for a number). Change TableX in a decent name and press Enter.

Wilt u nog meer blogberichten lezen? Ga dan naar de pagina Blog/kennisbank.