Posts in category: Uncategorized

You can import a lot of different data into your Excel spreadsheet. In this post, I’ll show you how you can get tables from Wikipedia both manually and with the help of Power Query. For this example, I’m going to use the page for the largest fast food restaurant chains. The data is sorted in a table, making it ideal to import into Excel.

How to import a Wikipedia table into Excel by copying and pasting

1. Copy and paste the values. Since the data is easily organized in Wikipedia, you can just use your mouse to drag, and copy and paste everything into Excel. The hardest part is just to make sure you’ve selected everything you want to copy.

Here is how the data looks after you’ve removed formatting:

Pro tip: if you have the latest version of Excel, you can use CTRL+SHIFT+V to copy the values without any additional formatting.

2. Remove the comments and values in brackets. In Wikipedia tables, you’ll often see references and notes after values. To remove this, you can use find and replace in Excel to get rid of the values. Set it up so that you replace [*] with nothing. The asterisk will remove everything which comes between the brackets, as well as the brackets themselves.

You can repeat these steps if there are any values in parentheses ( ) as well and any other special characters. After these steps, your data should look much cleaner. The downside is that it may take multiple adjustments to get the data cleaned up correctly and for all the issues to be accounted for.

Import Wikipedia tables using Power Query

Another way you can import tables is with the help of Power Query. Here’s how you can do that:

1. In the Data tab, select From Web and copy the link and press OK.

2. Select the table which resembles the data you want to import. Oftentimes there can be multiple tables, so it can be a good idea to cycle through them to see which one is the best match for your data. Then hit the button to Transform Data.

3. Remove any unneeded columns. To remove columns in Power Query, right-click on the headers and select Remove.

4. Clean up the data. You can use the Extract option in Power Query to grab values that come before a specific character. This is similar to the find and replace function but it can remove everything before a value:

Here you can specify the opening parenthesis as the character. You can repeat this step for brackets and other characters as well. This type of find and replace can also be done within Excel by just using *( to grab everything before the opening of a parentheses or *[ before an opening bracket.

Then, convert the values to Whole Number to ensure they are formatted correctly. If they aren’t, you’ll see some error values, in which case you’ll have to go back to the previous step to correct them. Otherwise, you are done and ready to move on to the last step.

5. Load the data back into your Excel spreadsheet. For this step, all you need to do is click on Close & Load on the Home tab.

Your formatted table is now loaded into Excel:

The benefit of using Power Query is that it saves your steps just like a macro would. If you want to refresh the data and download it again, to check for updated information, you can just right-click on the table and select Refresh.

If you like this post on How to Import Tables From Wikipedia Into 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

An Excel spreadsheet has lines and displays like a grid. And rather than buying graph or grid paper, you can print it out yourself and customize it. Below, I’ll show you how.

How to create your own graph and grid paper in Excel

1. Select all the cells in your spreadsheet. You can do this by using the CTRL+A shortcut.

2. Adjust your column width. Right-click any column header and re-size the column width to 2. You can make this smaller or larger if you prefer.

3. Select the PDF printer. Go to File -> Print and select Microsoft Print to PDF.

4. Modify the margins. Instead of Normal Margins, select Custom Margins. Set the margins all to 0 to maximize the space on your sheet. Also, on the margins page, select the options to center your page both horizontally and vertically.

5. Select the cells on the main tab. Go to the View tab and select Page Layout to see what an entire page will look like. Select all the cells for that initial page.

6. Adjust your borders. On the Home tab, select the drop-down option for borders and select More Borders.

Here you can adjust the color and look of your gridlines. Although Excel looks like it has gridlines on the cells, they won’t actually print out until you do this step and actually format them. I prefer to use a moderate grey color so that the outline isn’t too dark or too light.

6. Save the file as a PDF. By saving the gridlines how you want, you now have a graph paper template which you can re-use to print out whenever you need to. If you want 1 page or 100, you can just create as many copies as you need.

The benefit of setting up your own grid paper is that you can customize it so that it looks just how you want. For example, you could decide to highlight certain cells a specific color and apply different types of formatting to suit your needs.

If you like this post on How to Print Graph / Grid Paper 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 me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

Want an easy way to track your expenses? In this post, I’ll show you how you can use my free expense tracking template, which is based in Google Sheets. It makes copying and pasting expenses over simple, and you can quickly see reports that summarize your spending. No need for macros or even refreshing data.

How the expense tracker template works

There are just two tabs in the expense tracker template. One is Data tab where you enter all your expenses, and one is the Summary tab that has charts where no data entry is required.

Entering the data

On the Data tab, these are the following areas where you’ll enter in information:

• Columns A & B are for the Vendor and Amount. Expenses should be positive and refunds are negative. This can just by copy and pasted from your bank or credit card statement downloads.

• Column C is optional and only necessary if you don’t want an expense item to go to its default category. For example, you may have a one-time expense that throws off your budget for a spending category. You can check off the box for ‘Irregular’ and it will flow through to that category and bypass the default spending category.

• Columns G and H are where you will set up your Vendors and which spending category they relate to. This is important so that when you enter your transaction data in columns A & B, the Category field (Column E) will automatically populate as well. Otherwise, everything will go into a “Not Categorized” bucket.

• Column J (aside from the first row which is reserved for income) is where you will set up your spending categories. The spreadsheet accommodates 10 categories. Any more than that and the charts can become difficult to read.

• In Cell K1 you can enter an income amount (if applicable). The other values in column K are simply a tally of the spending by category.

Once you have all your transactions entered, you can go to the Summary tab where you’ll see a summary of your spending.

The reports

There are three charts on the Summary tab:

• A waterfall chart is the main chart that you’ll see on the page. It shows you your spending during the period. This starts with an income amount (this is entered on the Data tab) and every expense is negative afterwards, showing you how much of your income is left over. There are two totals, one before irregular expenses and one that includes everything. The purpose here is to show how your spending would have looked if not for one-time, irregular expenses, and how much of your income was left before and after those expenditures.

• There is an Amount vs Vendor chart which shows you spending by Vendor.

• % of Expenses shows a breakdown of spending by the different categories. In the middle it shows the total spending during the period.

There is no defined period for this template

There are no date fields in this template for the purpose of keeping it simple. Whether you want to look at one week, two weeks, a month, or several months, you can plug the data into here to see how much your spending was for that period. However, if you get past row 200, you will want to copy down the checkboxes and the formula in column E.

Download and use the template

The reports in the template don’t require a refresh and so as soon as you enter your data, you can begin viewing the Summary tab.

If you’d like to use the template, you can get a copy of it here.

If you like this Expense Tracking Template, 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.

There are numerous ways to display numbers in your reports. Using percentages and decimals are two common ways to do so. In this post, I’ll cover when it might makes sense to use percentages and when to decimals may be more appropriate. I’ll also provide you with some easy-to-use formulas that will allow you to convert percent to decimal, and vice versa. You can do these calculations whether you’re in Excel or just have a calculator handy.

Converting between percent and decimal

If you want to convert numbers between percent and decimal, the process is incredibly simply in Excel. Select the value(s) you want to change and then select the format you want.

However, if you’re not using Excel, you can still accomplish this manually. To convert a percent into a decimal, all you need to do is to pretend you’re moving the % sign two spots to the left, and then convert it into a period.

In the case of 50% it becomes .50, or 0.50, depending on whether you want to display the 0 in front. This also works with large percentages, such as 1,000%. That’s a significant percent, but the same logic applies, and following the same steps would convert the value to 10. That tells you that the new value is 10 times the size of the original value.

To convert back into percent, you multiply the value by 100 and drop the % sign.

Formula for converting decimal to percent:

Formula for converting percent to decimal:

How to calculate percent of something versus percent change

One important distinction you should consider is to determine whether you’re looking at a portion of something, or a change in value. If you’ve eating half of a pizza, that’s 0.5 of it, or 50% of it. But if you’re talking about the price of something going up by 10%, that’s a slightly different calculation.

Let’s take the stock price of Microsoft as an example. At the start of 2020, its stock price opened at $158.78. By the end of 2021, it finished the year at $339.32. If we take the ending price and divide by the beginning price (339.32/158.78), then that gives us 2.14, or 214%. It would be correct to say that $339.32 is 2.14 times $158.78. But it would not be correct to say the stock price increased by that amount. That’s because you’re not calculating the actual increase in value. To do that, you need to subtract 1, to arrive at 1.14, or 114%.

If the stock didn’t increase at all, you would be left with an equation of 158.78/158.78, which would be equal to 1, or 100%. The ending value was 100% of the beginning value, but it certainly wasn’t an increase of 100%. Thus, the need to deduct 1 will give us the correct answer in that case — a 0% change. The same goes for decreases. Suppose the stock fell by 30% to $111.15. Dividing $111.15 by $158.78 would tell you the price is now 70% of the value it was at the start of the year. That’s correct, but to get the percent change, you deduct 1 from that, which tells you it declined by 30%.

The formula for percent change:

The formula to calculate the portion or relative size of something:

It’s a subtle difference but it’s an important one to note, which can prevent you from making a mistake in your calculations.

What to do if your decimals or percentages are very small

If you’re dealing with numbers that go to four or five decimal places, it may not be helpful to display them as percentages or even as decimals. For example, if you were to say the odds of getting struck by lightning in your life were 0.00654% or 0.0000654, whether you use decimal or percent isn’t going to be helpful in conveying those adds.

One thing you can do is flip those numbers around by calculating the inverse. By taking 1/0.0000654, that returns a value of approximately 15,300. Stated another way, it tells us that the odds of getting struck by lightning are 1 in 15,300. It’s a far more effective way of communicating the odds as you’re no longer dealing with miniscule percentages that can be hard to visualize.

The formula for converting from a decimal value to a rate:

As you can see from these formulas, they are fairly simple and can be incorporated into your spreadsheet and even done just on a calculator. You could create LAMBDA functions with these formulas, but they involve so few steps that the time savings may not amount to much.

If you liked this post on How to Convert Percent to Decimal, 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.

Nested IF statements aren’t always the most efficient way to structure your formulas. And they can make it difficult later on if you need to fix a formula or make a change to it. What’s worse, is if you inherit someone’s spreadsheet and try to dissect their nested IF functions. In this post, I’ll show you how you can get around using nested IF statements and the different alternatives you can use.

How nested IF statements works

To start, let’s look at how you might construct nested IF statements. Here’s a data set that has different cardholders and their related expenses:

I’m going to create a column with a series of IF statements to see how much Bob spent on home repair since the start of 2021. With a nested IF statement, I might first check if the cardholder is Bob. Then, if that’s true, check if the category is Home Repair. And then, check if the date is after Jan 1, 2021. Here’s how that would look inside of a formula:

=IF(B2="Bob",IF(D2="Home Repair",IF(A2>DATE(2021,1,1),E2,0),0),0)

You can see this starts to get pretty messy. And the IF statements could continue going on if you have even more criteria you want to fit into here. If I were to copy this formula down, I could get a total of all the values where Bob spent money on Home Repair. However, this wouldn’t be terribly efficient.

You could use a pivot table to quickly summarize the data by cardholder spending and category. But for this example, let’s assume that you need to do it within a formula and can’t rely on creating a pivot table when doing these types of calculations.

Using the AND function to group multiple criteria

An effective option in making your nested IF functions shorter is by using the AND function. It allows you to put all your conditions in one neat formula that you can embed within an IF function. Within the AND function, I can enter all these arguments:

AND(B2="Bob",D2="Home Repair",A2>DATE(2021,1,1))

You can keep on adding to conditions to the AND function for as many rules as you’d like to apply. This can make it cleaner to see all your criteria. All of the criteria within the AND function need to be met for the formula to return a TRUE value. Similarly, you can use the OR function if you want to check if any criteria are met.

The above formula can easily be embedded within the IF function as follows:

=IF(AND(B2="Bob",D2="Home Repair",A2>DATE(2021,1,1)),E2,0)

This does the same job as the nested IF formula except it’s a lot cleaner. However, the drawback here is that like with the nested IF statement, if you wanted to calculate all of the instances where Bob spent money on Home Repair, you would need an extra column and ad all the values up. That’s still not very efficient.

Using an Array function

Another option you can use for quickly tabulating these results is by using an array function. This can apply the logic to every cell and calculate the total for you. Rather than IF and AND statements, you can evaluate each argument, force a 1 or 0, and then multiply that by the amount to arrive at a total. Here’s how that formula might look:

=(B2:B100="Bob")*(D2:D100="Home Repair")*(A2:A100>DATE(2021,1,1))*(E2:E100)

This formula extends to the bottom of my data set. How it works is that each group of parentheses represents an argument. If it evaluates to TRUE (i.e. the criteria is met) then the value becomes a 1. If the criteria is not met, then it evaluates to a 0. So if all the criteria is met, the results will be 1*1*1 multiplied by the amount in column E. If any one of the conditions is not met, then the result will be a 0. This is the same method as the earlier examples.

The downside of an array is that it will automatically extend to the bottom of the data set:

This again runs into a similar limitation where your formula of using up more cells than you might want to occupy. But to get around this, you could add the SUM function before your array formula:

=SUM((B2:B110="Bob")*(D2:D110="Home Repair")*(A2:A110>DATE(2021,1,1))*(E2:E110))

Using SUMPRODUCT

Another function that can do the job is SUMPRODUCT. With this function, it can take care of all the criteria while also summing up the total in just one cell. The logic is similar to how the array formula was calculated above. The key difference here is to put that all within the SUMPRODUCT function. Here’s how it looks:

=SUMPRODUCT((B2:B110="Bob")*(D2:D110="Home Repair")*(A2:A110>DATE(2021,1,1))*(E2:E110))

This will obtain the same result as if you were using the array function. SUMPRODUCT is used for multiplying arrays but it can be made to work in this fashion as well. The key is making sure you encompass all the arguments withing parentheses (hence why I opened and closed SUMPRODUCT with not one but two parentheses.

As you can see, there are many different ways you can make your formulas more efficient in Excel without having to rely on nested IF functions.

If you like this post on How to Avoid Using Nested IF Statements 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.

Regular 2D charts in Excel can be useful if you want to compare two metrics. But if you want a third, that’s where knowing how to make 3D bubble charts can be incredibly effective. It can allow you to pile in a lot of data into just a single visual. It can take a bit longer to set up the chart just how you want to, but it can pay off in the end. Here’s how to do it.

Determining which values to plot where

In a 3D bubble chart, you have an X and Y axis, plus you can also specify the size of the bubble. The field that contains the largest variances will probably be the most appropriate one to use as the bubble size, since that will make it easier to differentiate large values from smaller ones.

In my example, I have country data that shows GDP per capita, life expectancy, and alcohol consumption per capita. I suspect the biggest variances might be in GDP per capita, and so that’s what I’m going to use as my bubble. Then for the X and Y axis, I’ll plot life expectancy against alcohol consumption. Here’s an excerpt of what my data looks like:

Be selecting in choosing which values to plot

In a 3D chart, the size of the bubbles can get large, and that means there can be limited space to work with. For that reason, it’s important not to plot dozens of different data points. In my data set, I have more than 160 countries, which is far too many to plot on a 3D chart.

One way to filter a large data set like this is by creating a separate table, one that utilizes a lookup to extract the same values. This can be a useful way to dynamically update your data. You can use the VLOOKUP function to extract the data. And rather than doing it one by one, you can use VLOOKUP to extract multiple columns through just a single formula. My filtered data set has the following countries in it:

Plotting the data onto a 3D bubble chart

The next step no involves creating the bubble chart itself. For this, go to the Insert tab and select the bubble chart from the X Y (Scatter) section. Initially, it may not look correct, but that’s fine. Remove all the series and start adding them one by one. To modify the data, right-click on the chart and click on Select Data. Click on Add to add a new series, where you will see the following places to enter data:

In my example, here’s how I will fill it out:

• Series Name is the name of the country
• X values will relate to the life expectancy
• Y values will relate to the alcohol consumption per capita.
• Series bubble size is the GDP per capita

Repeat these steps for each of the data points you want to add to your chart. Although this may be cumbersome, by using the VLOOKUP, these values can quickly be updated and changed by simply changing the lookup value (in my example, it’s the country name).

If you liked this post on How to Make 3D Bubble Charts 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.

Want to find the latest trending items on Google? You could go to trends.google.com, or you could import that data into your Excel file. In this post, I’ll show you how.

To get the latest trending topics on Google, you can go to https://trends.google.com/trends/trendingsearches/daily?geo=US. You can adjust the string at the end if you want to see trends from a different country, but the link I’m using here is for the U.S.

Use Power Query to import the data

One of the most effective ways of getting data into Excel from an external site is by using Power Query. The key is finding the right link. On the Trending Searches page in Google, there’s an RSS button that you’ll want to click on:

Then, you should see the following URL:

https://trends.google.com/trends/trendingsearches/daily/rss?geo=US

That is the URL you’ll need to use in Power Query. Back in Excel, go to the Data tab and select the From Web button. Put that link in the following box:

When the Power Query window loads up, you might be tricked into thinking there is nothing there:

Expanding the data

The data is there, it’s just contained within a table. Click on Transform Data at the bottom of the Power Query window. Then, click on the icon in the channel field that shows that there are items within there that you can expand:

Click on the OK button in the following dialog box where you can specify all the columns you can add. There are many embedded tables here and if you want to see what’s contained within them, simply click on one of them that says Table. For example, if I click on the Table shown under the channel.item field, I see all the values contained within that table:

Within here, I can see that there are still multiple tables that I can expand, and there’s also a title column that looks to contain the actual trending item name. You can continue expanding depending on the fields you want to see. You can expand everything, and then at the end select which columns you want to keep. If you do expand everything, you’ll end up with some unnecessary columns such as channel.link which contains the same repeating value. To remove a column, simply right-click it and click on Remove:

Repeat this step for all columns you want to eliminate. Alternatively, you can select the columns you want to keep, and then select the option to Remove Other Columns. Either approach will work the same. The columns that I’ve chosen to keep include the title, the number of searches, the two description columns, as well as the time of publication:

Cleaning up the table

There are a few items I can clean up to make this import nicer into Excel. The first is to make those numbers actually read as numbers. Because of the + sign at the end, I can’t convert them into whole numbers. What I will do is select the entire column, and then click on the Repace Values button under the Transform section in Power Query. Then I am effectively doing a find and replace, to swap the + signs with nothing:

Now, the format of that column can be changed to a whole number. I can do that by clicking on the ABC letters (indicating it is a text field) and selecting the option for Whole Number:

Upon doing so, you’ll notice the values align to the right, meaning they are now being read as numbers. Now they can be sorted in descending order so that the most popular items are at the top.

Another change I will make is to replace some more values in the two description fields:

• Replace ' with an apostrophe (‘)
• Replace   with a space (‘ ‘)
• Replace " with quotation marks (“)

There are also some duplicate items in the title field that I’ll remove. To do that, right-click on the field name and select Remove Duplicates. The last thing I’ll do is re-name the headers.

This is what my Power Query table looks like after all these changes:

Now I can click on Close & Load to get the data into Excel:

Now, all you need to do to load the latest trending data into Excel is just to refresh the query.

If you liked this post on How to Get Google Trends Data Into 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.

Power Query is a useful way to pull in data and make changes to it. The steps get recorded as you make them to your data set. But by doing so, you can also be hardcoding some of those steps along the way. That’s a no-no. When possible, you should make your formulas, macros, and templates as dynamic as possible. One way you can do that in Power Query is by using named ranges. In this post, I’ll show you how you to do that.

Start with creating a regular named range

In this example, I’m going to use a named range to make it easy to change the markup on products. Here is a list of some generic products and prices:

In Cell E2, I have my markup percentage. I’m going to create a named range called ‘markup’ for this cell.

Assigning that named range in Power Query

Next, I’ll create a Power Query connection by selecting any of the values above in columns A or B. Under the Data tab, selecting From Table/Range under the Get & Transform Data section will launch Power Query. From that screen, you can launch the Advanced Editor under the Query section of the Home tab. That will open up a new window where the code is stored:

There is not a whole lot there right now just because the only thing I’ve done thus far is just link to the Excel table. Above the Source line, I’m going to add my named range. I’ll call this Markup as well. The formula to reference my cell is as follows:

Markup = Excel.CurrentWorkbook(){[Name="markup"]}[Content]{0}[Column1]

As you can see the named range of ‘markup’ is referenced and it’s important it is spelled exactly the same way as the code in Power Query is case-sensitive. The above code creates a table and by referencing {0}[Column1] I’m directing it to the value in the first row, and first column. It’s an important part of the code, otherwise, you will get something that Power Query doesn’t recognize as being a single value. Add the above line, along with a comma after it in the code. Here’s how it should look:

When I close out of that and go back into the main Power Query window, there’s a new step called Markup where I’ve assigned my named range. Now all that’s left is to do something with this named range.

Create a custom column to use a named range in a calculation

On the Add Column tab in Power Query, there’s an option to create a Custom Column. If you click on that, you’ll see the following window, where you can create a formula:

You can double-click the available columns off on the right to insert them into the formula. This will be a fairly straightforward formula where I’ll take the cost and multiply it by 1 plus the Markup variable I created earlier:

Now a new column has been created:

And that’s it. Now I can hit Close & Load to get back into the worksheet, where I’ll now have a price column. Changing the markup value in the sheet and then refreshing the data will update the price column to reflect the new markup percentage.

If you liked this post on How to Use Named Ranges 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.