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:


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:

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:


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:

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.


How to Calculate Streaks in Excel

Do you want to calculate how long a team’s winning streak is, or how many cells in a row meet certain criteria? In this post, I’ll show you how you can calculate streaks in Excel. Unlike a simple count function, this will require being able to reset your count and go back to zero. I’ll show how this can be done using an easy approach that involves a helper column, and a more challenging way that doesn’t require one.

The easy way to calculate streaks

Here are some results, showing wins (W), losses (L), and ties (T).

Game results on a spreadsheet showing wins, losses, and ties.

The helper column I’m going to create will evaluate the criteria. And the criteria, in this case, will be whether the result is a win. For this, all that’s required is a simple IF statement checking if the value is a W:


If the result is a W, the formula will return a value of 1, otherwise, it will be 0:

Criteria column showing a 1 value for W and 0 for L.

Next to that column, I will create another one for the actual streak. This formula will look at the criteria column, and if it equals 0, then the streak is 0. If it’s a value of 1, then it will add on to the previous value in the streak column, and thus, add on to it. The formula is as follows:


And that results in the following calculations:

Result, Criteria, and Streak columns for calculating streaks in Excel.

The assumption here is that the earlier results are at the bottom and the most recent games are at the top.

If you wanted to calculate how many games were either won or tied in a row, and thus, an undefeated streak, all you need to do here is to adjust the criteria column. The updated formula would be this:


And now the streak values change:

Calculating an undefeated streak in Excel with a helper column.

The difficult approach, without helper columns

If you don’t want to use a helper column, calculating streaks is a bit more challenging. You will be using an IF function and checking for criteria, but this time you’ll need to always adjust your starting point (i.e where the streak is 0). And that will need to be within a SUM function to ensure that the values are added. The key to making this work is using the INDIRECT function so that you have control over the exact range you want to include.

Inside that function, I’ll start with column A and use the current row the cell is on, which can be done using the ROW function. Here’s how it starts:


B2 reflects the first cell in the streak calculation, and it will return a value of 2. The last cell needs to be the last time the streak was broken — when the team recorded a loss. This involves using the MATCH function and searching for an “L”. That formula is as follows:


Here again, I use the ROW function and as my ending cell, I put A15, which is the last cell in the range. This could be adjusted to use a MAX function to make it variable. Since the MATCH function will return a number corresponding to its position within the range (e.g. it won’t return the actual row), I will adjust for the row number immediately above the first cell to be searched. In this case, since I’m searching cells A2:A15, I need to add 1 to ensure I get the row number and adjust for the fact that the MATCH function doesn’t begin from the very first row. I will add all this together into my earlier formula:


Now I have to enclose this within the IF function and check to see if the result is a W:


Then, I put that all within a SUM function:


The one last adjustment that’s necessary is to account for if there is no loss found and the team starts on a winning streak. For this, I’ll add an IFERROR function just before the MATCH function so that if it evaluates to a 0 (after adding the 1), then it will default to the last row (15):


The full formula for calculating the win streak is the following:


Given how complex this formula is, it can get messy if you create too many conditions in it. And if you do have multiple criteria you’re dealing with, then the first approach may be the more practical one to use in that case.

If you liked this post on How to Calculate Streaks 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.

hiding values in excel

How to Hide Zero Values in Excel

If you’ve got a big spreadsheet with lots of numbers to look at, it can sometimes be a bit difficult to look at a large chunk of data. That’s where knowing how to hide zero values in Excel can be helpful in reading and analyzing data in Excel. By not seeing the zero values, you can easily focus your eyes on the more important numbers that may need more analysis.

However, it doesn’t have to be just zero values that you hide. Any number that’s insignificant for your analysis can also be hidden. For instance, you can be analyzing a company’s financial performance and choose to hide any movements that are less than 5%. The same principles apply as you would use to hiding zero values. Below, I’ll show you how you can hide not just zero values but any values that you don’t want showing up in your data while still factoring them into totals and any other calculations. You won’t be deleting anything, just masking the information.

First, let’s take a look at how to hide a potentially even bigger nuisance: errors.

How to hide errors on a spreadsheet

To help illustrate how to hide zeo values in Excel as well as other numbers, I’m going to use some real-world data — Amazon’s most recent annual earnings report, which the company released last month. Here’s the company’s income statement from the past three years:

Amazon's income statement over the past three years in Excel format.

If you’d like to follow along, you can download the data from the SEC. First up, I’ll add a few columns showing the change from 2018 to 2019 and from 2017 to 2019. Here’s how it looks just copying the formulas straight down:

Analyzing Amazon's income statement using Excel with error values showing.

I have divide by zero errors as there are rows that have no data. I could just remove these cells but as with anything in Excel, it’s good to be consistent. Rather than deleting those error values, I can get rid of the errors in one of two ways.

The first is by using an IF statement to say that if the denominator is 0 or blank, to ignore the calculation. The second is just to use an IFERROR statement.

Here’s what my formula looks like for the 2018 to 2019 change:


Where C5 is the 2019 data and D5 the 2018 numbers. I don’t need any parenthesis as order of operations ensures the formula will calculate properly. However, it doesn’t prevent me from getting a divide by zero error. Since the numerator can be blank or zero, what I’ll want to focus on is fixing the denominator in D5. To do this, I can add an IF function that looks at whether the denominator is a number. Here’s what my formula will need to look like to remove that error:


The formula now checks to see whether D5 is either a zero or blank, and if it is, it returns a blank value. Otherwise, it calculates as normal. Now I can copy this formula down and get rid of the error values.

An alternative way to fix this is by using IFERROR. Introduced in Excel 2007, the function can be an easy way to replace errors on your spreadsheet with another value. In this example, I’m going to use empty quotes (“”). The benefit is obvious: it’s a lot easier to use IFERROR than an IF statement, especially combined with an OR function as well. Here’s what my formula would look like with IFERROR:


It’s a whole lot easier and quicker. I don’t have to worry about the logic and all the reasons why the formula might error out. However, it’s not a perfect solution and here’s why: it will correct errors, but it’s possible they’ll be errors you’re not expecting. For instance, if I copied the data wrong or keyed something over and put text in a field where it should be a number, the IFERROR will correct that and you won’t be able to tell whether it’s blank because it is a divide by error problem or something else. That’s where it can be a little dangerous in using this one-size-fits-all approach to fixing error values. As long as you’re okay with that, it’s a perfectly good approach to fixing the divide by zero errors.

Here’s the data now that it’s been cleared of errors:

Analyzing Amazon's income statement using Excel after error values have been hidden.

That looks a lot better but the problem is that it’s still a lot of percentages to look at and it’s difficult at a glance to see what are the big changes are from the prior year. This is where it’s also important to hide zero values in Excel, as well as low values that aren’t useful for analysis.

How to hide zero values in Excel and other numbers that you don’t want or need to see

In order to hide data, it’s useful to use conditional formatting. If you’re not familiar with how to use conditional formatting, check out this post. Conditional formatting won’t remove or erase any data, which makes it a good solution that will keep all the data and calculations intact.

In the Amazon example, there are some pretty large-moving items in the list. Removing zero values won’t do anything and the threshold needs to be big for it to be helpful in hiding the lower values. Let’s start by removing the percentages that are less than 20%. Here’s the formula that I will use in the conditional formatting to accomplish this:


Column F is where the % Change from 2018 values are. I need to use the AND function because if I just look at anything that’s less than 20% this will also capture negative movements that are more than 20%. And for now, I want to keep those. I want to remove anything that’s between -20% and +20%, which is what the above formula will capture. If I was only looking to remove the zero values then the formula would be as simple as F5=0.

The next step is to adjust the formatting so that the cell font is white. Changing the color is an easy way to hide a cell’s value if it’s on a white background. While that data is still there, it won’t be visible:

Analyzing Amazon's income statement using Excel with error values fixed and low values hidden.

It creates a lot more white space, allowing me to see a lot more of the bigger values. The problem that I notice, however, is that there are some low values that are creating big movements in percentage. I can go a step further and create another conditional formatting rule that will also ignore the percent change for any item in 2019 that was less than $1 billion (1,000). This is how that formula will look:


I need to freeze column C because the conditional formatting will be used for the other change column as well and I don’t want the reference to move. Now, with this adjustment, it makes a much bigger difference and helps me narrow in on fewer items:

Analyzing Amazon's income statement using Excel with error values fixed and low values hidden.

I can see that the significant changes from 2018, besides the totals, were in sales, technology and content, and marketing. However, since the growth rate from 2017 is even higher, I’ll need to adjust those percentages to also ignore anything that’s not at least a 50% improvement. Here’s how that formula will look (note that I’ll only apply it for the % Change from 2017 column):


Remember, since I’m analyzing percentages, these figures need to be in decimal point. Otherwise, I would be using whole numbers. With those changes, this is how my data looks:

Analyzing Amazon's income statement using Excel with error values fixed and low values hidden.

Now I’ve also got a reasonable amount of items I can focus on for the % Change from 2017 column. In addition to the same items increasing from 2018, I notice that fulfillment costs have also shown a significant increase over two years.

The conditional formatting works great in clearing out numbers that I don’t want to see. However, there’s just one small problem…

Analyzing Amazon's income statement using Excel with hidden values showing on a dark background.

If I change the color to anything that isn’t white, those numbers that I hid become visible again. That leads me to another all-important section:

How to hide values in Excel that are on different background colors

You can create conditional formatting rules to address other background colors but that’s just not practical. If you use lots of colors on your spreadsheet the last thing you want to do is create a rule for every different color and make sure the cells are hidden in the same font color. There’s also a problem if someone changes the color too.

That’s why using font color to hide values in Excel isn’t a good idea. The good news is that there’s a much easier way to hide values that doesn’t involve you having to try and match up the color.

Rather than changing the color, what you should do is use a custom number format. Simply use three semi-colons and that will do the trick:

Choosing a customer number format to hide zero values in Excel.

Without going into the details of the different formats you can use, by using three semi-colons you’re telling Excel that you want no formatting to be used whether the amount is positive or negative. Now, my hidden data remains hidden regardless of the background color:

Analyzing Amazon's income statement using Excel with hidden values no longer showing on a dark background.

Now you don’t have to worry about background colors and can easily hide your data in any context.

If you liked this post on how to hide zero values in Excel as well as other values, 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.


Formatting and Removing Errors and Zero Values

Often times in a data set you’ll have to handle with errors that can wreck your data, especially if you need to do any analysis on it. There are several ways that you can handle errors so that they don’t show up in your data.


The first method is by using the IFERROR function, which allows you to easily replace the error with whatever you want in its place. If you want a numerical value, you may want to put in a 0, otherwise you can just leave it blank. 
I’ve purposely added various types of errors to my data set:
Here is one of the formulas that’s causing an error:
In the above example, I could use the formula =IFERROR(VLOOKUP(D7,L:L,1,FALSE),””) to replace the error with a blank. I could also put a 0 in its place instead of the “”. 

In older versions of Excel (2003 and earlier), the IFERROR function is not available. However, what you can use is a combination of the IF and ISERROR functions. To recreate the same formula as above, we can use the following:
The disadvantage of this method is you have to repeat your original argument. First, you are checking if the value is an error, if it isn’t, then you have to repeat the formula again to save the value. It’s not terribly efficient, and likely why we saw the IFERROR function introduced in newer versions of Excel.
Using the IFERROR or IF(ISERROR()) functions can be useful for eliminating errors, but sometimes it may not be helpful for dealing with specific ones. For example, if your cell is blank or it has an error and is made to look blank, you won’t be able to tell the difference just by looking at it. The danger is that you may assume it’s a different type of error.

What you can also use is the ISNA() function, which can tell you if the cell returns the #N/A error. This way you can trap this error specifically, rather than everything that can be captured by the ISERROR() function.
Getting Rid of Zeros

If you’ve used a lot of error-handling functions and replace your errors with zeros, you could up with a lot of zero values on your spreadsheet:
The problem if you have a lot of zeros on your spreadsheet, is it can sometimes be a distraction away from what you really want to see – the non-zero values. There are two ways you can get rid of the eyesore:
1. Change the format to Accounting. Doing this will remove the zero values and replace them with a dash, which makes it a bit easier to skip over when doing a review:
However, you may not want to use the Accounting format, and that leads me to the other option:
2. Conditional Formatting. Refer to this post on how to setup rules for this. What I normally do in these cases is set the zero value cells to a light gray color font so that they do not attract your attention:
Common Types of Errors

Here’a list of some of the common types of errors you’ll find in Excel:
#REF: This is an error that you’ll incur if your range doesn’t go far enough and the error relates to your reference. For example, consider a VLOOKUP formula that extracts from column number five but you only specified a range that had four columns, that would result in a REF# error.
#N/A: You’ll get this error if your VLOOKUP or MATCH formula is correct, but the value you’re looking up isn’t found, and hence, not available. However, there’s other contexts it can apply to, and it just means that it wasn’t able to find the value you were looking for. 
#VALUE: This error normally shows up when there is an issue with your actual calculation. For instance, if you’re trying to multiply a number by a field that has text. 
These are just a few examples of the errors that you’ll encounter, but these are also likely the most common that you’ll come across.