Do you want a quick way to clean up your pivot table and remove blanks and errors from it? Below, I’ll show you how to do that with just a few steps. In the below pivot table, I have error values and blank row values, which indicate that data is missing:
Ideally, we would adjust our data set to ensure that this data is cleaned and there are no errors. But if you need to quickly clean this up, here’s what you can do.
How to remove error values from a pivot table
To prevent error values from showing on your pivot table, follow these steps:
1. Select your pivot table.
2. On the PivotTable Analyze tab, click on Options
3. Under the Format section, check off For error values show
4. If you want something else to show in place of an error value, enter it in that field. Otherwise, leave it blank and then press OK.
Now your pivot table will not show any error values on it:
There’s still the issue of the (blank) value in the row labels. Let’s address that issue next.
How to remove (blank) row labels from a pivot table
Follow these steps to get rid of the ‘(blank)’ row values which appear in your pivot table:
1. Select the drop-down filter button on your pivot table.
2. Select Label Filters and Does Not Equal
3. Set the criteria so that it does not equal(blank)
This will now remove the blanks from your pivot table:
Now both the blanks and error values are gone from your pivot table.
If you like this post on How to Hide Blanks and Error Values on a Pivot Table, 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.
The Price/Earnings to Growth (PEG) Ratio is a metric that enhances the traditional price-to-earnings (P/E) ratio by incorporating the company’s earnings growth rate into the calculation. This ratio is calculated by dividing the P/E ratio by the annual earnings per share (EPS) growth rate.
This calculation provides a more nuanced view of a stock’s valuation by factoring in future earnings growth, offering a more comprehensive perspective compared to the P/E ratio alone, which only considers the current price relative to earnings.
Why Investors Find the PEG Ratio Useful
Investors use the PEG ratio for several reasons. It allows for a more balanced comparison between companies with differing growth rates. A high P/E ratio might suggest a stock is overvalued, but when accounting for strong anticipated growth (as the PEG ratio does), the stock might actually be undervalued. This makes the PEG ratio a favored tool for identifying stocks that might offer a better return on investment, particularly when looking for good growth stocks.
The PEG ratio also aids in evaluating the potential overvaluation or undervaluation of a stock in relation to its growth prospects. A PEG ratio below 1 is often interpreted as a stock being undervalued given its earnings growth, whereas a ratio above 1 might indicate overvaluation. This simple benchmark can guide investors in making more informed decisions.
What is the Formula to Calculate the PEG Ratio?
The PEG ratio includes two components: the stock’s P/E ratio and the annual EPS growth rate. This is what the formula looks like:
Creating a template in Excel to calculate the PEG Ratio
Calculating the PEG ratio in Excel is straightforward, allowing investors to efficiently assess multiple stocks’ growth prospects against their valuations. Here’s a step-by-step guide to setup a worksheet to help you do this:
Input Data: Begin by entering the necessary data into Excel. You’ll need the current stock price, EPS, and the annual EPS growth rate. Ideally, you’ll want to setup the inputs first, followed by the formulas at the bottom. This will make it easier to enter the data in logical steps: first the ticker, the stock price, the EPS, and then the annual EPS growth.
For the annual EPS growth rate, you can pull this from a site such as Yahoo Finance (it’s under the ‘Analysis’ section). The percentage used is based on the next 5 years. Although it is percentage, enter it as a number (i.e. 100% would be 100).
Calculate P/E Ratio: In the first calculation cell, I’ll calculate the P/E ratio by dividing the stock price by the EPS.
Calculate PEG Ratio: The next calculation cell is the PEG ratio. This is calculated by taking the P/E ratio and dividing it by the annual EPS growth rate. If a stock is expected to grow at a 50% growth rate, the value should be 50, not 0.5 (i.e. don’t enter it as a percentage). Otherwise, this won’t calculate correctly.
Conditional Formatting. This is an optional step, but one which can help with your analysis. Use conditional formatting rules to highlight the PEG ratio based on its value. If it is less than 1, I’ll apply a green highlighting, a red highlight if it is more than 3, and yellow for anything in-between. Here is how you might set that up with an icon set
Here is how the template looks based on their stock prices and data as of Feb. 1, 2024:
In the above example, we have a fast-growing stock in NVDA, a moderate-growing stock in AAPL, and a slower-growing one in KO. Essentially what we are doing here is looking if the EPS growth rate is higher than the P/E ratio. If it is, that suggests it is not an expensive buy. NVDA, for example, is expected to more than double each year for the next five years, as is evident by its 102% EPS growth rate. While that would make it look like a cheap buy, you’re also assuming that it really can achieve that kind of a growth rate, which would be no easy feat. That leads us to an important part section: the limitations of this calculation.
Limitations of the PEG Ratio
While the PEG ratio offers valuable insights into a stock’s potential value by incorporating growth into the valuation equation, it’s important to recognize its limitations. Understanding these constraints can help investors use the PEG ratio more effectively alongside other analysis tools.
Growth Rate Estimations: The PEG ratio is heavily dependent on the accuracy of the earnings growth rate projections. These forecasts can be highly speculative and vary widely among analysts. Overly optimistic or pessimistic growth estimates can skew the PEG ratio, leading to potentially misleading conclusions about a stock’s valuation.
Historical Growth vs. Future Potential: The PEG ratio typically uses historical data to predict future growth, but past performance is not always a reliable indicator of future results. Companies in rapidly changing industries or facing new competitors may not sustain their previous growth rates.
One-Size-Fits-All Approach: The simplicity of the PEG ratio, while a strength, can also be a drawback. It does not account for the nuances of different industries or the specific risks and opportunities facing individual companies. A low PEG ratio does not guarantee success, nor does a high PEG ratio always indicate a bad investment.
Dividend Exclusion: The PEG ratio does not consider dividend payments. For income-focused investors, a company’s dividend yield and the stability of its dividend payments can be as important as growth. Companies with high dividend yields might be undervalued by the PEG ratio, which only focuses on earnings growth.
Market Conditions: The effectiveness of the PEG ratio can also be influenced by the overall market conditions. During bull markets, growth stocks tend to perform well, and their high PEG ratios may be justified by the market’s momentum. Conversely, in bear markets, value stocks with lower PEG ratios might be more favorable, regardless of growth projections.
Quantitative Focus: The PEG ratio is a purely quantitative tool and does not take qualitative factors into account. Elements such as management quality, brand strength, market position, and industry trends can significantly impact a company’s future performance but are not reflected in the PEG ratio.
If you liked this post on How to Calculate the PEG Ratio 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.
A good way to gauge the strength of the U.S. economy and how well it is returning to normal level is by looking at Las Vegas’ visitor data. The Las Vegas Convention and Visitors Authority (LVCVA) has plenty of important metrics that it tracks on its website. From the number of visitors to the city to occupancy levels to daily room rates, and other key performance indicators (KPIs). Using data from that website, which you can find here, I’ll guide you through the step-by-stop process as to how you can build a dashboard to track some of those key metrics.
Step 1. Preparing and consolidating the data
One of the most important parts of data analysis is to clean up your data from the beginning. By doing this, you’ll avoid headaches later on. It’ll also make it easier for you to do analysis in the first place. To get a proper glimpse of how Las Vegas is doing now, it’ll be useful to track multiple years. On the LVCVA website, you can download data for multiple years. For this example, I’m going to download data from 2019 through to 2023 YTD.
This is what one of the files looks like:
As of writing this article, data for 2023 is available up until the end of July. Since the data is organized in the same format on all of the files I’m downloading, I can just copy and paste one year after another. The key is for the rows to line up.
But I still need to clean up this data. One problem is that there are gaps between the months. Once I’ve loaded all the years together, I’ll remove those blank columns. The easiest way to do this is the highlight the top row. Then, press F5 and select Special. There will be an option to select Blanks:
Then, all those gaps are selected:
If your right-click on any one of them, select Delete, choose Entire Column, and press OK. Now those columns are gone:
There’s still one problem here. The way the data is structured right now isn’t useful when creating pivot tables. And if you’re creating a dashboard, you’ll want to be able to create pivot tables easily. Doing so can make it easy to create reports on the fly and easy to make changes. It’s easier to have dates going vertically than horizontally to scroll through data. So what I will do is use the TRANSPOSE function to flip it. All that’s necessary here is to use the function and select your entire data set. Then, voila:
Before I make any further changes, I want to convert this into values. Since I used the TRANSPOSE function, it’s sitting as an array. To change this, I’ll select the entire data set, press CTRL+C, and then press CTRL+SHIFT+V to paste as values. If you don’t have that functionality on your version of Microsoft Excel, right-click and select Paste Special and click on Values.
I will also add a few more columns to make the analysis easier. I’ll create a column for the month and year. This will involve using the MONTH and YEAR functions. The only argument that is needed is the original date, which in the screenshot above, appears under ‘Tourism Indicators.’
And since I want to compare 2023 to 2019, I’ll add a column for ‘Current Period’ and ‘Comparable Period.’ The point of this is to make sure that I can filter the current YTD values against the same values from 2019. Since I have data up until July, any comparisons should also run up until July 2019. For the Current Period, I’m using the MAXIFS function to grab the maximum value for the Month field for the current year (I can use the TODAY function to make it dynamically pull in the current year). Then, for the Comparable Period column, I can compare the Month field to see if it is less than or equal to the Current Period. If it is, then I’ll set the value as a “Y” to indicate it falls in the comparable period or “N” if it doesn’t. This way, if I come across month 8 and my current period only goes up to 7, it will mark that as an “N” which will allow me to easily filter out those results.
Lastly, I will convert all this data into a table. The purpose of this is so that I can easily reference the different fields later on, without having to remember column letters. To convert this into a table, select Insert and click on Table. Then, on the Table Design tab, you can name the table something that’s easy to remember. In my example, I’ll refer to it as tblConsolidated.
Step 2: Identifying the KPIs to track
Before rushing out to create the pivot tables, it’s important to know what you want to track. You don’t want to create a pivot table and track everything possible, otherwise it won’t be a useful summary, which is what a good dashboard should aim to do. That’s why you should devote some time to identify what some of your KPIs should be.
There are a lot of metrics on here and these are the ones that I am going to use, which will help gauge how active and busy Las Vegas is:
Visitor volume. Obviously the number of people visiting the city is a great indication of how many people there are.
Occupancy levels. If hotels are booked up, that’s another positive sign that the city is busy.
RevPAR. This takes the room revenue divided by the number of available rooms. It shows how well a hotel is filling up its rooms at a given rate.
Average Daily Rate. This is partly reflected in RevPAR but it can be a useful indicator as people are more familiar with room prices than they are with RevPAR, especially those who visit Las Vegas often.
En/Deplaned Passengers. This is a helpful metric to know how much out-of-town traffic there is coming to the city.
Average Daily Auto Traffic. With this metric, readers can see how busy the roads are.
Gaming Revenue (Las Vegas Strip). This is another important KPI because it tracks how much people are spending at casinos.
Step 3: Creating the pivot tables
Now it’s on to creating a pivot table for each KPI you want to track. To make this process easier, just create a pivot table one time, and then copy it for as many charts that you want to create. This way, you don’t have to go back and select Insert->Pivot Table over and over again. Just make sure to leave enough room so that they don’t overlap, otherwise you’ll encounter errors.
It’s also a good idea to label your pivot tables by going into the PivotTable Analyze tab. For a pivot table to track visitor volume, you might want to call that ptVisitorvolume, for example. This will be helpful later on if you want to change charts and aren’t sure what PivotTable1 relates to. You’ll also likely want to change the default formatting for a pivot table:
To change the format, don’t just highlight the cells and make the changes, otherwise they’ll revert back once you update the data. Instead, right-click on one of the values and select Value Field Settings. Then, select Number Format and apply the formatting you want to apply to that field.
What I also like to do is put all the pivot tables on a separate tab to keep them organized, while all the charts will go on a main tab dedicated for the dashboard.
Step 4: Creating the charts
When creating your charts, one thing to consider is how you want the data to be visualized. You can do this as part of the stage to identify KPIs. For visitor volume, for example, I’ll use a line chart since I want to see the month-over-month progression. This will also make it easy to compare against multiple years.
Since these are charts created from pivot tables, they are pivot charts, and they come with drop-down options:
They aren’t terribly appealing and to get rid of them, click on the chart, select the PivotChart Analyze tab and unselect the option for Field Buttons:
One thing that can help with creating charts is by using Excel’s existing Chart Styles, which are on the Design tab (which is visible if the chart is selected):
This can be an easy way to customize your charts without having to do so manually.
You may also want to adjust how the data is displayed. Visitor volume, for example, may make sense to leave as the default, which is a summation. But when looking at ADR or RevPAR, you wouldn’t want to sum those values up. Instead, you may want to calculate the average instead. To do that, right-click on one of the fields and select Summarize Values By and select Average
Now, you’ll see an average based on period, which makes more sense than summing up prices.
At this point, it comes down to your personal preferences as to how you want to design the charts, and it would be far too deep to try and get into all those options here. However, I’d suggest mixing up a bit of bar and column charts and also changing up the colors so your dashboard doesn’t look like the same item over and over.
Some additional things you may want to consider are:
Adding data labels. And if you do use them, consider not using axis labels;
Using legends where and when make sense to do so;
Adding background images to your charts to have a different look and feel to them;
Having descriptive titles to help summarize what the chart is displaying;
Not plotting too much on on chart. You may want to consider plotting years instead of months;
Not using a border color so that your charts blend in with the background.
Here are a couple of charts I created with images in the background to make it clear what they are showing:
Step 5: Adding key numbers at the top for further emphasis
Charts are good, but what can also be useful is to put key numbers right at the top so that readers don’t have to spend much time looking for the most important metrics. For example, using formulas, you can pull in the total number of visitors for the period, the occupancy rate, the ADR, RevPAR, and other items, based on the latest information.
While these can be good to include in charts, by making them big and allowing them to stand out as soon as you open up the dashboard, it can help drive the point home even further.
In the example above, I have a list of the current metrics along with the growth rate or comparable percentage from 2019, to help show how the metric is doing compared to that year. You could also add conditional formatting to this to highlight where there is an improvement and where things may have worsened.
Step 6: Finishing touches
Once you’ve got your charts and metrics all on there, the last piece of the puzzle is to add a title as well as any icons or images that may be relevant to help give some added pop to your dashboard. If you go to the Insert tab, you can use that to pull in pictures from the internet. Excel also has built-in icons and stock images that you can use, just by doing a search:
This can be an easy way to help your dashboard stand out even further. Here’s a snapshot of the dashboard I created:
If you liked this post on How to Create a Dashboard to Track Las Vegas’ Visitor Data, 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.
Do you need to calculate the time that that has elapsed between two date values in Excel? In this post, I’ll show you how you can show the difference in hours, minutes, and seconds. This can be useful if you need to determine hours on a work shift or just to see how much time is remaining until a deadline.
The following table is what an employee’s shift schedule might look like over the course of a week:
You have the time they started work, left work, and the duration of their break. To calculate the time difference and net hours worked, this can be accomplished by the following formula:
Time Work : Time Out – Time In – Break
It’s just a simple subtraction formula. However, the tricky part is that by default, Excel will calculate this difference in days and so the result will be shown as a fraction of a day (since it is less than 24 hours):
There are a couple of ways to fix this. The first way is to multiply the results by a factor of 24 so that the calculation gets converted into hours:
The caveat here is that now instead of fractions of a day, you now have fractions of an hour. If you prefer to not do any conversions and instead just want to display the value as elapsed time as hours and minutes, that can be done by formatting the cells, which is the alternative method.
To do this, select the cells in the Total Time column and select CTRL+1 to Format Cells. From there, go to the Custom category and enter [h]:mm as follows:
By doing this, the result will be similar to when you multiplied the values by 24:
An important difference you’ll notice is that the Total Time column shows in terms of hours and minutes, whereas the Hours column still shows fractions of an hour. For instance: 9 hours and 30 minutes shows up as 9:30 in Total Time but under the Hours column it is 9.50. One column is showing the actual minutes while the other is showing it in terms of fractions of an hour.
If you wanted to only show the number of minutes elapsed, the time format would simply be [m]. Then, your time would show in terms of minutes.
And to show the time in seconds, use [s]:
You could, of course, do all of these conversions by multiplying the hours field by 60 to convert it into minutes and then by 60 again to convert into seconds. By just changing the number format, you aren’t doing any changes to the original calculation. Either option can get the desired end results. However, if you want to specifically show hours and minutes and seconds, and not fractions of an hour, you’ll want to use either [h]:mm or perhaps [h]:mm:ss if you have your time broken down to the second.
If you liked this post on How to Show Elapsed Time 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.
Duplicate and unique values can be difficult to find in a large data set. In this post, I’ll show you how you can find and highlight duplicate values, as well as how to extract unique values, in Google Sheets. In this example, I’m going to use a list that shows historical World Cup results, including the winners of past years.
Highlighting and finding duplicate values
There is a built-in function in Google Sheets that allows you to filter out unique values. Under the Data menu, there is a section for Data cleanup where you can select the option to Remove duplicates.
However, by doing this, you will actually remove duplicates. And if you don’t want to remove data, this could lead to unintended results. If you simply want to find and highlight duplicate values, you’re better off using conditional formatting.
In this data set, I’m going to highlight the duplicate values in the champion field to identify repeat winners. To do this, I can create a conditional formatting rule in Google Sheets to apply formatting when criteria is met. My criteria will be to look at whether a value shows up more than once within a list. The formula utilizes the COUNTIF function:
=COUNTIF(B:B,B1)>1
This formula needs to be added when creating a conditional formatting rule. To set that up, I’ll select the entire column and under that Format menu, click on the option for Conditional formatting. In that section, there will be an option to Add another rule. And under the drop down for Format cells if…, I select the option that says Custom formula is. And in that box, I’ll enter in the above formula:
I’ll leave the default highlighting options, and now it will highlight all the values that show up more than once in column B:
As you can see, there are many repeat winners in this list. If I only wanted to see the winners that only won once, then I would adjust the formula so that it looks for a value of equal to one, as opposed to more than one.
=COUNTIF(B:B,B1)=1
By altering the formula, it will highlight only the values that show up once:
You could also go further and make even more specific conditional rules, such as highlighting countries that have won two or more times. Through conditional formatting, you can make your highlight rules as specific as you need them to be.
Extracting and counting unique values
If instead of getting the duplicates you wanted to just get a list of unique values, that’s an even easier process in Google Sheets. Using the UNIQUE function, all you need to do is select your range, and Google Sheets will give you a list of the unique values:
=UNIQUE(B2:B22)
This formula results in the following list:
There have only been eight countries that have won the World Cup heading into 2022. But suppose you only wanted to count the number of unique winners. For this, you can use the COUNTUNIQUE function, which takes the same range as the argument:
=COUNTUNIQUE(B2:B22)
The above formula returns a value of 8, which is the same if I were to count the number of values from the Unique formula. There’s also the COUNTUNIQUEIFS function that you can deploy which allows you to also apply an IF statement to the CountUnique function. Suppose I wanted to count the number of unique winners after 1980, that formula would be as follows:
=COUNTUNIQUEIFS(B2:B22,A2:A22,">1980")
Column A contains the year and this returns a value of 6, excluding the two countries that only won prior to 1980: England and Uruguay. Using this function, you can apply multiple criteria if you need to.
If you liked this post on How to Find Duplicates and Unique Values 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.
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:
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:
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.
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:
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.
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:
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):
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:
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:
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:
Another way you can accomplish this is by clicking the Sort button:
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:
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.
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:
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:
Now, the data pastes without any of the colors and formatting onto my Input sheet:
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:
That gets me a bit closer to where I want to get to:
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:
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:
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:
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:
By dropping this into my Input tab, this is what my Outputnow shows:
All that I needed to do was to copy the formulas and just adjust the columns they referenced on the Inputtab. 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.
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:
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:
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:
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:
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:
Now, I’ll remove the legend and format the base color, which is currently grey, to a blank fill color:
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:
With all those changes, my updated forecast chart now looks like this:
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.
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:
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:
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:
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 FormatAxis. Then, check off the box that says Categories in reverse order:
Now, at least my chart is going in the right direction (an alternative could be to structure your data in the opposite direction):
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:
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:
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.
![Modifying the number format to show elapsed time in Excel by using the [h]:mm format.](https://howtoexcel.net/wp-content/uploads/2023/01/image-11.png)
![Showing elapsed time in terms of minutes in Excel by using the [m] format.](https://howtoexcel.net/wp-content/uploads/2023/01/image-19.png)
![Showing elapsed time in Excel in terms of seconds using the [s] format.](https://howtoexcel.net/wp-content/uploads/2023/01/image-20.png)
