Select regular data and choose Insert – Tables or Press CTRL T. Agreed. But there is more to it. Read on to find out the nuances…
Contents
Some tables are created automatically
If you connect to external data, it is automatically converted to a table. Power Query output is also a table by default. There is no way to change that.
However, in this article, we will see how to create tables manually – which is a more common scenario. In this article we will focus on converting existing data to tables.In the next article we will see how to create a new tables from scratch.
Error checking must be done before creating tables
Existing data will have Formulas and references associated with it.There is a very high chance that some formulas and a princess may be outdated.Outdated formulas are shown with the green marker.We must find each such error and correct it before proceeding further.
Refer to these articles to understand how to handle Formula reference related errors.
Auditors, Risk Managers, Everyone: Did you know? These green marks are WARNINGS!!
Green Marks Part 2: Formulas showing wrong results!
All existing Errors must be corrected before converting data to tables. Once table is created it automatically updates all dependent Formulas and references.This works only if there are no pending errors. Hence this precaution is very important.
Selecting the data before creating the table
If you Click inside a data block and choose Insert – Tables, it will work in most cases – Excel automatically selects the data range. It is like pressing CTRL A. In some cases this may select the range correctly. However, in many cases you cannot rely on the automatic region selection. That is why it is necessary to ensure that the selection is correct.
Data heading touching on the block
This is a common problem. There is a title for the data which is touching the data itself.
In this case, Excel selected the entire block including the title. Therefore, the headings were wrongly created.
Totals included by mistake
Another common problem is that there are grand totals at the bottom of some columns which are touching the actual data. These totals are also included in the Table – which is obviously a mistake. You may not even realize this if the data is large. But any calculation on that column will give you wrong results.
Data must be clearly defined
To prevent such problems, make sure that the data is clearly demarcated and there is no extraneous information touching it from all four sides. If required, add extra rows or columns to clearly delineate the data.
Understanding the Create Table Dialog
Insert Table or CTRL T does NOT create the table immediately. It selects the data range and shows a dialog. The dialog has a checkbox called My Table Has Headers.
You must look at that checkbox and THINK before clicking on the OK button.
This checkbox is actually TELLING YOU what Excel thinks about your data. If the checkbox is selected, Excel thinks that your data has a header row. If it is not selected, Excel thinks that your data does NOT have a header row.
In most cases, it understands correctly. If there is no header row, Excel will add generic column names like Column1, Column2, and so on.
However in some cases it gets confused. That is why you must cross-check if Excel has understood things correctly and if it has not, you must correct it by manipulating that checkbox.
How Excel gets confused
In this case, Excel thinks that there are no headers. Why? Because the logic Excel applies to detect existence of headers is based upon simple common sense. It DOES NOT understand the meaning of text written there. It only understands whether it is text or not text.
So if first row has all text and other rows have a different data type, it thinks that the first row is a header.
In this case everything is text – forcing Excel to think that it is all data. But wait, it is smarter than that. See the same data but with first row having different formatting…
Because the first row had BOLD formatting, it assumed – correctly – that there is a header.
In short, look at the checkbox status – look at your data and find out if Excel has understood it correctly or not. If it is confused, change the setting before clicking on Ok.
This is the correct way.
False detection of headers
In this case, you have to CLEAR the checkbox and then manually add the column names after the table is created.
Table Formatting is automatic and useless
Creating the table automatically formats the data. By default light blue and white bands are used. This saves you the effort involved in manual formatting. But in any case, raw data does not need not look nice – because it is never going to be looked at. The output which you produce by processing the raw data is what people look at. So don’t waste too much time in choosing a specific color combination.
Formatting for showing data in PowerPoint
Choose formatting if you are going to display this data by copy pasting it into PowerPoint or elsewhere. Unfortunately, if you choose the default formatting and paste it as a picture in PowerPoint, the white part becomes transparent and creates a lot of frustration.
If you want to paste the table data as a picture into PowerPoint, choose a format which has both light and dark bands having some color (other than white) or choose a uniform color without banding.
Use different formatting to avoid visual confusion
The automatic formatting DOES have a practical purpose. If you have multiple tables – obviously the data in them is different. To differentiate data VISUALLY, make sure you have different color used for each table. If all tables look default blue, you may get confused and perform wrong operations on the wrong table and get totally confused.
Existing formatting interferes with Table Formatting
The automatic formatting performed by Table creation process often fails – when you already have applied some manual formatting.
We will discuss this in the next article…
This article is a part of Knowledge Pack – Excel Tables
***
