ChartwithTargetLine

How to Create a Chart With a Target Line

Are you creating a chart that shows progress, with a certain goal in mind? In this post, I’ll show you how to create a chart with a target line so that you can see how close you are progressing toward your goal.

A common example for this type of chart is where you are reporting monthly sales and have a goal you want to reach for the year. Here’s a chart that shows the monthly revenue and has a cumulative total as well:

Chart showing monthly and cumulative sales.

Creating the target line

To create a target line, I need to add another series to this chart. For example, let’s say your goal is for sales to hit $50,000 for the year. To do that, you just need to create another series. I’ll call it ‘Target’ and for each of the values, I’ll enter in $50,000:

Excel table showing monthly and cumulative sales alongside a target.

You don’t need to enter $50,000 manually into each cell. You could use the autofill to copy the values down. However, a more flexible way to do this is to enter $50,000 into the first cell, and use a formula to refer to that cell. That way, if you change your target amount, you only need to make the change in one cell.

If you’ve already created your chart and want to add the line to your chart, you’ll need to right-click on the chart and click Select Data. Then, adjust your chart range so that it includes the extra column, and then you’ll see your chart update with the line. If you are creating a chart from scratch, then you just have to select the correct range when first creating it.

Chart showing monthly and cumulative values with a target line.

One additional thing you may want to do at this stage is to adjust the formatting of the target line. A good idea can be to make it look different from the other lines on your chart. One way you can do this is by using dashes. If you click on the target line, you will see a pane show up on the right-hand side showing you options to format the data series. Click on the paint bucket icon and you’ll see various settings for the line. There is one option for the Dash type which will allow you to show the line as breaking up as opposed to being solid:

Changing the dash type for a line chart.

After also changing the color to a solid black, this is what my chart looks like with these changes:


If you like this post on How to Create a Chart With a Target Line, 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.

MultipleSort

How to Sort Data by Multiple Columns in Excel

Sorting data in Excel is relatively easy, and can be done with a click of just a button. However, it can be a bit more challenging when you’re trying to sort data by multiple columns. Once you’re familiar with the process, it’s not a whole lot more difficult. In this post, I’ll show you how you can do that.

How to sort just one field or column

In this data set, I have multiple fields that I can sort by:

Data set before applying a sort.

To sort by any field, it’s as easy as clicking on any column and clicking either the ascending button (the first button below) or the descending button (the second one shown):

Ascending and descending order buttons in Excel.

The ascending order button will sort values from A->Z, lowest to highest, or oldest date to newest date. The descending order button will do the reverse, and sort values from Z->A while amounts will go from highest to lowest. Doing this will sort one column at a time. If I sorted the data above by dates in ascending order, this is how it would look:

Data sorted by date.

This shows me the data from oldest to newest entries.

How to sort multiple columns in Excel

If I wanted to sort by date and then by store. I would need to apply multiple sorting rules. Even if I wanted them all to be in ascending order, I can’t just go and click on each column and click the ascending order button. If I did that, this is how my data would be sorted:

Data after applying multiple sorting rules.

The data isn’t sorted by date anymore. You can see that only the store names are sorted properly. This is because it’s the most recent sort that has been applied. And the last field I clicked on to sort was store, so that’s what it will be sorted by. There are a couple of ways I can fix this.

The first method is by going in reverse. Since the last column that I click in is what I’m sorting by at the top, that needs to be the first one I click on, not the last. If I click and sort (by ascending order) Store and then the Date field, this is what the data set will look like:

Data after applying multiple sorting rules.

Another way you can accomplish this is by clicking the Sort button:

Sort button in Excel.

Then, you’ll have the ability to specify your sorting rules. To accomplish the same sort as above, you would set it up as follows:

Creating sorting rules in Excel.

The advantage of this approach is you don’t have to work backwards. It can be simpler to plan out how you want to sort your data without having to worry about remembering the sorting rules in reverse. For larger, more complex sorting rules, using the Sort button is going to be easier. If, however, you only have a few fields you want to sort, it may not make a difference which method you choose.


If you liked this post on How to Sort Data by Multiple Columns 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.

H2ESecReport

How to Convert a Table From an SEC Report Into Excel

If you want to download a company’s financial statements or data, the easiest place is often straight from the source: the Securities & Exchange Commission (SEC). You can download financials in Excel format if there is an interactive option within the SEC filing, but that won’t give you all the tables contained in an earnings report. In this example, I’m going to use Adobe’s most recent earnings report to show you how to get a table into Excel

Downloading the data

Adobe’s earnings report is found here, with the following financials on page 4:

Adobe's income statement for the quarter ending March 4.

Copying it into Excel

Copy the table and then go to paste it data into Excel. But when you right-click in Excel, make sure to select the option to paste it so it matches the formatting on the sheet, as shown below:

Paste with a format matching the spreadsheet.

Now, the data pastes without any of the colors and formatting onto my Input sheet:

Adobe's earnings report downloaded into Excel.

If when you paste it doesn’t show up like this and it looks like just a few lines, re-try copying the data. It may help not to include the header that says “three months ended” and simply start copying from the first line item (“revenue” in the above example”) to ensure that Excel picks it up as a table.

Formatting the data

It looks pretty good except that I have many extra columns. And numbers that have dollar signs have been pushed out by one column. What I will do here is create a template in a separate sheet that will automatically pull in what is needed. The new tab, called Output, will be where I create my formulas. My assumption is that the spacing will be consistent and that the current period values are in columns D and E, and the ones from the prior-year period are in columns J and K.

Starting in cell A1, I’ll create a simple formula that checks if the same value on the other sheet is blank. If it isn’t, then it will pull in the value, otherwise, it will remain blank:

=IF(Input!A1="","",Input!A1)

I will do the same thing for column B, except this time I am looking at values from the Input tab in column D. And I will need to adjust for if there is a $ sign. If there is, I need to pull the value from column E instead. Here’s what that formula looks like:

=IF(Input!D1="","",IF(Input!D1="$",Input!E1,Input!D1))

That gets me a bit closer to where I want to get to:

Financial figures pulled into a separate tab with formatting applied.

There are still a couple of issues. The first is that on row 30, there is a symbol that isn’t a dash that I need to remove. This is character code #151. And there’s also a trailing blank space behind the numbers that needs to be removed. This isn’t your ordinary blank space and it is character code #160. I need a couple of SUBSTITUTE functions to remove those character codes:

=IF(Input!D1="","",SUBSTITUTE(SUBSTITUTE(IF(Input!D1="$",Input!E1,Input!D1),CHAR(160),""),CHAR(151),0))

For character 151, I want to replace this with a 0 value since that’s what the symbol is in place of. Next, I need to convert these values to numbers. I can do this by multiplying them by a factor of 1. I’m going to use the IFERROR function as well so that in case it’s text, it will return the original value in column D. Here’s my completed formula:

=IF(Input!D1="","",IFERROR(1*SUBSTITUTE(SUBSTITUTE(IF(Input!D1="$",Input!E1,Input!D1),CHAR(160),""),CHAR(151),0),Input!D1))

Now, I can repeat this formula in the adjacent column. Except this time instead of referencing D and E, I’ll refer to columns J and K. Now, my output tab looks as follows, after applying some formatting to it:

Financial statement formatted in Excel.

This can be re-used over for other tables in an SEC report, as they generally follow the same pattern. For example, this is Adobe’s table showing sales by segment:

Adobe's sales broken out by segment.

By dropping this into my Input tab, this is what my Output now shows:

Output tab in Excel showing Adobe's segmented financial information.

All that I needed to do was to copy the formulas and just adjust the columns they referenced on the Input tab. If you’d like to use the file I’ve created for your own use, you can download it for free, from here.


If you liked this post on How to Convert a Table From an SEC Report 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.

H2Eforecastrange

How to Make a Forecast Chart Showing a Range of Possible Values

In a previous post, I showed how to make a forecast chart in Excel with a dotted line. This time around, I’m going to go one step further and show you how to create a chart that shows a range of possible values. This is useful in the event that you want to show some flexibility in your forecast and where providing a range might be a more realistic option.

For this example, I’m going to project a company’s future dividend payment. Below, I have a a record of the past dividend payments along with the annual rate of increase:

Historical dividend payments along with their annual growth rates.

In order to create a range, I’m going to set both a high rate of growth and a low one. Since the company has made 10% increases in the past, I’m going to use that as the high. And for the low rate of growth, that will be 5%. Using those different rates, I can set up additional columns for what the dividend would be if the low rate were used and if the high one were applied. I will also create a column to calculate the difference between the high and low amounts, as well as one for a base amount — which will just be equal to the low amount. This will be used for stacking the difference on top of it to create the desired area chart:

Historical and projected dividend rates.

Creating the chart

Now that the data is set up, I’m going to start creating the chart. Using a combination approach, I’ll set a line chart for the actual, low, and high columns. And for the base and difference amounts, I will set those to be stacked area charts. The growth rate I’ll leave as is because I will remove that once the chart is created:

Using a combo chart for line and stacked area charts.

Next, I’ll right-click on the chart and click on Select Data. From the next screen, I will untick the box for the Growth Rate:

Removing a series from an Excel chart.

Then, I will right-click on the x-axis, select Format Axis and select the option to put Categories in reverse order. Now my chart looks as follows:

Forecast chart showing line and stacked area charts.

Now, I’ll remove the legend and format the base color, which is currently grey, to a blank fill color:

Forecast chart showing line and stacked area charts.

I’ll change the line color for the high amount to green, the low amount to red, and apply dashed lines to both. For the actuals, I’ll set that to a black line. And for the area chart that is in green right now, I will apply a Pattern Fill and use a checkered pattern:

Using a checkered pattern fill for a stacked area chart.

With all those changes, my updated forecast chart now looks like this:

Finished forecasted line chart showing a pattern fill area for the range of possible values.

If you liked this post on How to Make a Forecast Chart in Excel With a Dotted Line, 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.

H2EForecastChart

How to Make a Forecast Chart in Excel With a Dotted Line

Charts are an effective tool in forecasting. In this post, I’ll show you can show you can make an actual and forecast chart in Excel look like one continuous line chart, with the forecasted numbers being shown on a dotted line.

For this example, I’m going to use Amazon’s recent quarterly sales as my starting point:

Amazon's quarterly sales.

I’m going to create another column for forecasted amounts for future quarters. I’ll make a simple forecast and assume that sales will increase by 10% every quarter:

Amazon's quarterly sales alongside a forecast.

For the last quarter (2021-09), I’m including the same total in the Forecast column. This is to ensure that the new line chart picks up where the last one ends and that there isn’t a gap. Then, I’ll create a line chart for these data points, which, by default, looks like this:

Two line charts showing actual and forecasted amounts.

I’m going to flip this chart in reverse order so that the forecasted values are on the right. To do this, right-click on the x-axis and select Format Axis. Then, check off the box that says Categories in reverse order:

Categories in reverse order setting on Excel.

Now, at least my chart is going in the right direction (an alternative could be to structure your data in the opposite direction):

Two line charts showing actual and forecasted amounts with categories reversed.

Because of the change in colors, this makes it easy to differentiate my actuals from my forecast. But I want it to be all the same color and only be differentiated by dotted lines. To do this, I will right-click on the forecasted line and select Format Data Series:

Formatting the data series on a line chart.

There will be an option to change the Dash type. The default is solid, and I’m going to change that to the second option from the top — Square Dot. After changing that and making the colors the same, and applying some formatting, here’s what my chart looks like:

Line chart showing Amazon quarterly sales with forecasted amounts as dashes.

If you liked this post on How to Make a Forecast Chart in Excel With a Dotted Line, 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.

stockdashboard

Creating a Stock Market Dashboard in Excel

Want to create a dashboard to track the stock market and the latest business-related news? Below, I’ll show you how you can create a stock market dashboard using Excel and Google Sheets to pull in all the data you’ll need. If you’d prefer to just download the file, you can do so here.

Step 1: Compiling the data

You can get stock prices into Excel using the STOCKHISTORY function. However, that isn’t available on older versions of Excel and it also doesn’t pull in the current day’s prices. Using Google Sheets can be more effective for this purpose. Plus, on there, I can pull in business-related news as well.

To start, I’m going to pull in values for the Dow Jones, Nasdaq, and S&P 500. I’ll also download the values of a couple of exchange-traded funds (ETFs) that track healthcare and tech stocks. To get the latest price, you can use the built-in GOOGLEFINANCE function that’s only available on Google Sheets. To get the latest value of the Dow Jones, the following formula will work:

=GOOGLEFINANCE(“.DJI”,”price”)

And to calculate the percentage change:

=GOOGLEFINANCE(“.DJI”,”changepct”)/100

For the Nasdaq, you’ll use “.IXIC” and for the S&P 500 the ticker is “.INX”

For the ETFs, since they aren’t indexes, there is no period beforehand and I reference XLK for tech and XLV for healthcare. In my Google Sheets file, I have a simple layout for the values and their changes that I will later pull into Power Query:

Stock market indicators in Google Sheets.

Next, I’ll also download the latest business-related news. Google Sheets has another unique function for this: IMPORTFEED. All you need to do is find an rss feed from a website that you want to pull information from. Not every website has an rss feed but what you can do is just do a Google search for the name of a source and ‘rss’ to see if you can find a link. There are three sources I’m going to use for this dashboard:

CNBC: https://www.cnbc.com/id/10001147/device/rss/rss.html

WSJ: https://feeds.a.dj.com/rss/RSSMarketsMain.xml

NYT: https://feeds.a.dj.com/rss/RSSMarketsMain.xml

I will pull them all in the same way, using the IMPORTFEED function. Here’s an example with the CNBC feed:

=IMPORTFEED(“https://www.cnbc.com/id/10001147/device/rss/rss.html”,”items”,true,10)

In Google Sheets, the top articles from each of those rss feeds will show up, including the title, URL, date created, and even a brief summary:

News articles pulled into Google Sheets using the IMPORTFEED function.

Now, it’s time to pull all this data into Excel.

Step 2: Loading the data into Excel using Power Query

To import data from Google Sheets into Excel, you need to first share the sheet. While in Google Sheets, go into File -> Share -> Publish to web. Then, you’ll be prompted to select what you want to share. I’ll start with the Markets tab I created and then the News tab:

Publishing data to the web from Google Sheets.

Copy this URL as you’ll need it to load the data into Power Query. While you’re back in Excel, go under the Data tab and click on the From Web button under the Get & Transform Data section. You’ll be prompted to enter a URL. This is where you’ll paste the link that you copied from Google Sheets:

Creating a query in Excel using the from web option.

On the next page, select Table 0 as where you want to extract data from. And if you want to do some cleanup (getting rid of extra columns), you can do so by clicking on the Transform Data button:

Selecting a table for Power Query to pull data from.

To remove any unneeded columns in Power Query, just right-click on a column header and click Remove:

Removing a column from Power Query.

Once you’re done, click on the button to Close & Load if you want the data to be loaded on a new sheet. If you want to control where it gets pasted, then use the drop down and select Close & Load To.

Repeat these steps for the other Google Sheets tab.

In addition, I’m also going to load data from a few other sources:

Top 100 Gainers on Yahoo Finance: https://finance.yahoo.com/gainers/?offset=0&count=100

Top 100 Losers on Yahoo Finance: https://finance.yahoo.com/losers?offset=0&count=100

Upcoming IPOs from IPOScoop: https://www.iposcoop.com/ipo-calendar/

The process for importing these links into the dashboard is the same as for Google Sheets. Go through Power Query, import from web, and paste in the URL plus make any formatting changes necessary. The next step involves putting all this data together in a dashboard.

Step 3: Creating the dashboard

In my spreadsheet, I’ve created two tabs: one that hold all my Power Query downloads (the ‘Data’ tab) and a ‘Dashboard’ tab for where all the information will be displayed.

To make the set up of the dashboard easy to manage, I’m going to change the column width to 10 for everything. To do that, press CTRL+A to select all the cells on the Dashboard tab, then right-click on any of the headers, and there you’ll be able to select column width.

First up, I’m going to get the indexes and market indicators as a starting point. To do this, all I need to do is link to the values and the percentages for the S&P 500, Dow Jones, Nasdaq, Tech, and Healthcare tickers I imported from Google Sheets. By default, I’ll set the formatting for all the cells to be green:

Market indicators imported into Excel from Google Sheets.

To make this more dynamic, I will add some conditional formatting so that if the percentage change is negative, the corresponding cells will highlight in red. For this, I can select all the cells in green above and create a conditional formatting rule the starts with where the first percentage is (in my spreadsheet, it is cell E6):

=E$6<0

This is a simple rule but by not freezing the column (E) and freezing only the row (6), it can be applied to all the cells above. I can apply a red background color so that if any of the percentages are negative, the cells will highlight accordingly:

Market indicators imported into Excel from Google Sheets with negative values showing up in red.

For the next part of the dashboard, I will copy over the news stories that were also downloaded from Google Sheets. This time, I’m going to use the HYPERLINK function so that I can not just link to the title but also create a clickable link that will allow me to open the story should I want to open it in my default browser. The function itself is simple and involves just two arguments, one for the actual URL and another for what the text should show up. Since it’s shorter, I’m going with the title. After applying some formatting and copying all three sources, this is what my dashboard looks like:

Stock dashboard showing stock market indicators and the latest business news.

For the last part of the dashboard, I’m going to pull in the tables from the other data sources (top 100 gainers, losers, and upcoming IPOs). If these are on the Data tab, you can just cut and paste them onto the Dashboard tab. And for each one of the tables, I’m going to create a chart based on the symbol and the percent change.

To do this, select the Symbol column and the % Change columns. Then under the Insert tab in Excel, open up the charts and select Treemap. If you selected too many columns or didn’t specify which ones you wanted, you might get a different look. But if you only selected those two, you should see something like this:

Treemap chart in Excel.

Since the chart includes the symbols, the legend can be deleted. Also, I’m going to change the color scheme so that it goes from dark green to light green. This change can be made by clicking the Change Colors button next to the chart:

Changing the color scheme of a treemap chart.

To add the percentage to each of the boxes, right-click on one of the ticker symbols and click Format Labels. Then, check off the box for value so that the percentages will also show up next to the symbols:

Treemap chart in Excel showing ticker symbols and percent changes.

These steps can be repeated for the other charts. However, for the losers table, since the percentage change is negative, it needs to be flipped to positive first. To do that, that query needs to be edited. If you click on Queries & Connections section under the Data tab, you’ll see a list of all your queries. Click on the one that takes you to the top losers query. Right-click edit and Power Query will open up.

Once in Power Query, select the % Change column and under the Transform column at the top, click on the Standard drop down, which will show you all the different calculations you can apply:

Power Query menu showing standard calculation operators.

Click on Multiply and then for the value in the next box, enter -1. Pressing OK will then flip all the values to negatives.

Multiplying values in Power Query.

Now, you can create the same Treemap chart for this table. For the IPOScoop download, the field I’m going to use is Est. $ Volume. This query will also need to be edited in order to use that field since it is text. Although it is a bit more complex since this field contains text and dollar signs, there’s a relatively easy way to parse out what you need.

In Power Query, select the column, and under the Add Column tab, click on the Column From Examples button (choose the option for From Selection):

Column from Examples button in Power Query.

That will create a new column:

Power Query editor after adding a new column from examples.

In Column1, I can enter the value that I want Power Query to extract. If I just enter a few values to show what I want (in this case, I only need to enter 300), Power Query fills in the rest, figuring out what I am trying to do. It’s an easy way to parse data in Power Query.

The Power Query column from examples filling in the rest of my values.

After creating the new column, I can change the format from text to currency by clicking on the ‘abc’ letters in the title:

Changing a column's format in Power Query.

Now that I have the column created, I can remove the original one and load the data back into Excel and proceed with making a Treemap for this chart using the symbol and the newly created column.

The last thing I’m going to do is create a new column to show the change in volume to determine how much more (or less) trading there was for each stock on the day compared to the average. This will compare the average three-month volume with the current day’s volume. The one complication is that some of the values contain letters:

Stock trading volumes showing letters and numbers.

To convert these values, it’s important to first parse out the letters. If a value doesn’t contain a letter, then it is in thousands. I’m going to set everything to millions. So if the value doesn’t contain a letter, it will be multiplied by 0.000001 to convert it into a fraction of a million. And if it contains a ‘B’, it will multiply by a factor of 1,000. Otherwise, the value will remain as is. Here’s how the first part of the formula will look like, which involves determining the multiplication factor:

IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))

Since the letter is always at the end of the string, just using the RIGHT function (which looks at the right-most string) will suffice. This result needs to be multiplied by the remaining value. That value can be extracted by using the SUBSTITUTE function which will replace one value with another:

SUBSTITUTE([@Volume],”B”,””)

In the above formula, the value of B will be replaced with an empty string. This is the same as simply removing the value. To ensure that any ‘M’s are also removed, I will embed this formula within another one that will substitute out those values:

SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)

I multiply this by the first part of the formula, and my numerator is as follows:

(IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))*SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)

For the denominator, I’m going to use the exact same formula, except instead of the current volume, I’m going to use the field for the three-month average:

IF(RIGHT([@[Avg Vol (3 month)]])=”B”,1000,IF(RIGHT([@[Avg Vol (3 month)]])=”M”,1,0.000001))*SUBSTITUTE(SUBSTITUTE([@[Avg Vol (3 month)]],”B”,””),”M”,””))

The last part involves putting all this together:

=(IF(RIGHT([@Volume])=”B”,1000,IF(RIGHT([@Volume])=”M”,1,0.000001))SUBSTITUTE(SUBSTITUTE([@Volume],”B”,””),”M”,””)/(IF(RIGHT([@[Avg Vol (3 month)]])=”B”,1000,IF(RIGHT([@[Avg Vol (3 month)]])=”M”,1,0.000001))SUBSTITUTE(SUBSTITUTE([@[Avg Vol (3 month)]],”B”,””),”M”,””)))-1

The -1 at the end is to put the change in a percentage of less than 100%.

Another step you might consider at this point to help identify these changes is to format these numbers so they are easier to read. You can use conditional formatting (color scales) to easily highlight the highs and lows. And if you want to format the percentages so that they show commas and negative percentages show up red, use the following in the custom number format:

#,##0%;[Red]#,##0%

The semi-colon before the [Red] separates out what the percentages should look like when they are positive (the part before the semi-colon) and what they should like when negative (the part that comes afterward). The [Red] text indicates the value should be in red text.

Here’s how this section looks as part of my dashboard:

Stock market dashboard showing top and bottom gainers.

And here’s a snapshot of the dashboard as a whole.

Stock market dashboard in Excel.

One thing to remember: if you want to update the queries and the dashboard, make sure you go under the Data tab and click the Refresh All button. Otherwise, your data may not be up to date.

Also, to prevent your tables from stretching out when updating the queries, select each one of them and under the Table Design tab, click the Properties button (under the External Table Data section), where you should see this:

External data properties in Excel.

Make sure the Adjust column width checkbox is unticked. This will prevent your columns from stretching out and disrupting your layout.


If you liked this post on Creating a Stock Market Dashboard 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.

H2Echecklist

How to Make a Checklist in Excel

There are many different apps to choose from if you want to create a checklist. But if you’re doing Excel work and have tasks associated with it, it may be easier to just include the checklist right within your spreadsheet. In this post, I’ll show you how you can make a checklist in Excel quickly and easily that you can re-use in many spreadsheets.

Step 1: Creating your list

Excel is an easy place to create a list since a spreadsheet is already in a grid format. You can use either numbers or letters as prefixes, or without anything at all:

List of tasks in Excel.

Step 2: Add checkboxes

In order for this to look like a task list, we should add some checkboxes. If you don’t have the Developer tab enabled in Excel, make sure to do so. Under Excel Options, you’ll have an option to customize the Ribbon. This is where you can select which tabs you want to have enabled:

Enabling the developer tab in Excel.

Once enabled, go to the Developer tab and click on the Insert button. Select the checkbox icon that is under the Form Controls section:

The Insert menu under the Developer tab.

Then, use the mouse to drag and create a checkbox. It will automatically create some generic text to say ‘Check Box 1’ — you can remove this as it is unnecessary. Once you’ve got the checkbox in the position you want (and within its own cell), copy the entire cell and paste it over so that you have a checkbox next to each task:

Check boxes next to all the different task items.

Each checkbox can be linked to a specific cell. And every time you click the checkbox, the value of that cell will toggle between TRUE and FALSE, to indicate if the box is ticked or not. To create a link, right-click on a checkbox and select Format Control. Then, under the Control section, select a cell in the Cell Link section:

The control section in the Format Control settings.

Then, when the checkbox is ticked or unticked, here’s how the values in the will appear in the linked cell:

Two check boxes, one ticked and one unticked.

The danger with copying these checkboxes after you have linked a cell, is that those cell links won’t change; multiple checkboxes will be linked to the same cell:

Multiple check boxes linked to one cell.

To correct this, you will need to modify the cell link for each checkbox. Once that’s done, it’s time to move on to the last step.

Step 3: Add conditional formatting

Right now, ticking the checkbox doesn’t do anything but show a TRUE or FALSE value. In this step, I’m going to add some conditional formatting to also cross out the item. To do this, I’m going to highlight the column that has the tasks (column B) and create some conditional formatting rules:

Creating a new conditional formatting rule.

I’m going to create a rule that looks at the column that contains the cell link values (column C). It will check if the value is set to TRUE using the following formula:

=C1=TRUE

Applying conditional formatting using a formula.

Then, under the Format options, I will apply a strikethrough effect:

A strikethrough effect applied under the font settings.

Now, when a checkbox is ticked, the text will have a strikethrough effect:

Task list with completed tasks showing a strikethrough effect.

The TRUE/FALSE values can be hidden since they don’t need to be visible in order for the checkboxes and strikethrough effects to work. The only other changes you may want to make at this point relate to formatting. This includes applying a header. Here’s what your finished checklist might look like with some additional formatting:

Checklist created with tasks, checkboxes, and strikethrough effects.

If you liked this post on How to Make a Checklist 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.

H2Ezerochart

How to Hide Zero Values on an Excel Chart

If you have values that you are plotting on a chart and some of them are zeroes, you will likely notice your chart sliding all the way down to the bottom. It can be problematic when you are entering in year-to-date data which will inevitably lead to blank or zero values. That’s why in this post, I’ll show you how to hide zero values from showing up on a chart in Excel.

Let’s start with the following example:

Line chart showing zero values.

In this case, we have values for just a few months. From April through to December, the values aren’t necessarily zero — we just don’t have data yet. But the problem is that on the chart, the line graph shows them as being zeroes. If we were to get rid of those zero values, it would fix the issue:

But the problem is that this may not be a convenient solution. If you want to create formulas to calculate the totals for each month, going back and deleting the ones with no values and remembering to put the formulas back in for future months isn’t going to be a very convenient option. There is a way that the calculations can be adjusted so that you can still get the zero values not to show.

Let’s suppose you have a SUMIF calculation for each month which looks as follows:

Excel sheet showing a SUMIF calculation by month.

One way to fix this issue is to add an IF statement to avoid the zero values. But returning a blank value won’t fix the issue. Instead, what we’ll want to do is return an #N/A value. To do that, you just need to use the following formula:

=NA()

That just needs to be incorporated into the formula to say that if the sum is equal to 0, an NA value is returned:

=IF(SUMIF(E:E,A3,F:F)=0,NA(),SUMIF(E:E,A3,F:F))

Chart hiding blank values but #N/A values still showing.

Although this solves the problem, it creates a bit of an eyesore with the #N/A values showing up in our data set. If you want to get rid of that, there’s a solution for that as well. Using conditional formatting, we can adjust the values so that any #N/A values show up blank. To do this, I’ll select column B and under the Conditional Formatting in the Home tab, select the option for a New Rule:

Creating a new rule for conditional formatting.

Select the option to Use a formula to determine which cells to format and enter the following:

=ISNA(B1)

Creating a conditional formatting rule using a formula.

I need to use B1 since that is the start of the range that I have selected. Next, you can just click on the Format button and set the font color to white so that the #N/A values don’t show up. This is what my sheet and chart looks like after applying the formatting:

Excel chart with the zero values hidden.

If you liked this post on How to Hide Zero Values on an Excel Chart, 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.

H2Eunpivot

How to Unpivot Data in Excel

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:

Data in a summarized, table format.

into this:

Data that has been unpivoted.

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:

Table showing in 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.

dashboardsgs

How to Make Dashboards in Google Sheets

A big advantage of using Google Sheets is that the data is readily accessible online and you don’t need to worry about if people are running different versions of it like you would with Excel. One of the areas where it may be lacking is in creating dashboards. Although you can incorporate slicers, they’re not as user-friendly or nice looking as what you would get in Excel. But in this post, I’ll go over how to make dashboards in Google Sheets quickly and easily.

Here is a sample of what my data set looks like. If you want to view the data plus the dashboard I created here, you can check out the Google Sheets file here.

Google Sheets data set.

Step one is to create some pivot tables. Like with Excel, I prefer to create a pivot table for each view that I want. I will set up four pivot tables, categorizing sales by:

  • Store
  • Salesperson
  • Product
  • Date

To keep things simple, you can put each one of those fields in the ROW section while the sales can be in the VALUES section:

Pivot table editor in Google Sheets.

When creating the pivot tables, be sure to un-check the option to Show totals (this is so that they don’t show up in the charts):

Show totals option in Google Sheets for pivot tables.

What you may want to do is create one pivot table and then copy and paste others, and just change the rows. One additional step you will need to do for the pivot table that contains the dates is to also group them by month. To do that, right-click on any of the dates and select Create Pivot Date Group:

Creating a pivot date group in Google Sheets.

Then, from the following menu, select Year-Month:

Different pivot date groups in Google Sheets.

This is how your pivot tables might look like once you are done:

Set of pivot tables in Google Sheets.

Where you put these pivot tables isn’t important. The key is leaving enough space between them so that they don’t potentially overlap should your data get bigger. Otherwise, you will run into errors and have difficulty updating your data. Since my pivot tables won’t get any wider based on the selections I’ll make, there doesn’t need to be any extra columns between any of them.

Now that the pivot tables are set up, the next step is to set up the different charts for each of them. For the sales by store, I will create a pie chart to show the split among the stores:

Pie chart showing sales by store in Google Sheets.

The one thing you will want to pay attention to for each chart is the range. Since your pivot table could expand, it’s a good idea to make the range bigger than it needs to be, even if it will contain blank values. For example, changing this:

Default date range for chart in Google Sheets.

To this:

Expanded data range for chart in Google Sheets.

This will ensure that additional data gets picked up by the chart should your pivot table get bigger. This is also why it is important to ensure you don’t place any other pivot tables below one another. Ideally, you’ll want to keep them side by side rather one on top of the other.

For the pivot table that shows sales by salesperson, I’ll use a bar chart since the names can be long:

Bar chart showing sales by salesperson.

For the product sales, I’ll mix it up and have those as column charts:

Column chart showing sales by product.

And for the sales by date, I will set those up as a line chart:

Line chart showing sales by month.

I will also add a scorecard chart, using any of the pivot tables. For this, I just want to pull the total sales:

Scorecard chart showing total sales.

Now that these charts all set up, it’s just a matter of organizing how you want to see them on your worksheet:

The one thing missing to make this dynamic: slicers. To add slicers to all these pivot tables, click on any of them and click on the Data tab and select the Add a Slicer button:

Adding a slicer to a Google Sheets pivot table.

Then, select the columns you want to filter by:

Selecting the column to use in a slicer.

As long as you are referencing the correct data range, then the slicer will apply to all the pivot tables correctly. And now, if I add a slicer for the stores and only select stores A and B, my dashboard updates as follows:

Dashboard filtered by slicers.

One thing to remember when you are applying changes: don’t forget to click on the green OK button on the bottom, otherwise your selections won’t be applied:

Applying filters for slicers in Google Sheets.

If you liked this post on How to Make Dashboards in Google Sheets, 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.