All posts by : howtoexcel

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.

A new feature Excel has recently rolled out is the ability to create Lambda functions. In short, this is a way to create custom functions within Excel that utilize variables. Previously, you would have needed to use VBA to accomplish this. But as long as you’re running Microsoft 365 and have the latest version of Excel, you can do that yourself. Here’s a quick overview of how it works.

Creating a simple Lambda function

Similar to how you might create named ranges, you can easily create a new Lambda function within the name manager in Excel. Click on the Formulas tab and then click on the Name Manager button:

Then, click on the New button, which will then give you the following dialog box:

I’ll set the name equal to times5 because what I want to do is take a value and multiply it by 5. In a Lambda function, I need to specify any variables, and then, what I want the function to do. I’ll use x as my variable and multiply it by 5 in this formula:

=LAMBDA(x,x*5)

I’ll plug that into the named range so that it is filled out as follows:

When I click OK, the function is created. Now I can go into Excel and reference the function by using its name:

=times5(10)

That will return a value of 50 as it will take the x value (10) and multiply it by 5.

Creating a Lambda function with multiple variables

Let’s move on to a more complex function. This time, I will calculate the compounded annual growth rate. For this, I will need a percent change and the number of years over which it will happen. Let’s say an investment grew by 200% over a period of 10 years. I want to know how much it would have averaged each year to compound to such a return. The formula to calculate compounded annual growth rate is as follows:

(1 + percent change)  ^ ( 1 / # of Years) - 1

The calculation in this case ends up being 3^(1/10)-1 which returns a value of 0.116, or 11.6% after converting the value into a percentage.

To convert this into a function, I’ll set the percent change as x and the # of years as y:

=LAMBDA(x,y,(1+x)^(1/y)-1)

The first two arguments are variables as there is no calculation taking place. But the Lambda function recognizes that what follows afterward is the actual computation that needs to happen. I’ll create this as another named range, using CAGR as the name of this function:

Now, to reference this within Excel, I’ll call the function as follows:

=cagr(2,10)

Where 2 refers to the percent change (200%) and 10 is the number of years. This returns the same value of 11.6% as when doing it manually through the calculation. The benefit of using a Lambda function is that it can easily be reused within the workbook without having to regenerate the formula over and over again.

If you liked this post on What Are Lambda Functions, 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.

Conditional formatting in Excel can help you do a lot of things. If you have a range of data, using colors can help show where the high values are and where the low ones are. It can also be used for more complex analysis, especially since you can also use formulas in conditional formatting. In this post, I’ll show you how you can use it to analyze a company’s financial data.

Use conditional formatting to find changes in values

In this spreadsheet, I have some key financial numbers from Nike’s recent quarterly results;

You may be tempted to create additional columns or rows or even another sheet to help calculate the percentage changes and differences from prior periods. But instead of doing that, you can use conditional formatting to highlight important movements. For example, I’m going to create a conditional formatting rule to highlight cells that are higher than the previous period. That will allow me to easily see values that are increasing. And at the same time, a lack of formatting would suggest that they are declining.

To set this up, I’ll select the rows I want to analyze (in this case, anything from row 3 down), and under the Conditional Formatting drop down on the Home tab, I’ll create a new rule. I’ll select the option to use a formula to determine which cells to format:

I have a place to enter my formula. Since the first value in the range I selected was cell B3 (I started from the third row, second column), everything in my formula will be relative to that starting point. If I want to see if there has been an increase in value from the previous period, I’ll use the following formula:

=B3>C3

It’s a simple formula that checks if the current value is greater than the cell next to it. And because it’s relative, when the rule is applied to cell C3, it will look at whether C3 is greater than D3. It will then adjust for all the other cells. If I apply a green background and white text formatting for this rule, then my spreadsheet now looks like this after applying the conditional formatting:

Right away, you can spot the green-highlighted cells that show values that increased from the previous period. And any that aren’t highlighted in green, we can see have been declining. For example, we can see that the company’s sales for the past three quarters aren’t highlighted in green. That tells us sales have been falling for three straight periods. In the Apparel row, before this most recent quarter, sales were increasing for three straight periods as shown by the three consecutive green-highlighted cells.

We can apply more complex highlighting than this. For instance, let’s also emphasize any values that jumped by more than 10% just to make it even more evident when the company had a strong performance. To do this, I’ll again create a new conditional formatting rule. But this time, I’ll use the following formula:

=B3>(1.1*C3)

If the current cell is more than 10% of the cell next to it, then I will highlight a darker green formatting, italicize, and bold the value. Here’s what that looks like after adding that rule:

Now, it’s easier to see large changes in values. For example, there was a significant increase in sales in Greater China in Q3, and the level of inventories also moved by more than 10% compared to the previous period. We can see both rules being applied, both the 10% increases and the non-10% increases that were positive.

Another rule I’ll also add is to strictly look at the profit margin (row 20) and to highlight any values that are less than 10%, as Nike normally generates profits that are higher than that. So this will help highlight any periods that weren’t terribly strong. My formula for this rule is as follows:

=B20<0.1

When applied, now my data looks like this:

There’s even more formatting now. And the inevitable problem with adding too many rules is that they may end up overlapping one another. For example, the 9.5% profit margin, while below 10% and highlighted in yellow, was also higher than the 8.38% profit margin in the previous period. Technically, multiple rules apply. This is where it’s important to manage your hierarchy.

How to rank the priority for conditional formatting rules

To determine which rule gets more importance, you’ll need to go to the Conditional Formatting drop down again and go to the Manage Rules section. If you don’t see all of them there, make sure you have the entire worksheet selected:

Once you see all the rules, you can use the arrow keys to move them up and down. By default, the more recent ones go to the top. But let’s suppose I want the profit margin rule to be last in terms of importance. I can move that one down so my hierarchy is now as follows:

With this hierarchy, now I only see one cell highlighted in yellow:

Since the 9.5% profit margin was a 10% increase from the previous period, the first conditional formatting rule applies and takes priority over the others.

If you liked this post on How Conditional Formatting Can Help Analyze Financial Data in Seconds, 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.