Qlik Sense can read data from
You may find it useful to make some changes in
Selecting data from
Microsoft Excel sheets
When you select data from
|Field names||Set to specify if the table contains Embedded field names or No field names. Typically in an
Set to the number of rows to omit as table header, typically rows that contain general information that is not in a columnar format.
My spreadsheet looks like this:
In this case you probably want to ignore the two first lines, and load a table with the fields
This means that the first two lines are considered header data and ignored when loading the file. In this case, the two lines starting with
Embedded field names.
This means that the first line that is read is used as field names for the respective columns. In this case, the first line that will be read is the third line because the two first lines are header data.
Microsoft Excel spreadsheets for easier loading with Qlik Sense
If you want to load
Use column headings
If you use column headings in
Formatting your data
It is easier to load an
- Aggregates, such as sums or counts. Aggregates can be defined and calculated in Qlik Sense.
- Duplicate headers.
- Extra information that is not part of the data, such as comments. The best way is to have a column for comments, that you can easily skip when loading the file in Qlik Sense.
- Cross-table data layout. If, for instance, you have one column per month, you should, instead, have a column called “Month” and write the same data in 12 rows, one row per month. Then you can always view it in cross-table format in Qlik Sense.
- Intermediate headers, for example, a line saying “Department A” followed by the lines pertaining to Department A. Instead, you should create a column called “Department” and fill it with the appropriate department names.
- Merged cells. List the cell value in every cell, instead.
- Blank cells where the value is implied by the previous value above. You need to fill in blanks where there is a repeated value, to make every cell contain a data value.
Use named areas
If you only want to read a part of a sheet, you can select an area of columns and rows and define it as a named area in
Typically, you can define the raw data as a named area, and keep all extra commentary and legends outside the named area. This will make it easier to load the data into Qlik Sense.
Remove password protection
Password protected files are not supported by Qlik Sense, so you need to remove password protection from the spreadsheet before loading it into Qlik Sense.