In this post, we’re going to talk about importing CSV files with power query! This is a guest post written by John MacDougall, an Excel MVP from the US. John runs a famous Excel blog called How To Excel where you can find tons of useful tips and tricks. In this post, he will explore the use of Power Query for Importing CSV files into Excel. But not just plain CSV files. CSV files that have different number of rows of metadata.
You might be getting your data from another system like a general ledger, CRM, admin system or any other system that has data you need to report on.
A lot of the time, these will export data as CSV files.
If the CSV files you’re importing are nicely formatted with column headers in the first row of the file, then it’s an easy process to import into Excel with power query. We can select a From Text/CSV query from the Data tab, and pick the file from the resulting file picker menu. From there we can either load the data or further edit it in power query.
If you’ve never seen or heard of power query, then you can learn about power query here. It’s essentially a tool you can use to import data into Excel and then transform the data.
Problems Importing Files with Metadata
Sometimes, it might be the case that these files contain metadata. This is just data about the file such as when the report was run or the period it covers. Usually, these will be in the first couple of rows of your CSV files.
In the above example, it’s not until the 10th row that we see the columns headers and the report data because in rows 1 through 9 we have our metadata information.
This is easy enough to deal with if we always have 9 rows before our column headers start. We can remove the top 9 rows in our query then promote the first row to the column headers.
But what if this changes? Maybe some CSV files will have only 8 rows because there is no account contact information in the system.
If we try to run our query on a file with less than 9 rows of metadata, then we’ll end up promoting the wrong row as column headers and our query will throw out an error in the next step where we try and reference any column.
Can we avoid this and make it dynamic so that no matter what row our data starts on the query will remove the correct number of rows?
Import the CSV
The first step is to import a single CSV file. Go to the Data tab and select a From Text/CSV. In the resulting file menu picker, navigate to the folder where your reports are stored and select one of the CSV files.
You can select any of the reports regardless of which row the column headers appear in.
Press the Edit button to open the query editor.
Power query will also add a Change Type step to guess the data types of the different columns. We only want the imported data as a source without any other transformation steps applied to it , so we can delete this step with a left click on the X next to the step under the Applied Steps area.
Now we can Close & Load the query as a connection only. I’ve named the query ImportData.
Get the Row Number of the Column Headings
Now we will create a query to get the row number which contains the column headings. Remember, this was something that might change depending on the file.
In the Queries & Connections window pane, right click on the ImportData query and select Reference from the menu. This will create a new query with the ImportData query as the source.
Now we should be in the query editor with our raw data that includes the metadata rows. Go to the Add Column tab and press the Index Column button. This will add an index row starting at 0 in the first row and incrementing by 1 each row.
This index column will tell us the row number that our column headers are in.
We can now filter the first column of the data. Left click on the filter icon in the column heading and then deselect everything except for Product. Our column headers are always named the same across our different files, so this will always result in the row containing the column headers being left.
Now our column headers will be in the first row. We can then right click on the first row value in the Index column and select Drill Down from the menu.
This will turn our query results into a single value which corresponds to the number of rows of metadata we need to remove from our file. We can name the query GetFirstRow and then Close & Load it as a connection only.
Remove the Metadata
We have obtained the row number which contains the column headings in the data using an index column, a filter and then drilling down on a single value. Now we can use this value to remove rows from our file.
We can reference the ImportData query again with a right click and selecting Reference from the menu.
Now we are ready to remove our metadata rows. From the Home tab press the Remove Rows button and select Remove Top Rows from the menu. When the pop up appears that asks how many rows to remove, we can enter any value as a temporary placeholder. Let’s enter 9 for now.
Now we need to edit the M code for the last step in the formula bar. We need to replace the placeholder 9 value that we entered with GetFirstRow. This was the name of our query that gave us the single value of the row number containing our column headers. Press Enter to confirm the edit to the formula.
Now we can promote the first row to column headers. Go to the Home tab and press the Use First Row as Headers button.
Change any of the data types as needed, then name the query Report and Close & Load it to a table in the Excel.
Within our changing data format, we were able to find and use the column heading names which remained constant to dynamically get the number of rows we needed to remove.
Whenever you’re faced with changing data formats look for the constants (I like to call these data anchors) and use them to get the results you’re after.
Now if we change the path and file name in the ImportData query to point to another report and it has a different number of metadata rows, our query will still work! We could even parameterize our query with a path and file name parameter so we can easily import any file from any folder, and we don’t have to worry about the changing rows of metadata.
New Data Types in Excel will undoubtedly be THE talking point of the near future Excel evolution. That being said, they are also the reality in Office 365 Fast Insiders. That’s where they were recently introduced by Microsoft. This post will first explain what the new Data Types in Excel are and then we will show some examples of the Data Types in Excel in Action. But we will finish off with what would probably be the fourth verse of Imagine by John Lennon. I will explain what the new Data Types in Excel mean for you and how I see them developing in the future. After you’ll read “the fourth verse”, you should realize why the new Data Types in Excel are a true game changer and why they DO MATTER for you!
What are the New Data Types in Excel
The New data Types are technically Linked Data Types. This means they pull data from an Online Source. Right now, there are two New Data Types in Excel, Stocks and Geography.
But for Understanding, you can think of the New Data Types in Excel as Entities. Every Cell contains a “hidden lookup row” that turns the text within a Cell into a “record” with properties.
A cell containing the name of an Entity (like the name of the City)
Ends up containing this…
But now, you can call any property you want in two ways…
like a formula
or just adding Columns to a Table either Manually or by clicking on the Icon in the top right corner of the Table (see the above image)
If you think about it, it’s as if a cell contains much more information than just it’s content. From today’s Excel standpoint, you can really think about it as if there is a lookup Table. Since these are Linked Data Types the data is actually online but you are still just calling the data you need. And since the Cities are in an Excel Table, you can just throw a new City in there… Let’s add Sofia (Bulgaria)
And there it is. This is the future people. Right now, you can find (if you are running the latest version of Excel) the New Data Types on the Data tab under Data Types.
There are only two new Data Types available at the moment but Microsoft plans to add new Data Types. You can read more about that here and here. Also at the time of this writing the only language that is supported is English.
Short Demo of the New Data Types
Before discussing the future of The New Data Types in Excel as I see it, here is a short gif of the whole process as it works today
Future of the New Data Types in Excel
The following is not an official Microsoft standpoint, it’s just my personal view of the possibilities of the new Data Types in Excel.
The way I see the New Data Types progress (in addition to of course being many more of them) is that each company will be able to utilize them in their own way. Imagine being an HR Specialist in a Company. At this point all the data about your employees is stored in a Database, Excel or even in a SharePoint List. Now imagine entering the name of one of your employees in a Cell in Excel. Then you just go Data/Data Types/Human Resources. It recognizes the name and gives you the ability to use all the data about that employee that the company has without writing a single lookup.
The same could be true for the Company Products, Customers… The Sky is the limit! Thinking about it this way, you can surely see the incredible value this brings to Excel users.
Readers of Excel Unplugged know that I am a big believer in using keyboard shortcuts to be fast and more efficient in Excel. There are a lot of good resources for learning shortcuts but the Excel Add-in from David Hoppe called XL Shortcut is just what the doctor ordered.
I’ve had a pleasure of David Hoppe’s company on many occasions and we were fellow speakers at the Amsterdam Excel Summit 2017 and I must admit I always admired his eye for detail and his chart and dashboard design. You can read more about David and his work on his site excelusive.com. But in this post, I will review the add-in and show you how it might benefit your shortcut mastery.
Let’s start off with what XL Shortcut is. In David’s words: “XLShortcut is an interactive US-layout QWERTY keyboard that shows all Excel shortcuts (excluding shortcuts from the ribbon and dialog menus) on top of the keys under different combinations of the [Control], [Shift] and [Alt] keys. You can execute shortcuts right from XLShortcut, so you can be productive and learn at the same time.”
That sounds really promising. But does it live up to its promise? Let’s find out.
Before we dive in, I want to notice up front that XL Shortcut was designed for English US versions of Excel only. It features an English US QWERTY keyboard layout. If you have another setup the add-in will not work!
If you go to David’s site, you can download the add-in from https://excelusive.com/xl-shortcut/. The good news is that XL Shortcut is totally free of charge. The bad news is that it comes without support and usage is at your own risk.
However, I’ve been testing it for a few days now and all I can tell you it works. And to be honest, it’s just a fancy user form to browse shortcuts, so there’s no risk of using it.
Unboxing XL Shortcut
On downloading the add-in, you will get a ZIP file containing a readme text file and the add-in itself – a plain Excel add-in file (.xlam). Installing the add-in is really easy: just open it and you’re done! However, because this is a Macro-enabled Excel file, system security will block its contents upon downloading it. So before opening the file, you must Unblock the file using the file properties. This is unfortunate but necessary – other great VBA add-ins suffer the same inconvenience imposed by Microsoft’s new safety protocol.
If that doesn’t work for you, simply copy the xlam file to
And if you still don’t see it after restarting Excel go to File/Options/Add-Ins and check if the XL Shortcut is checked.
Once installed you will see a simple dialog that shows you how to activate XL Shortcut. How do you wonder? With its own dedicated shortcut of course! XL Shortcut requires you remember but ONE shortcut, [Shift + Escape] in order to master them all (according to the dialog’s title that is). And closing the interface requires a simple press of the [Escape] key, which is good to know. You can also select Start in the newly formed XL Shortcut group on the Home ribbon.
However you choose to start it up, you will get this
After this ‘heads-up’ dialog, the XL Shortcut keyboard interface is fired up automatically so you can dive right in.
I really like the shortcut captions on top of the highlighted keys. This way I get a sense of where the ‘hidden shortcut-treasures’ lie in a blink of an eye. When you hover over the highlighted keys, a little shortcut description appears in the top right of the user form, including the active key combination, which can be helpful.
In the top-right, we find a ‘hamburger menu’, which will take you to XL Shortcut’s settings dialog:
You can configure a few options and the size of the keyboard. By default, all options are checked:
it supports the Excel 2003 menu shortcuts, which is awesome
shortcut execution is enabled, which also is pretty cool
and if shortcut execution is enabled, we get a key press preview (which is really unclear, I guess you just have to play with it to see what it’s about…)
To close out of the settings you can either select Apply or Cancel your modifications. It also has a little hyperlink to the original webpage where we downloaded the add-in.
But now for the cool part: click on, or press on your keyboard, any combination of the [Control], [Shift] and [Alt] keys and have XL Shortcut reveal all shortcuts at will. For instance, selecting the key combination [Control + Shift] highlights the following keys:
A nice touch is how all key combinations have their own color:
Using the [Alt] key is a bit different. It shows us both the native shortcuts as well as the Excel 2003 file menu shortcuts, as they are still supported up to this day. There are some really cool shortcuts in there that you couldn’t do any other way, like deleting the current worksheet [ALT+E+L]. Traversing up and down the file menu nodes is pretty intuitive as well: just select a key to drill down or use the [BACKSPACE] key, which is highlighted, to return to the parent menu:
Another really cool feature is the option to execute the shortcuts right from the interface: just select a shortcut and the interface will disappear and then the shortcut will be executed. As claimed, this really allows you to learn shortcuts while working, without breaking your workflow.
What really stands out is the level of quality David put into this add-in. This wasn’t just a weekend project – he definitely put in the hours developing and refining it.The interface looks slick & clear, nothing like what you’d expect from a free, VBA-only add-in.
I must say, using XL Shortcut sure beats the hell out of the traditional shortcut listings that are so omnipresent on the internet. It definitely goes the extra mile in terms of usability. From the interface design to interacting with it through both the mouse and your keyboard, XL Shortcut delivers a truly empowering experience.
A word of caution though: shortcut execution is mimicked with VBA’s SendKeys method, which has its shortcomings:
it is dependent on the right language settings for it to work – English US for both your OS and your Excel interface language
it has a reported bug on toggling your NumLock key inadvertently. Luckily XL Shortcut handles the NumLock toggling by turning it off when showing the keyboard and then reapplying its previous state when closing it. A side-effect is that you cannot use your numeric keyboard with XL Shortcut!
Furthermore, I miss a way to search for shortcuts – a simple search field could have done the job. And it’s a pity that is doesn’t support multiple language setups.
But all in all, I have to say, I am really impressed by XL Shortcut and recommend it to anyone that fancies being an Excel-shortcut hero. Again, you can download the add-in at https://excelusive.com/xl-shortcut/.
This post will feature VBA code that will recolour the Worksheet tab based on a set of predefined conditions. Let’s imagine we have a Workbook where each Sheet represents one active project in a company. Cell A1 gives a short status of the project from the task scheduling aspect. The three statuses are
The VBA code will go through all the (Projects) Sheets in the Workbook, check the value (status) of cell A1 and if the status of the Project (Value of cell A1) is “On Time”, the Sheet tab colour will be set to Green, if it’s “Slight Delay”, the colour will be Yellow and if it’s “Delayed”, Red colour will be applied.
So here is the VBA code.
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
ProjectStatus = sh.Cells(1, 1).Value
Select Case ProjectStatus
Case "On Time"
sh.Tab.Color = 5287936
Case "Slight Delay"
sh.Tab.Color = 65535
sh.Tab.Color = 192
This is more or less just a proof of concept code. Although, in project management this would be nice to see. I could also easily see this applied in a Workbook that has Sheets with imported data, intermediate calculation Sheets and final report Sheets which are usually (for organisational purposes) colour coded. And if someone really wants to spent a Weekend on it you could have Conditional Formatting applied to cells and then have VBA code run through those cells and figure out which is the dominant formatting (in sense of colour) and apply that to the Sheet tab. So lots of applications for this…
Here is the original Workbook so you can play with the code and see it in action. (Keep in mind the code was written in Excel 2016).
This weeks post will be a short testament to a development venture by a few of my countrymen. Like most of Excel fans, I’m also a big fan of Power BI and it’s reporting capabilities. But having said that, the built-in visuals have often left me wishing I was in Excel and I could do a bit of customising. About two weeks ago I started playing around with Zebra BI Power BI visuals and I must admit, I was extremely impressed. Up to a point where I said to myself, people should hear about this and that is why I wrote this post.