power query Archives - Page 3 of 3

How to Pull Foreign Exchange Rates Into Excel Using Power Query

August 24, 2021

by howtoexcel with No Comment power query

In a previous post, I showed how to get stock prices using Power Query. This time around, I’m going to show you how we can do the same for foreign exchange rates, pulling that data into Excel. For this example, I am going to use the currency conversion site xe.com.

Creating the connection

The first thing that we need to do when creating a Power Query connection is determining which web page the data will come from. On the xe.com website, there is a currency table for each day. If I wanted to pull the USD foreign exchange rates into Excel as of August 22, the URL I would use is as follows:

https://www.xe.com/currencytables/?from=USD&date=2021-08-22

The link is convenient because I can easily alter the currency and the date. I’ll go over how to do that later but first, let’s create the connection. To do that, go in the Data tab on the Ribbon and click on From Web button under the Get & Transform Data section:

Get and Transform data section of the Excel ribbon.

Then, on the next screen, I’ll paste the link and click OK:

Entering in the URL for Power Query to pull data from the web.

Once it has loaded, there will be multiple tables to choose from. Table 0 is the one that has the exchange rates:

Selecting the correct table from the Power Query download.

Instead of clicking to load the data, click on Transform Data to make any adjustments to it before it loads into Excel.

Adjusting the query

Once the query is loaded, you’ll see the following window:

Power Query window with foreign exchange rates.

You probably don’t need or want to see all these rows in Excel. So what you can do before loading it is to clean the data up a bit. Let’s say I only want to pull the US exchange rates in Excel for EUR, GBP, CAD, and AUD. To do that, I’ll click on the Currency header and filter for just those options. You can filter the same way you would an Excel table. And once you’re done, you should see the table get a whole lot smaller:

Power Query table after filtering currency values.

To cut down the table even more, I can remove the Name field since. To remove any column, simply right-click on it and click Remove:

Clicking on the Load & Close button will now populate this into my Excel sheet:

Foreign currency rates loaded into Excel.

Now, let’s alter the URL so that it can be adjusted dynamically for both the currency and date.

Using variables in the Power Query link

An advantage of using a link that has the currency and the date in it is that it is easy to change. I’m going to start by creating a couple of variables. The first is for the currency, and the second is the date. I’ve set up named ranges of ‘Currency’ and ‘Date’ the following fields:

When entering the date, I’m entering an apostrophe (‘) first so that formatting isn’t an issue and Excel reads the value as text.

What I’m going to do next is go into Power Query and create these variables. To do so, go back into the Data tab and click on Queries & Connections. Off to the right, you should see your query. Right-click on it and select Edit:

This will open Power Query back up. I’m going to click on Advanced Editor button on the Ribbon, under the Query section:

The query options in the Power Query Home tab.

At the top of the code, I’m going to insert two lines. One for each variable:

Currency = Excel.CurrentWorkbook(){[Name=”currency”]}[Content]{0}[Column1]
Date = Excel.CurrentWorkbook(){[Name=”date”]}[Content]{0}[Column1]

I will also need to adjust the Source, so that instead of this:

Source = Web.Page(Web.Contents(“https://www.xe.com/currencytables/?from=USD&date=2021-08-22”))

It becomes this:

Source = Web.Page(Web.Contents(“https://www.xe.com/currencytables/?from=”&currency&”&date=”&date))

The variables are ready to go. But before using these them, it’s important to remove the Changed Type step from Power Query:

Removing the changed type step from Power Query.

This step looks for exact column names which can cause an error if your names change when downloading data.

Now with that done, you can change the values and your table will change. Here’s what it looks like if I change the currency to AUD and set the date to July 31:

Excel table showing different foreign exchange rates.

The one problem, however, is that now I have AUD and don’t have USD. What I can do is create a separate table of all the currencies I want to pull in:

I can load this table into Power Query by going back into the Data tab and this time clicking on the From Sheet option. I’ll rename the FXtable and it shows below the other table:

Now, if I only want to see the values from FXtable, what I will need to do is merge the queries.

Merging queries in Power Query

Switch over the Table 0 query and get rid of the Filtered Rows step. Then, on the Home tab, there is an option to Merge Queries in the Combine section that you’ll want to click:

On the next screen, I’ll select the FXtable from the drop down and select the currency fields from each table:

Leave the default of Left Outer selected and then click OK:

Power Query after applying merge tables.

Next, click on the button on the FXtable to expand the table:

Click OK on the next section to expand the only field from that table:

Selecting which fields to expand in Power Query.

Which will result in this:

Then, just filter the FXtable.Currencies field so that null values don’t show up, and you’re left with just the currencies that were present on that table:

Filtering out the null values in the FXtable.Currencies column.

Now that the table has served its purpose, I can remove the FXtable.Currencies column and I’m back to what I had before:

Power Query after removing the FXtable.Currencies column.

Now, I can modify the base currency, the date, plus the currencies that I want to show up. Suppose I just want to see the EUR-GBP currency rates for Jan 1, I could enter the following values:

Spreadsheet showing named ranges and the foreign exchange rates.

All you need to do is refresh the data from the Data tab and all the queries will update:

And the data matches what comes from the xe.com site:

The Foreign Exchange rates showing in Power Query.

Are you looking for a way to pull historical data for a currency pair? You can use Yahoo Finance to do that, and the steps for doing that query is similar to how you would pull stock quotes from there (refer to the link at the top of this post).

If you liked this post on How to Pull Foreign Exchange Rates Into Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Dealing With Tables With Changing Headers in Power Query

August 10, 2021

by howtoexcel with No Comment power query

One of the more common errors you’ll run into when using Power Query is when your headers change names. If that happens, and you have a step in Power Query that alters the headers or relies on them in any way, you’ll end up with an error saying a header wasn’t found. This can happen if you are querying a table whose headers change or if you simply change the name of one of them yourself. Below, I’ll show you an effective strategy for dealing with tables with changing headers in Power Query so that even if the header names change, you can avoid running into these errors.

Changing headers in Power Query

Let’s start with the basics of how you would normally create headers in Power Query. For this example, I’m going to use data from the City of New York Expenses, which you can download from here. This is what the data set looks like:

City of New York Expenses in Excel format.

To launch this data into Power Query, go to the Data tab and click on From Sheet. Once the range is selected, click on OK. Once in Power Query, the data looks as follows:

Expense data that is populated in Power Query.

To change any header name in Power Query, all you need to do is double-click on the header. In this example, I’m going to change the first header so that rather than Publication Date, it will just say Date. Then, I’ll go in and click Close & Load to get out of Power Query.

The problem arises if the header in the table were to change. If I go back into the Expense_Actuals sheet (which Excel automatically created for me when I set up the table in Power Query), it shows these headers:

And if I change the first header so that it looks like this:

I’m going to have a problem, because Power Query is going to be looking for Publication Date rather than just Date. Now, if I go under the Data tab and click Refresh All — which will update the query — I get the following error:

Power Query error showing that the column wasn't found.

This is the error that shows up when Power Query can’t find the header. If I modified the header name for Fiscal Year or any other column, then I wouldn’t get this error. The reason is the step of changing Publication Date to Date is hardcoded into Power Query and if it doesn’t find that header, it will give me the above error message.

The key to dealing with tables with changing headers in power query is to demote them, which I’ll cover next.

Promoting and demoting headers in Power Query

By default, Power Query will use the first row of your table as its headers. In my situation, this is a problem because if the header in the table is changing, I could run into errors. To eliminate this, I’m going to demote the headers. To start, I’ll go back to edit the query. To do this, you can go under the Queries & Connections table, right-click on the query and click on Edit:

Then, select the first Power Query step (which will likely just be ‘Source’), and you should again see the table again:

Then, under the Transform tab, in the Table section, click on the option to Use Headers as First Row:

Select the use headers as first row option in Power Query to demote headers.

This has the effect of demoting the headers so that they are now rows. And upon doing so, the header names become just Column1, Column2, etc:

Power query table after demoting the headers.

The option above it to Use First Row as Headers is the exact opposite — it would promote the first row and make that the header for all the columns; it would undo the above step.

However, now with the plain column names, what you can do is go through and re-name each of the headers however you want, which now looks as follows:

Power Query table after changing the header names.

The one issue that remains here is that now we have the old header names in the first row. To get rid of them, go into the Home tab and click on Remove Rows and select the option to Remove Top Rows

And then for the number of rows to remove, just enter 1 and click OK:

Removing the first row from the Power Query table.

And then, the updated Power Query table looks as follows:

You can remove any other steps that were previously in Power Query to avoid it looking for the old header names.

One thing to also remember is the Changed Type step which Power Query automatically generates and looks to convert each header into the correct data type. As you can see from the formula for that step, it will look for the hardcoded header names:

The changed type in Power Query also looks for exact header names and can cause issues as well if you haven't removed this step.

You can either modify the header names within the formula, or you can just remove the step entirely. But this too can cause issues if it is looking for a hardcoded header name. Once you’ve made the last step of removing or changing the Changed Type step, you should be good to go.

Now, when you go and refresh your query, even if you have modified the header names, your data will refresh correctly and put in the header names you have specified in Power Query.

If you liked this post on dealing with tables with changing headers in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Make a Conditional If Statement in Power Query

July 20, 2021

by howtoexcel with No Comment functions left power query

In Excel, IF statements give you way to handle multiple scenarios. You can determine which result to return based on another value or input that a user makes. A common example is where a cell contains no value. You can create a formula to say if the value is blank, you return a result that is blank. And if it isn’t blank, you can perform a calculation. IF statements work similarly in Power Query although you can’t enter them in cells. Below, I’ll show you how you can create a conditional IF statement in Power Query and how you can use it in your data set.

In this example, I’m going to use data from the data.gov website on the Tuition Assistance Program. You can download the CSV data from here if you want to follow along.

Getting the data into Power Query

Once you have downloaded the data, the first step is to pull it into Power Query. For that step, just click anywhere on the data set and under the Data tab, click on the option to get data From Sheet:

The data is fine in the shape that it is right away so there is no need to make any changes when loading it into Power Query.

Creating a Conditional Column in Power Query

Suppose we wanted to just differentiate the data between whether the funding is related to the private sector or the public. You could do a pivot table but if you want to just have a column to pull in those amounts separately, you can create a conditional column. A conditional column works like an IF statement, only it is easier to set up.

One thing to remember with Power Query is if you want to just alter the current column, you want to stay on the Transform tab. But if you want to create a brand new column — which is what I’ll be doing in this example — you want to go onto the Add Column tab at the top:

Once you are on the Add Column section, you will see an option for a Conditional Column right below it:

Click on that button, and then you will see the following window:

Add conditional column window in Power Query.

For the column name at the top, I will call it Private Funding, since that is what I want to calculate. And the criteria is simple: I’m going to set it so that if the Sector Type column is equal to PRIVATE (this is case-sensitive in Power Query), then the output will be the TAP Recipient Dollars column. Otherwise, I want the value to be zero. Here is what that looks like:

Add conditional column window in Power Query with data filled in.

You’ll notice that on the output, value, and else fields, there is a down arrow. Clicking on this will allow you to switch between a column or a value. You can specify if you want to enter a value or reference a column. In this case, I want to reference an entire column if the criteria is met. And if it isn’t, I want to set it to a value — zero. For the operator, you also don’t need to look for an exact match, that too can give you various options:

Once that is set up, I have a column called Private Funding in Power Query that is equal to the TAP Recipient Dollars if it is Private funding only. Otherwise, it is set to 0:

Private Funding conditional column set up in Power Query.

Now, I can repeat these steps for Public Funding and will now have a value in either private or public funding:

Additional columns created for private and public funding.

You may think this is a bit redundant but it saves having to create a pivot table if I wanted to do a summary (or a SUMIF function). One of the great things about Power Query is when I no longer need a column, I can just delete it. If I right-click on the original Sector Type column, there is an option to Remove from the shortcut menu:

This doesn’t impact my table because Power Query saves the steps I take and each time repeats the same order. This way it is safe to remove the unnecessary tab and avoid having redundant data that isn’t needed anymore.

Using the conditional column option is easy but if you want something more versatile to possibly include other Power Query functions, you can also use the Custom Column button, which I’ll cover next.

Creating an IF Statement Using a Custom Column

The option to create a Custom Column is also under the Add Column section:

Custom column option in Power Query under the Add Column section.

In this example, I will create a conditional column to look at if the TAP Level of Study column indicates at least a 4-year degree. By looking at the values there, we can see that the years are indicated in the first number:

Column in Power Query showing level of study.

If this was in a spreadsheet, I could just use the LEFT function to extract the first number. But in Power Query, I’m going to do it a little differently. Instead of the LEFT function, I am going to use the Text.Start function (these are also case-sensitive), which works the same way:

Text.Start([TAP Level of Study],1)

In this formula, I’m selecting the field, TAP Level of Study, and extracting just the first character from that. However, I still need to convert this into a number if I want to evaluate it as one. Next, I need to enclose this within the Number.FromText function. My formula looks like this:

Number.FromText(Text.Start([TAP Level of Study],1))

The next step is to evaluate it to see if the value returned is greater than or equal to 4:

Number.FromText(Text.Start([TAP Level of Study],1)) >= 4

If I am content with just getting back a series of TRUE or FALSE values, then I can stop here. But if I want to customize the values to say ‘YES’ or ‘NO’ then I will need to add to this formula by adding an ‘if’ statement at the beginning. I will also need to use the ‘then’ and ‘else’ keywords to tell Power Query what I want the results to be:

if Number.FromText(Text.Start([TAP Level of Study],1)) >= 4 then “Yes” else “No”

This is how it looks in the Power Query Custom Column window:

Creating a custom column formula in Power Query.

As you can see, going through the Custom Column approach will give you more flexibility as to what you can do with your conditional statements. While the Conditional Column is easy to use, it isn’t as flexible as you might need it to be. Now, when I click OK to create this column, I know have values that show either ‘Yes’ or ‘No’:

Column in Power Query that was created with Custom Column.

If you are looking for other Power Query functions, you can check out this page.

If you liked this post on How to Make a Conditional If Statement in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Unpivot Data in Excel

July 6, 2021

by howtoexcel with No Comment formatting pivot table power query

Using pivot tables to summarize data can be a great way to display information quickly and total everything up. However, in some cases, data that you download is already in what you might call a pivot table format where it is summarized and you want to put it in more of a tabular format. In this post, I’ll show you how to unpivot data in Excel where you can turn a table like this:

into this:

Unpivot using Power Query

Rather than copying and pasting data into a tabular format and doing the process manually, you can just use Power Query to do it for you, all in a matter of seconds. First thing’s first, you need to get your summarized data into Power Query. To do that, click on one of the cells in the table and on the Data tab, click on the From Sheet button in the Get & Transform Data section:

Selecting the From Sheet button on the Get & Transform Data section.

Then, click OK on the default range and then the next screen will be Power Query:

The key to making the unpivot work correctly is to determine which column(s) you don’t want to unpivot. In this case, it is only the Year field as I want to have the years listed out. With the Year column selected, I right-click on the header and select Unpivot Other Columns:

Select Unpivot Other Columns from the menu.

After clicking on that, the data is unpivoted and now it is in tabular format:

All that is left now is to press the Close & Load button in Power Query, which will then populate the data back into Excel:

You can repeat these steps for other, similar summarizes should you need to unpivot data.

If you liked this post on How to Unpivot Data in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Parse Data in Excel Using Power Query

May 25, 2021

by howtoexcel with No Comment advanced parse power query

In an old post, I went over how to parse data using various different functions. This time around, I’m going to show you how much easier it is do that in Power Query. If you’re not comfortable using LEN or MID functions, then this will make your life a whole lot easier. And to keep things simple, I’m going to use the same data set as I did in the previous post, which you can download from here.

Setting up the query

The first step involves copying the data from the webpage and then just pasting it into cell A1. With no adjustments, my data just contains the raw data:

The one thing I’m going to do is remove the blank rows just so that Excel recognizes the full data set without having to adjust it. I can remove the blank rows in Power Query, but I’m going to do it at this stage so I don’t need to worry about finding what row number I need the range to go down to. To remove blanks, I will select column A, press F5, special, blanks, and then right-click delete on one of the cells. Now that there are no gaps in my data, I can set up the query.

To do that, I’ll go into the Data tab, and in the Get & Transform Data section, click on the From Sheet button.

Get & Transform section of the Data tab.

Excel should now autodetect the entire range. Click OK and the query will be created:

Right now, it looks the same as what it was before, except it’s in the Power Query window. Next, I’ll actually start making the transformations.

Parsing the data using delimiters

Just like in the older post, I am going to set up fields for Country, City, and Population. But this time, you won’t have to fumble around and worry about setting up complex formulas. In the Power Query Editor, I’ll select the Add Column tab. And in there, I’m going to select the Extract drop-down selection and choose Text Before Delimiter:

I’m going to use the colon (:) as the delimiter and then click OK

Setting up the text before delimiter in Power Query.

That nicely parses out the countries:

Applying the text before delimiter for the Country field.

I can double-click on the header where it says Text Before Delimiter and change it say ‘Country’

Next, let’s parse out the City field. I need to make sure that Column1 remains selected. This time, I’m going to select Extract under the Add Column tab and then select Text Between Delimiters. I’m going to set my start delimiter as a colon. And the end delimiter will be the opening bracket:

Setting up the text between delimiters in Power Query.

And after re-naming the field to City, this is why my Power Query Editor looks like:

Power Query Editor after setting up the Country and City fields.

The last column to parse out is the Population. For this, I’m going to follow a similar step as above except I’m going to extract the text within the brackets. But in some instances, there is data within brackets that doesn’t relate to the population. But one consistency is that the population always comes at the end. So in this case, I’m going to use the Advanced options and specify that I want to start searching from the end of the string. I have left the other options the same:

The Advanced options in the Text Between Delimiters section.

Now, my fields look pretty good:

Country, City, and Population fields set up in the Power Query Editor.

The one thing I still need to do is remove the headers for the different letters. Since there is nothing in brackets, I can filter for any blank value in the City field. To do, this, I will click on the drop-down arrow for that field and select the option to Remove Empty:

Removing empty values from the City field.

Now, the data looks good and ready to import back into the worksheet:

City field after removing the blank values.

I technically don’t need that first column anymore. It’s done its job and one of the great things about Power Query is I can delete it, and it won’t impact everything else I’ve done. I’m going to right-click and delete that column so that all I’m left with are the fields I actually need:

Power Query Editor after removing the first column.

All that’s left now is to load the data into the spreadsheet. To do this, click on the Close & Load button in the top-left corner. It will now put that into a new tab by default:

Power Query data loaded back into an Excel sheet.

And just like that, you’ve parsed the data without having to go a painstaking effort of figuring out the correct formulas. But as easy as this was, there is an even easier way of parsing the data out (most of the time).

Parsing the data using examples

I’m going to re-do the previous step, this time taking a different approach, without the use of delimiters. This time, I’m going to the Add Column tab and select the Column From Examples button. This will generate another column on the right-hand-side:

Using Column From Examples in Power Query.

What I’m going to do in Column2 is give Power Query some examples of what I want this field to contain. Since it is the Country field, I’m going to start by typing out a country name. Even after just entering the first one, Power Query has figured out the pattern and does the rest for me:

Column From Example after entering in one value for the Country field.

You’ll notice at the top it has the Text Before Delimiter which is what I used when I did this manually. The less complicated the data, the quicker and easier it will be for Power Query to predict what I’m trying to do.

I’ll repeat the step for the City field. This time when I enter just one value, it hasn’t figured out the rest of the values:

Column From Example after entering in one value.

Instead of La Paz at the bottom of the above screenshot, it only pulls ‘La’ and so what I will do is correct that entry manually. Upon doing that it updates the calculations, but they still aren’t quite right. For Bosnia and Herzegovina, it is including part of the country name:

Column From Example after entering in two values.

I will manually update that value to just enter Sarajevo, and once I do that it now looks correct:

Column From Example after entering in three values.

And if I look at the formula that it has generate, it now is the same as what I did manually with selecting the delimiters:

The last column, Population, was the most challenging to set up because I needed to use the Advanced settings. Let’s see how well Power Query is able to extract this one using examples. Again, I’ll start with entering in the first value:

Column From Example after entering in one value for the population.

It doesn’t look too bad except for La Paz, it pulls in ‘seat of government’ which is in brackets, as opposed to the population. I’ll manually correct this one, and upon doing so this is what my column looks like:

Column From Example after entering in two values for the population.

Now the problem is the n/a values aren’t picking up correctly. Once I correct them, the column looks to be correct, except for Delhi:

Column From Example after entering in three values for the population.

After making a few more adjustments, the column looks to be correct:

Column From Example after entering in several values for the population.

One of the challenges with doing it this way is if a field isn’t easy to predict for Power Query, it may take some manual entry before it is able to get it just right. And even then, you may not be certain that you’ve accounted for all the possible variations. While this method can make it really easy for simple data parsing, for more advanced ones you will likely want to familiarize yourself with how to use the different Extract options.

If you liked this post on How to Parse Data in Excel Using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Get Stock Quotes From Yahoo Finance Using Power Query

March 18, 2021

by howtoexcel with No Comment intermediate investing power query stocks tips & tricks

There are a few different ways you can pull stock prices into Excel. You can use the new STOCKHISTORY function, pull data from Google Sheets which has a native stock function, or you can also use Power Query. In this post, Power Query is what I am going to focus on and show you how you can pull data right from Yahoo Finance. Of course, if you don’t want to do it yourself, I also have a template that is ready to use and download (at the bottom of the page).

Creating the query

For this example, let’s pull Apple’s stock history for the past month. To do this, we can simply go to the Yahoo Finance page that shows the stock’s recent price history, located here and select any interval, whether it is five days, one month, or three, it doesn’t matter.

To get the complete data set, I’m going to copy the actual CSV download link from that page, not simply the URL. That way, it is possible to pull a much wider range than the default of 100 days.

I’m going to use that link to set up the query. To create it, go into the Data tab, select the From Web button next to Get Data:

On the next page, you’ll be given a place to enter a URL, and this is where I am going to enter the download link from Yahoo Finance:

Click on OK and Power Query will connect to the web page. Next, you will see a preview of the data and if it looks okay, you can just click on the Load button:

Then, the data will load into your spreadsheet and it should look something like this:

Stock price data downloaded into Excel from Yahoo Finance using Power Query.

If that’s all you need, you can stop here. The only downside is if you wanted to look at a different ticker or change the date range, you would need to get a new link, and update the query manually, which is not ideal at all. This can be automated and takes a little more effort but it can be done by adding some variables and making some tweaks to the query.

Setting up the variables

In Power Query, you can utilize named ranges. In this case, I’ll set them up for the ticker symbol, as well as the start and end dates. That way, I can pull up a stock’s history for a specific time frame. The three named ranges I’m going to create are called Ticker, StartDate, and EndDate which can be entered all in the same place:

For the dates to work on Yahoo Finance, they need to be converted to a timestamp. This is what that calculation looks like:

=(A1-DATE(1970,1,1))*60*60*24

Where A1 is the date. This is what the dates look like when converted into this format:

Those timestamps are needed for the Yahoo Finance URL to populate properly. These are the values that need to be tied to a named range.

Next, these ranges need to be coded into Power Query. To do this, click anywhere on the table that the query created, and you should now see a section for Query in the Ribbon and click on the Edit button:

That will launch the editor. From there, you will want to click on the Advanced Editor button:

Advanced editor in the power query menu.

Then, you’ll see how the query is coded:

You can see the source variable is where the URL goes. To insert a named range from the Excel document into this code, we need to use the following format:

VariableName = Excel.CurrentWorkbook(){[Name=”namedrange“]}[Content]{0}[Column1]

Creating the ticker variable will look as follows:

Ticker = Excel.CurrentWorkbook(){[Name=”TICKER”]}[Content]{0}[Column1]

To keep things simple, I kept the name of the variable the same as the named range within the Excel file. Here is what the editor looks like after adding in the variables for the ticker, start date, and end date:

Power query editor after adding variables.

The one thing that I still need to adjust is the source. This is a hardcoded URL and it needs to be more dynamic, utilizing the variables.

In this part, I’ll need to adjust the query carefully to ensure that it is generated correctly. I will put the ticker variable where the ticker should go, and put the start and end dates (in Unix format). This is an excerpt of how the updated source data looks like:

Note that for the start and end date named ranges, I included the = sign to ensure the variable is read as text.

Now that the source is changed, all you need to do is update the variables and click on the Refresh All button on the data tab, and the table will update based on what you have entered.

If you want to download my template, you can do so here.

If you liked this post on how to get stock quotes from Yahoo Finance using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

7 Ways to Fix a Slow Spreadsheet

February 13, 2021

by howtoexcel with No Comment indirect intermediate power query tips & tricks

Is your spreadsheet running slow or constantly freezing and crashing on you? There are many ways you can make it quicker and more efficient. Below, I’ll cover seven different ways you can fix some of the more common reasons your file may not running well.

1. Turn off calculations for the workbook

The first thing you can do is to turn off calculations. If you don’t need your data to constantly be updating, then the easiest way to ease the load of your spreadsheet is to make sure those formulas don’t keep calculating. To do this, you can go to the Formulas tab and under the Calculation group, select Calculation Options and choose Manual:

How to select manual calculations in Excel.

You can always turn the formulas back on and use F9 to do a force calculation. But if you don’t want to re-calculate everything, a quick trick you can do is to use Find and Replace and replace the equals sign (“=”) with equals. It seems redundant but doing this will refresh your formulas and recalculate the range you selected. This can be an easier alternative to enabling calculations for everything and then having to wait for every calculation to update.

The danger with turning off calculations is that you could end up looking at data that hasn’t been updated and potentially incorrect values as a result of that. This is why you’ll always want to be careful when turning off calculations for an entire workbook.

2. Turn off calculations for individual worksheets

If you don’t want to turn off everything, you can turn off the calculations for specific worksheets. Although there isn’t a way from the calculation options to specify which sheets to turn off, you can do this through a macro.

To do this, go into visual basic (ALT+F11) and go into the ThisWorkbook object:

We’ll want to turn off calculations when the workbook is first opened so you don’t have to remember to do it later. The initial subprocedure looks as follows:

Private Sub Workbook_Open()

End Sub

To turn off a calculation, you just need a single line of code:

Worksheets(“Sheet1”).EnableCalculation = False

Then entire subprocedure look as follows:

Private Sub Workbook_Open()

Worksheets(“Sheet1”).EnableCalculation = False

End Sub

Where Sheet1 is the name of the sheet that you want to turn calculations off for. And if you want to turn them back on, you change the value from False to True.

This isn’t the easiest option, especially if you aren’t familiar with macros or don’t want to worry about coding anything. But with VBA you can turn off the calculations and then use a button to turn them back on.

3. Separate the data into multiple tabs

If you have a data set that includes multiple years worth of data, you may want to consider breaking it up. Have one tab for the current year, one for the previous year, and so on.

Using the INDIRECT function, you can refer to different worksheets in your file. By naming a header the same name as a tab (e.g. ‘2020’), you can make your formulas dynamic and pointing to a different worksheet as opposed to one very large tab. Looking up 10,000 rows in one tab versus 50,000 rows in a massive collection of all your data can significantly improve the time it takes for your calculations to run.

4. Separate data into multiple files

A more drastic move than breaking up data into different tabs is moving them onto completely different files. If you’re carrying lots of old data into one big spreadsheet, consider archiving some of it and saving it in another file. Unless you really need access to the old data all the time, it might make a lot of sense to break up your files and to keep your current version as lean as possible. You could even have your data on one file and a separate file for your reporting while using PowerQuery to create a snapshot of your data and simply refreshing it as you need it.

5. Use COUNTA and INDIRECT to limit the scope of your calculations

The INDIRECT function can also help you to ensure your formulas don’t include too many rows or columns. If you select an entire column to run your calculations on rather than just the first couple hundred rows that actually have data in them is another way you can slow down your spreadsheet.

To do this, you can use the COUNTA function on a range to determine how many cells have values in them. The formula is a simple one that you can run on an entire column:

=COUNTA(“A:A”)

Then, using the INDIRECT function, you can write a formula that does something like this:

=SUM(INDIRECT(“A1:A”&B1))

B1 in the example above contains the COUNTA formula and will give you the total number of rows that are used. Rather than doing this:

=SUM(A:A)

and selecting everything in column A to sum up, you can shrink your range down using a combination of the INDIRECT and the COUNTA function. This can be a time-saver when you’re dealing with complex calculations, especially if there are thousands of rows of data.

6. Minimize formulas and utilize pivot tables where possible

Another way to cut down on resources is to make use of pivot tables. Pivot Tables are more efficient than formulas and using them can help make your spreadsheet run smoother. The one drawback is that they’re not as flexible as formulas are. Refreshing the data also takes seconds and you don’t have to worry about turning calculations off.

7. Make sure your worksheets aren’t too big

Large files can take long to open and they freeze up a lot. A common problem I see is that worksheets are sometimes thousands of rows long even though there’s nothing there. This can happen if you download a large data set into a sheet and clear it out later. And although those rows may no longer be occupied with data, they’re still technically taking up space. This can add several MB onto a file and make it a lot more difficult to run.

How can you spot this issue? An easy way is to cycle through the tabs and click CTRL+END. You’ll be taken to the last cell in your sheet. If that takes you far beyond your last row or column of data, then that tells you your sheet is using up more data than it needs to. What you can do is delete all the cells in between the last cell (using CTRL+END) and the last cell that actually contains information in it. Then, save the file. You should see your file come down in size. Do this for each worksheet that has this issue. Even though the cells appear to be empty, they can be making your file unnecessarily large.

If you liked this post on 7 ways to fix a slow spreadsheet please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Create a Time Log in Excel

December 19, 2020

by howtoexcel with No Comment data entry import intermediate power query tips & tricks

Are you looking for an easy way to log and track your time in Excel? Below, I’ll show you how you can keep track of the time you spend on tasks without the need for a complicated template or to even open up Excel every time to enter in your time. With a combination of Excel and Notepad, you have all the tools you need to quickly and easily track your time and create a log in Excel.

An easy trick to turn Notepad into a log

To make the process of logging time easy, you probably don’t want to have to open up an Excel spreadsheet each time. There’s an easier way to do so and that’s by using Notepad. Open up a new instance of Notepad and write the following in the first line:

.LOG

Save the file as whatever you want, and then close it. Open it up again and you’ll notice there is now a timestamp when you open the file. Because you entered .LOG at the start of the Notepad file, it will now automatically create a timestamp each and every time that you open the file.

Now, when you’re working on a task, just enter in some text, such as “working on Excel,” click save, and close the file. Now, when you’re switching over to another task or want to say that you’ve finished the task, open up the text file again and enter in a new entry. You probably don’t need to say that you’ve ended a task since the start of a new task would effectively tell you that the previous one is over.

The key thing to remember when you’re logging your tasks in Notepad is that you’ll want to save the file once you’ve made an entry, and then close it out. A good place to store the file might be online or on a shared folder, somewhere that you can access it from any computer and that you can easily update from wherever you are. As you keep adding to the log, you’re essentially creating a database of all your entries.

You can create multiple log files depending on what you’re tracking or you can just keep one big list in a single text file. Either way, once you’ve made some entries, what you can do is now extract that time log in Excel, which brings us to the next step:

Pulling the data into Excel

The text file, while useful, isn’t going to be terribly helpful if you want to easily see the time you’ve spent on a given task. This is where Excel can be incredibly useful. To get the information into Excel, go onto the Data tab and import data using the From Text/CSV button.

You can leave the default settings and Load the data as is as it’ll likely leave all your text entries in vertical form, which will still work for our purposes. Here’s a sample of what my log file looks like after importing it into Excel:

If you’re using one of the newer versions of Excel that includes PowerQuery, a connection is created when you import the text file into your spreadsheet. This prevents you from having to re-import the file manually each time to check for changes. You only need to refresh the data and it will pull in the changes for you.

And if you make additional entries to your text file, save it, and refresh the data in the spreadsheet and it will update. Just simply right-click on one of the entries in column A, select Refresh, and the data will update from the file — as long as it remains saved in the same place.

If, after an hour I make another entry to make log file and click on update in the Excel file, the information is up-to-date without having to initiate another import process:

This is where Excel is very powerful and effective in making it easy to pull data from another file. However, the data isn’t in a form that’s terribly useful to us in the form that it’s in now. Let’s move on to the next part: setting up the template in Excel so that the time log will be a lot more user friendly.

Creating a template to populate the information correctly

The data is in column A, and what I’ll do is create headers in columns C:F for the Start Time, the Task itself, the End Time, and the Duration (in minutes). Here’s what that looks like:

Now, I’ll need to enter in formulas to populate all those fields. The start time field will initially pull from the third row in column A, and then it will grab every second row after that. So let’s start with building out that logic.

I’ll start with using an INDEX() formula to pull a value from column A. Since there’s only one column I’ll be extracting data from, the key argument is going to be the row number. The third row is where my first entry is, so for the row number I’ll start with the number three. Here’s what my formula looks like thus far:

=INDEX(A:A,3,1)

I select row 3 and column 1. This will only work for the first value. I need to adjust the formula so that it will automatically adjust based on which row I’m on, so that it knows to take either the first time entry, the second, the third, and so on. The ROW() function is helpful in this case because it will return the row number of the current cell. And since my first entry in the table will be on the second row, I’ll want to remove the first two rows. My row calculation looks like this right now:

3+ROW(C2)-2

For the first entry (on the second row), this will evaluate out to 3, since ROW(C2) will equal 2 and it will minus 2 from that. This still works for the first entry, but if I were to copy this formula down it would not give me the correct result for other entries. For instance, in row 3, the formula would be as follows:

3+ROW(C3)-2 this would evaluate to 3+(3)-2 = 4

But row 4 contains my task description, not the next timestamp. I need to double with each row I go down. I need to adjust my formula for the row calculation back in C2 to be as follows:

3+(ROW(C2)-2)*2

Now, the row number minus 2 will then multiply by 2. If I copy this formula down to cell C3, it’ll look as follows:

3+(ROW(C3)-2)*2 : this would evaluate to 3+(3-2)*2 = 3+(1)*2 = 5

This returns row 5, which is the next timestamp in column A. If I copy the formula down to row 4, then it will return the 7th item in the column, which is again the next timestamp. Now that the formula is correctly returning each odd-numbered row, I can use this formula for the template I’ve created. My full formula in column C2 looks as follows:

=INDEX(A:A,3+(ROW(C2)-2)*2,1)

This will work not only for the initial timestamp but it will also extract entries that come after it. All you need to do is copy the formula down.

I can replicate this for the Task field in column D. The only change I need to make is to use row 4 as my starting point rather than 3. And so my formula for the task column looks as follows:

=INDEX(A:A,4+(ROW(C2)-2)*2,1)

For the end time, I’ll use row 5 as my starting point. The end of one task will be the same as the start of the next task. And then all that’s left is to calculate the task duration in column F. The calculate the difference in times, I’ll start by taking the end time and subtracting the start time. However, this will give me a decimal that isn’t very easy to interpret:

The reason is that Excel converts this into a fraction of a day. A two-minute interval is less than 1% of the 1,440 minutes that are in each day, which is why the number is so low. To convert the duration into hours I can multiply it by 24, and then the number changes to 0.033, which is the fraction of an hour that two minutes represents. But if I want to go further and convert this into total minutes, I’ll multiply this again by a factor of 60. Now my formula looks as follows:

=(E2-C2)*24*60

Now, after rounding off the decimal points, my duration calculation in column F correctly gives me the number of minutes between the start and end time of a task:

The table is now set up and you can just copy these formulas down to accommodate more entries. You’ll end up with a series of zeroes if there’s not enough data in column A. If you want a cleaner solution, what you can do is use the COUNTA() function to determine the number of rows that are in column A and determine whether to apply a formula or not. For instance, in my example, my data goes until the 8th row and so my formulas look fine for the first two entries but after that, there is no end time for the third task and the subsequent entries are full of zeroes:

It’s not a terribly elegant solution at this point. To get around this, I’ll create a rule for each column to say that if there is no entry, it will be blank. For the start time, I’ll add the following to the beginning of the formula:

IF(COUNTA(A:A)<(3+(ROW(C2)-2)*2),””

This will check if there are enough rows in column A to extract a value for the current cell. If not, the value will be blank. Here’s how the full formula looks in cell C2:

=IF(COUNTA(A:A)<(3+(ROW(C2)-2)2),””,INDEX(A:A,3+(ROW(C2)-2)2,1))

For column D, the formula uses row 4 instead of row 3:

=IF(COUNTA(A:A)<(4+(ROW(C2)-2)2),””,INDEX(A:A,4+(ROW(C2)-2)2,1))

And for column E:

=IF(COUNTA(A:A)<(5+(ROW(C2)-2)2),””,INDEX(A:A,5+(ROW(C2)-2)2,1))

For the duration calculation, I will check to make sure there are values in both the start and end time, otherwise, the value will be blank:

=IF(OR(C2=””,E2=””),””,(E2-C2)*24*60)

With these formulas now set up, I can copy them down hundreds of rows down if I want and they won’t result in a series of zeroes or errors:

The data in Excel will now auto-populate as I add more entries to the time log and at the same time it won’t be an eyesore if there is incomplete data.

If you liked this post on how to create a time log in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.