If you’re getting an error message in your Excel spreadsheet that there’s a circular reference, that means that at least one of your calculations is referencing back to itself in one way or another. It doesn’t always have to be that two cells are referencing one another. Even indirectly referencing back to a cell can cause a circular reference error.
First thing’s first though, you have to find out where your circular references are before you can fix them, which isn’t always easy. Below, I’ll show you how to find circular references in Excel using multiple approaches.
Finding circular references
Let’s look at a very simple example where cells A1 and B1 are just referencing one another. This is what the error would look like on the two cells:
The blue arrows pointing at one another indicate that the cells are referencing each other. If the circular reference involves another worksheet, you’ll see something like this:
If you double-click on that line it’ll bring up the Go To dialog box which will show you which cell it’s referencing on another sheet:
From here you can click on the item in the Go To box and click OK and it’ll take you to that cell.
If you’re able to see blue arrows or the link to another spreadsheet, then it’s easy to trace where the circular references are . But if you’ve got a large spreadsheet, it may not be so easy spotting where the circular references are, which is why you may need to look at other methods.
One way is by looking at the bottom left corner of Excel, where you’ll see it mention circular references if there is an error:
And if you’re on the same sheet that the circular reference is on, you’ll also see which cell is causing it:
But it won’t be able to show you all of the circular references if there are more than one here as there’s obviously limited space to do that.
Another way that you can look for circular references is by going to the Formulas tab and then clicking on the down arrow next to the Error Checking button:
Then, click on the Circular References button and you’ll see a list of all the circular references in the workbook:
You’ll notice this way it will also show you which sheets have circular references.
Fixing circular references
Once you’ve identified which cells need to be fixed, then what’s left to do is adjust the cells. If you’ve just made an error then all this may involve is just deleting the contents of the cell entirely or just removing the reference to the cell in order to remove the circular reference.
But in many cases, it’ll involve a formula. And this is where it can get a little tricky as you may need to restructure your formula to ensure it’s not circling back in some way to the original cell. One possible solution can be to add another field as an intermediary to ensure you aren’t trying to do too much in just one formula.
If you liked this post on how to find circular references 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’re doing any type of data analysis, a moving average can be useful in smoothing trends and normalizing data. Below, I’ll show you how you can easily create a moving average in Excel. The data will again be based on the retail and food services sales data that I used in my post covering how to transpose data.
To calculate an average in Excel, all you need to do is use the AVERAGE function. Simply select the range that you want to average, and the function will take care of the calculation for you. Here’s an excerpt from the data set:
If I wanted to calculate the average for the year 1992, I could total the values relating to those years and then divide it by 12. The total comes to $2,007,617 and after dividing it by 12, I get an average of $167,301.42. But I can skip the step that involves dividing the data by using the Average function. Here’s a look at the difference:
However, there’s a way we can simplify this even further. In this example, we still have to manually select the range that we want to average, and that’s not going to be optimal if you want to pull the average for every year. It’s also easy to make a mistake.
What we can use is the AVERAGEIF function in Excel. That function allows you to add criteria to your average. Here, we’ll use the year, 1992, as the criteria. And by doing so, we don’t have to worry about selecting the right cells since we can just select the entire column:
You’ll notice the year is hardcoded, which isn’t ideal. Here’s how we can fix that while also making it easy to pull the average by year:
This is a great way to average by year, but it’s still not a moving average. Let’s do that next.
How to make the average move with your data
Companies often track sales numbers for the last 12 months, also referred to as the trailing twelve months (TTM). It’s actually easier to do than averaging by year since there’s no criteria — you’re simply averaging the last 12 months. The one limitation here is that you have to have 12 months of data before you can start. Here’s how that would look in our example:
It’s as easy as just selecting the previous 12 cells in the range, averaging them, and copying the formula down. However, you don’t have to select the data range, even if it is just a one-time thing. If you include the OFFSET function, you can make your formula a lot more adaptable and flexible. It allows you to move your data set, and you can also determine how many values you want to include in your average.
Here’s how a TTM calculation could look like using OFFSET:
This is a more complicated formula so let’s breakdown what’s happening here. The OFFSET function has multiple arguments, here’s a list of them:
Reference
Rows
Columns
Height
Width
The reference is just your starting point. In the first formula, I used cell C13 as that was the current value. It wouldn’t make sense to use data from a different row and I also want to make sure it’s in the value column since that’s where I want my data to come from.
Next, is the rows argument. I put -11 in this case because I want to start 11 rows higher than where the reference cell (C13) is. Moving 11 cells up would put me at C2, which is the first data point. The columns argument is left at 0 because I want to remain in the same column.
The height argument is key here because I want to ensure my data set contains 12 values in it. Without this argument, I would simply get the value from cell C2, $164,095. You can ignore the width argument or set it to 1, since you don’t need to include other columns.
If your data is organized in columns rather than rows, all you’ll need to do here is to switch around the arguments (e.g. rows would be in columns, height argument would go into width, etc.)
The last part of the formula is enclosing it within the average function so that it calculates the average.
Changing the number of periods you want to average
The big advantage of using the OFFSET function is now it’s a lot easier to manipulate your data and change how many values you want to include in your calculation. Suppose that instead of the last 12 months, we wanted to do a three-month average. All that would include is tweaking the OFFSET function so that it goes two rows back and includes three values. Here’s how that would look:
The key takeaway here is that the number of rows or columns that you want to go back will be one less than the number of rows or columns you want to include. The only exception would be if you don’t want to include the current month’s data. For instance, if in the December moving average you didn’t want to include December’s data and wanted to go from September-November, then you would offset three rows rather than two.
How a moving average can help smooth trends
If we were to look at the data since 2018, here’s how it would look in a chart:
It’s fairly stable and there aren’t any big jumps from one month to the next. Here’s how it would look with a three-month moving average:
Using the moving average, we can see what the longer-term trend is. You’ll notice that in the drop off that happened in March and April, the average goes on a much smaller decline because it’s still including earlier months in the calculation. And if we use a six-month average, then there’s even less of a dip in the trend:
Which period you use for a moving average will ultimately depend on how much smoothing you want. A short timeframe will be more volatile than a longer one, but a longer one may take too long to capture any changes.
That’s a quick overview of how to calculate moving averages in Excel. As you can see, you can just quickly grab an average or you can build a versatile formula using the OFFSET function which can make your calculations easier to change in the future.
If you liked this post on how to do a moving average 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.
Creating charts and graphs is a great way to display data visually and make it easier for users to read and understand it. However, in some cases, you don’t want or need a big chart, and something smaller would be more useful. This is where sparklines can come in to play and help you get your point across without a big chart in the way. Below, I’ll show you how to quickly and easily make sparklines in Excel that can quickly add context to your data.
*Please note that sparklines were a new feature of Excel 2010. If you’re running an older version of Excel, you won’t have these options available*
Getting the data set ready
I’ll show you how to create sparklines using my data, which is a download of Amazon’s income statement over the past 10 quarters. Here’s what it looks like:
From afar, it’s not the easiest thing to analyze to identify any trending. And ideally, we’d like to have some trending shown for each major income and expense category. Adding a chart just isn’t useful in this case, and this is where sparklines can help.
Creating sparklines in just one click
I’ll start by selecting the row that has revenue and then on the Insert tab and under the Sparklines category I’ll select the Line button:
It will then show me the range that I’ve selected and it will allow me to select where I want to place my sparklines:
In most cases, you’ll probably want this right next to your data. And that’s what I’m going to do — put it in the next cell to the right of the data, L3. Now it’s created my sparkline:
But there’s just one problem:
The sparkline is showing a downward trend. Amazon’s revenue has been increasing, not decreasing. One solution is to re-arrange my data, but that’s not necessary. To fix this, I select the sparkline and then under the Sparklines tab I click on Axis and select the option that says Plot Data Right-to-Left:
Now my sparkline looks a lot better:
This is an optional step and it depends on which direction you want your sparkline to go in.
Applying sparklines to other rows
Now that I’ve got one sparkline setup, it’s time to set up the sparklines for the rest of the income statement line items as well. Surprisingly, this is as easy as just dragging the sparkline down and copying it down to the other rows:
One of the cool features of sparklines is you can quickly add trending to every item without having to add a separate chart or graph for each row. And even for the rows where there was no data, it doesn’t result in an error, either.
If you prefer a column chart to a line chart, then you can easily make the change as well in the Sparklines menu:
It will quickly change the format of the charts:
There’s also a win-loss chart that you can use if you have negative and positive values. However, in most situations, you’re going to use either line or column charts, especially when you’re looking to show trending. But
You can change the color and other features of the sparklines just like with other charts. And all those options are available from within the Sparklines tab.
If you liked this post on how to create a drop-down list 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 need to collect user input in an Excel spreadsheet, you’ll want to be able to minimize errors and typos in the information that you receive. That’s why it’s important to know how to create a drop-down list in Excel as it will limit the selections that someone will have to choose from. Using lists can prevent someone from making a data-entry error which can save you a lot of grief later on when you go to analyze the data. Here are the steps involved in creating a drop-down list:
1. Create the list of items that you want your user(s) to be able to select from
The first step in learning how to create a drop-down list in Excel is to first identify your list of selections. This seems like an obvious step but sometimes people don’t actually set aside a space on their spreadsheet for a list of items and simply hardcode the selections later on. By listing the items, you can easily modify them later and visually see what are the user’s options. In this example, I’m going to give a user a list of stores to choose from:
2. Convert the list into a table
You don’t need to do this but there’s an important reason to do so: if you add items later, the range you select for your drop-down list will automatically update. If you just select a regular range, you’ll have to modify it if you add more options to it later. The goal is to make this as easy as possible. And if you always have to adjust the range for subsequent additions, it’s an easy step to miss.
To create a table, just select your range and on the Insert tab, click on the Table button:
Make sure to include your header in the select and that you tick off that your data includes headers:
Now you’ll likely see some automatic formatting applied to your list that shows it’s now a table:
3. Create a named range
Within the table, where you selections are, create a named range. Just select the cells you want to use (they should be everything in the column) and just assign a name to them. Here’s how you can create named ranges in Excel. Don’t worry if after assigning a named range it doesn’t reflect the name in the reference in the Name Box. Since it’s a table, it’ll still reference the table name. In my example, I created a named range called Stores.
By referencing named ranges, you avoid having to rely on cell references and it’ll make your list dynamic. This is also going to play an important role if you want to have multiple lists, with one based on a prior list’s selections.
4. Create the drop-down list using data validation
Decide where you want your drop-down list will go. Sometimes it’s helpful to highlight it so that it’s easy to distinguish it from other cells. In my example, I’ve highlighted the cell in yellow. Then, click on Data Validation under the Data tab:
From there, select a List for your range. The list should be equal to your named range or the range of cells you want to use for your drop-down selections. I’m going to set this equal to the Stores named range. When using named ranges, always put the equals sign in front:
Now, the drop-down list is ready to go:
You don’t have to use a named range for a list. You can just select the range manually or you can just type the options in:
As you can imagine, this is a very cumbersome approach and can be very time consuming if you have a long list. Unless you’ve got only a few options that will never change (e.g. yes/no), it probably doesn’t make sense to manually enter the selections this way.
A key benefit of using a named range, within a table, is that if you add selections they’re automatically updated. I’m going to add StoreF to my list of stores. And all that involves is just typing that store directly below the last value in the table:
If I go back to my drop-down list, the selection’s already there:
Had I manually entered the drop-down selections, the list wouldn’t update automatically. I would need to adjust it manually. This can obviously save a lot of time if your list will grow over time.
Creating drop-down lists based on prior drop-down list selections
If you’ve got multiple drop-down lists and want to make them dependent on prior selections, this section is for you. The good news is that it’s largely the same approach. If you know how to create a drop-down list in Excel, adding dependent lists won’t be much more difficult.
You can make some very complicated and nested drop-down lists possible by using a combination of tables and named ranges. In my example, suppose not every store sells the same product. So a scenario can be that a customer is placing an order and selects a store and then in their next selection they can select from a product that’s available in that specific store.
What we’ll need to do in that case is create another named range for that specific store. That range will show the products that store has available. Now, unless the number of selections will remain the same (e.g. same number of stores as there are products available in that store) — which I’m going to guess is unlikely — you’ll want to create a separate table. In this example, I’ll create a list of products just for StoreA, and convert it into a table:
I also need to select the list of products and assign a named range to them. For the named range, it’s important that I assign it the name of the store: StoreA. The reason this is important is that this becomes key to my formula in order to link the previous drop-down selection (where I chose a store) to the new named range.
In the next data validation list, I’ll need to use the INDIRECT function and refer to the previous selection (for stores). In my spreadsheet, the cell that contained the store selection was L6, and so my INDIRECT function will need to reference that in the data validation:
Since the range is hardcoded in the data validation settings, if you move your drop-down box you’ll need to update the indirect formulas. Here’s how my second drop-down selection looks now:
By using the INDIRECT function, the drop-down selections for the product category were updated based on the StoreA selection. I’ll create another table for StoreB where only ProductD and ProductE will be available. Now, when I go to select StoreB, these are what my selections look like:
The named range is set to StoreB for those selections. What we can do is drill down even further. Suppose ProductA and ProductB came in various sizes – for simplicity’s sake, I’ll just call them Size1, Size2, and Size3. To do that, I’ll again need to create a table for those selections, and a named range that refers to the product name:
These ranges are called ProductA and ProductB. I’ve put them in separate tables because since there are a different number of options, I don’t want them to be part of the same table. If they were in one table then ProductB would include blanks. And if only selected the first two selections then it wouldn’t automatically update if I added more selections.
Here’s what my new drop-down selection now looks like if I select ProductA:
For the third drop-down selection, I have to again use data validation and use the INDIRECT function and refer to the adjacent cell. If I select ProductB, I only have two sizes to choose from. Here’s how the drop-down lists look in action:
If you liked this post on how to create a drop-down list 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.
One of the most frustrating things that can happen to the spreadsheet you’ve spent hours or days on is when someone overrides a formula or a cell with important information. That why it’s important for people making and designing spreadsheets to be familiar with how to lock cells in Excel. It’s not a difficult thing to do and it can save you a lot of headaches down the road. There are two ways you can go about doing it. I’ll start with the approach that should work even on older versions of Excel:
Step 1: Unlock all the cells
By default, Excel sets that status of every cell to locked. However, until you actually protect a sheet none of that goes into effect. But you’ll actually want to undo that. The first thing you should do is select all the cells (CTRL+A) in your sheet and then go into Format Cells (CTRL+1). From there, go into the Protection tab where you’ll see this:
Untick the ‘Locked’ box and this will leave all the cells unlocked. It sounds like the opposite of what you want to do. However, it’s easier to unlock everything and then re-lock the cells you really need locked. Doing the reverse is going to be more time-consuming.
Step 2: Select the cells you want to protect and then lock them
Now, its time to select the cells that you want to lock. Formulas, any sort of rate schedules or information that shouldn’t be changed are things you’ll want to identify. Once you’ve selected them all, you can go back into Format Cells and this time you can re-check the box to have them locked. If any of these cells contain formulas that you don’t want people to see, you can also tick off the Hidden box. Doing this will prevent anything from showing up in the formula bar when someone looks at one of these cells.
Step 3: Lock your worksheet
Even though you’ve already selected which cells you want to be protected, nothing’s technically happened just yet. What you still have to do is lock the worksheet itself. On the Review tab, you’ll have a button to Protect Sheet. Click on that, and you’ll have the following options to choose from:
First thing’s first, you’ll want to put a password in. Otherwise, there’s really no point in protecting these cells if someone can just unlock the sheet without any password.
By default, Excel selects the first to options when protecting cells, allowing users to select both locked and unlocked cells. However, you can untick the locked cells if you don’t want them to be able to even select locked cells. If your goal is just to protect cells, these two options should suffice. But you can also restrict formatting cells, inserting and deleting rows as well.
Once you click OK, if you’ve entered a password, you’ll be prompted to re-enter it again to confirm it. Now your cells are protected. If you attempt to change the value on any of them you’ll get the following error message:
If you want to be able to change those cells, you’ll now have to go back to the Review tab and click on Unprotect Sheet. There, you’ll be asked for a password if you’ve entered one.
Optional step: highlight the cells you plan to lock
To prevent frustrating your users, you may want to take an additional step and highlight the cells that they either should or shouldn’t enter values in. This will avoid them getting frustrated with error messages popping up when they try and make changes. Highlight cells can make it a lot easier for users to identify which cells they should edit.
Tip: here’s a quick way to to find all your formulas
If you don’t want to hunt down all the formulas in your sheet that you want to protect, the good news is it’s easy to find them. You can use CTRL+~ to toggle showing formulas or values on your worksheet. However, there’s an even easier way to select all your formulas. By pressing F5 you’ll get the Go To box:
Click on Special (this is greyed out once you’ve locked the worksheet, so you’ll want to do this beforehand). Then, click on Formulas in the next menu:
Click OK and now all your formulas will be selected. From here, you can go to the Format Cells options and make sure they’re locked or hidden.
What if you’ve made a mistake and protected the wrong cells?
If you need to make a correction to which cells you’ve selected, all you need to do is to unprotect the sheet. Then repeat steps 1-3. But you may notice something odd when you go back to unlock all the cells:
This is how the checkboxes will show up if not all cells are locked and not all cells are hidden. You’ll only see the boxes blank or ticked off if all the cells have the same values (e.g. protected or hidden). Clicking once on each of these text boxes will turn them into checkmarks:
If you were to click OK then all the cells would be locked and hidden. Clicking these checkboxes an additional time will make them all unlocked and unhidden (but not until you protect the worksheet):
The alternative approach: Protect cells in Excel using the Allow Edit Ranges option
Below, I’ll show you another way how to lock cells in Excel that doesn’t require unlocking and re-locking cells. Rather than following steps 1-3 what you can first do is use the Allow Edit Ranges button on the Review tab. Pressing that button will give you the following options:
From here, you can click on the New button. Next, select a range that you want users to enter data on:
In this example, I’ve set a name of Range1 to everything in column A. I’ve also put no password for this range. Now, if I go to protect my entire worksheet, I can still edit any cell in column A (regardless of it is set to locked or not). If I had added a password for that range, I’d be prompted to enter it before gaining access to the range:
You also have the option to designate certain users who don’t need a password to edit the range. You can do this by clicking on the Permissions button:
What’s also convenient about the Allow Edit Ranges feature is that you also can protect the sheet right from its options.
Which method should you use?
Ultimately, both methods can help you protect your cells and formulas in Excel. If your worksheet is very structured and there are only certain places where users should make changes, then using the Allow Edit Ranges approach probably makes a lot more sense.
If, however, most of your cells should be editable and there are only a few cells that you need to lock or they’re spread out all over the place, then the first method may be preferable.
Either approach can work and it may just come down to your personal preference on how to lock cells in Excel.
Adding additional controls—locking down the workbook
If you want to take things a bit further and prevent people from modifying, viewing, or deleting worksheets, what you can do is protect your workbook as well. This is a simpler than how to lock cells in Excel and all that you need do here is just click on the Protect Workbook button and put a password on your entire workbook. Doing this will prevent users from renaming, moving, deleting, or hiding worksheets.
If you’d just prefer users don’t see certain worksheets and don’t even see that they’re there, you may want to consider using VBA to hide them without having to lock your entire workbook down. In some cases, locking a workbook may work well at preventing worksheet changes, but it’s an all-or-nothing approach: you can’t pick and choose which worksheets they can change — that’s where VBA can help.
If you liked this post on how to how to lock cells 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.
Whether you’re doing a forecast or looking back at how your sales were over a period of time, it’s important to ensure that you’re comparing apples to apples. While monthly and yearly numbers won’t have too much noise, once you’re trying to do a daily or weekly sales analysis, that’s when things can get a little challenging.
Below, I’ll show you how you can do a weekly sales analysis where you’re comparing the same days of the week against one another. This will give you an accurate picture of your year-over-year performance.
Step one: determine which day of the week you want to start on
This is a simple step and you’re probably going to go with either Sunday or Monday. But it’s an important one to consider because when you’re looking at weekly sales numbers, you want to be consistent. And while you can refer to the week number when comparing one week to a previous year, saying week 32 is not going to be as useful as saying the week starting August 5 or ending August 11.
In my example, I’m going to use Monday as my starting point to ensure that I’m not breaking up the weekend (the default in Excel is Sunday). To make it easy to compare a week, it will be helpful to create a header for the days of the week so it looks like a calendar.
Step two: entering the first date of the weekday you selected
The first Monday of 2020, wasn’t until Jan. 6 this year, which would be the second week of the year if we start on Mondays. The previous Monday was Dec. 30, which was technically week 53. Weeks 1 and 53 are often abbreviated. For now, just accept that there’s no Monday in Week 1 of 2020. I’ll show you how we can get around this problem further down.
For now, Jan. 6 will be our starting point which we’ll call Week 2. Now, that we have our starting point, we can build out what our subsequent weeks will look like.
For example, if I want to find out the start date for week 40, what I can do is simply use the following formula:
First, I multiply 7 by the difference in weeks. Then, add that to the first Monday value. In this example, it tells me the 40th Monday of the year is Sep 28, 2020. That’s why setting up the first Monday values is important to ensure that it’s easy to get the remaining dates.
This is the easier approach to take. However, later on I’ll show you a way where you don’t have to enter in the first Monday of the year.
Step three: filling in the remaining dates of the week for your sales analysis
Getting the starting date of the week is the toughest part. From there, all you have to add is just add 1 to each subsequent day:
Just adding 1 to the previous date will increment to the next day. No special formulas needed here.
Step four: getting the prior-year date
To get the previous year’s data you can follow the same approach as in step two. However, I’ll use this as an opportunity to show you another way that you can get the data. One that won’t require you to pull out the calendar.
First, we need to know what day of the week Jan. 1, 2019 fell on. To do this, we can just use the following formula:
=WEEKDAY(“Jan 1, 2019”,2)
The reason I put the number 2 as the second argument is because my week is starting on a Monday. If I set it to 1 or left it blank, the default would be Sunday. This is important because if Monday is my first day of the week then it’s day value is 1 and Sunday is 7. Had I used Sunday, then Sunday would have a value of 1 and Monday would be 2. This is why it’s important to know which day of the week you want your week to start on.
In 2019, Jan. 1 fell on a Tuesday, and the formula above gave me the result of 2. (Monday is 1, so Tuesday would be 2). The reason I need to know the weekday is because I need to adjust the date to find out when that week actually started. I use the following formula to do that:
What this formula does is subtracts Jan 1, 2019 from the number of days it is above day 1. It then moves the date back. I can simplify this formula by entering Jan 1. 2019 in cell A1. Then my formula looks like this:
=A1-(WEEKDAY(A1,2)-1))
I no longer need to use the DATEVALUE function and now it’s a bit easier to use. There’s also less chance of an error when entering the date. Now, when I want to find out the first day of the week, I can multiply 7 times the week number and add to this calculation:
=(A1-(WEEKDAY(A1,2)-1))+(7*(B1-1))
B1 is the week number. In this example, if I were to enter Jan 1. 2019 for cell A1, that would give me a result of Dec 31. 2018 for the start of Week 1. Excel also considers this to be the week that contains Week 53 and Week 1. This is where you can get around this issue. By calling this Week 1 of the current year and including December’s days into this week, it will ensure you don’t have the Week 53 problem. It may not look great to call the previous year’s dates part of the new year but it avoids having to manually make adjustments for this period.
Using the updated formula, I can change the Jan. 1 date to reflect 2019 and use week 40 to update my comparables for the weekly sales analysis:
From here, it’s just a matter of now using a SUMIF function on your data to pull the sales for each one of these dates and you’ve got your comparable sales numbers. With 2020 being a leap year, you can see that the dates have moved up two days from the prior year. Without the date adjustment, you could have ended up comparing a Sunday (Oct 4, 2020) against a Friday (Oct 4, 2020).
If you liked this post on how to do a weekly sales analysis, 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 your data contains date and time, and you only need the former, there are ways for you to remove time from the excel date. The first step, however, is in determining whether your data is in date format or whether the information is stored as text. Depending on which one it is, it will change how you will need to manipulate the data.
You can use the TYPE function to determine whether your data is in text or numeric format. The function evaluates a value and if it is numeric it will return 1 and if it is text the result will be 2. That will determine which path you need to focus on: converting a text date or just pulling the date values that you need. The latter is the easier of the two approaches.
Removing time from a date value
If the data is in date format, then it’s as easy as using the DATE function to pull out the fields you need. Let’s start with a date that shows the following:
2020-02-29 12:00:00 PM
It has more detail than we need with the time in there but it also has everything that’s needed—year, month, and day. The easiest way to pull out the date is using a formula as follows (where A1 is where the original data is):
=DATE(YEAR(A1),MONTH(A1),DAY(A1))
The DATE function takes three arguments: year, month, and day. By pulling these values out from the cell that has the time, we’re effectively creating a new value that has everything except the time. Now, if you don’t want this to remain a formula what you can do is copy the cell with the date and not the time, and paste it as values. Now, you’re left with hard-coded date values that do not contain the time.
As mentioned, this is the easy part of the process. The more difficult one is if your date is stored as text and where the DATE function results in an error if you try the above calculation. Let’s take a look at how to remove time from an Excel date when it’s in text format.
How to extract the date from a text field
If the same value above was stored as text, the formula involving the DATE function would result in an error. To pull the values that are needed to arrive at a proper date value, we’ll need to parse the data. Parsing can be a bit complicated but when you’re dealing with text, it’s the only way around getting the data you need.
In the above example, the date fields were separated by hyphens but it could be that slashes are used as well. Ultimately, it doesn’t matter, so long as there is some pattern that separates the month, day, and year fields. We will still use the DATE function. But in order to put the correct values in, pulling out the key information is going to be the challenging part.
Let’s start with pulling out the month, since in a month-day-year format, it’ll be the first value and thus, the easiest to extract. Here’s how the formula to pull the month would look, again, assuming A1 is where the data is:
=LEFT(A1,FIND(“-“,A1,1)-1)
Since the month is the first value, we use the LEFT function to pull the characters at the beginning of the cell. A1 is the cell we’re looking at, and the second argument is the length of the string to pull. Here, we’re looking for the dash(-) within the cell and subtracting one character so that the dash itself isn’t included in the extraction. This formula would produce a value of ’02’ and correctly return the month value.
To get the day is a bit trickier since it’s between dashes. It’s still possible to extract it but the formula is a bit more complex and requires using the MID function. Here’s the function with just the first two arguments filled in:
=MID(A1,FIND(“-“, A1 )+1,
The first part of the formula specifies the starting point. For here, we’re again using cell A1 but this time we’re looking for the dash using the FIND function to indicate where the second value begins. We add one to this value to ensure that we aren’t starting at the dash. Here’s what the next argument looks like, for the length of the value:
Here what we’re doing is using the FIND function to search for the dash but this time we aren’t starting from the first position but are starting from where the first dash was found, and adding a one to that. Then we subtract where the first dash was found, and the difference is the length of the string. It’s a complicated, nested function but it does what we need it to do. The completed formula for the day looks as follows:
The last part is to extract the year. And because this comes after the second dash, we’re going to need to nest two FIND functions, not just one. You could try and always start from a certain number, for example, the seventh character if your date format will always by mm-dd-yyyy. However, using the FIND function ensures you aren’t taking any assumptions (e.g. they may be leading spaces). I also avoid hardcoding numbers in formulas whenever possible. Here is the formula that remains for the year function:
The nested FIND functions are needed to ensure that I’m starting to search for the dash after the second instance was found. I use the number four for the last argument because rather than making this formula even more complicated, I figure the year will either be two characters or four, and it won’t deviate. If your data contains two characters for the year, then you can just change the final argument accordingly.
That leaves us with this long formula to extract the date for the mm-dd-yyyy:
It’s a complicated one so it may be easier to just copy and paste it rather than trying to reconstruct it yourself. If your original date is in dd-mm-yyyy format, here is a formula for that:
This just involves flpping around the formulas to grab the month and day. If your dates use “/” instead of “-“, then you can just to a find and replace in the formulas above to replace all the “-” with “/” or whatever else your system may use. Regardless how the data is separated, you can adapt the formula to how your data looks.
As you can see, having your data in the right format can make this process a whole lot easier. It’s once you get into text that it becomes much more challenging in pulling the date out. And again, once you’ve got the data you want, copying and pasting as values will ensure you don’t have to keep both the old and new data together.
If you liked this post on how to remove time from Excel date, 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.
Entering data into Excel isn’t much fun, especially if you’ve got a lot of it to enter. The good news is that there are plenty of ways that you can expedite the process so that it’s more efficient and takes less time. Here are eight ways that you can make data entry in Excel a whole lot quicker:
1. Using tab to cycle through your fields
If you’re entering fields across several columns, using the tab button can make it easier to enter data rather than using the arrow keys. And by hitting the TAB button, once you’re at your last column, you can hit the ENTER key and you’ll start from the beginning of the next line. As long as you keep tabbing over, Excel will remember which column you started from when you press ENTER.
Even if you accidentally tab over one or two fields too many, you still don’t have to use the arrow keys. All you have to do is press SHIFT+TAB and you will move in the opposite direction. It has the reverse effect of just pressing the TAB key. Similarly, the ENTER key will bring you down a row while SHIFT+ENTER will move you up a row.
Anytime you can avoid using the mouse or arrow keys, your data entry in Excel can go a lot quicker.
2. Using CTRL+D and CTRL+R to copy data
If you need to copy data from the cell directly above, you can use the CTRL+D shortcut and it will do just that. And if you want to copy data from the left, then you’ll use CTRL+R. This will work the same as if you were to drag the cells. That means that formulas will copy over as well.
3. Use Page Up and Page Down to cycle through tabs
This is another shortcut that can help you quickly jump through different tabs in your workbook. Page Down will cycle through the worksheets to the right, and Page Up will go through the worksheets that are to the left of your active sheet. If you’re entering a lot of data across many tabs, this will help you avoid having to use the mouse to switch tabs.
4. Entering data in multiple worksheets at once
If you have to enter the same data, the same formula, or if you just need to change the formatting so it’s the same across all worksheets, the good news is you don’t have to do one tab at a time. Instead, select all the sheets that you want to make the changes to and then enter the data or make the changes you need to make in any one of them. If the worksheets are all selected, the changes will be applied to all of them.
In the above screenshot, I’ve selected Sheet1, Sheet2, Sheet3, and Sheet4. The one I’m currently in is Sheet1. Any changes that I make in that sheet will carry over to the others.
Be careful, however, because if you make a change in cell A1, then A1 will change for all the worksheets that you’ve selected. If your data is structured slightly differently in the sheets, this may give you some unexpected results. This should only be used if your data is in the exact same structure. For formatting, however, it’s an easy way to apply formatting to many sheets at once. But when you’re entering data or formulas, you should double-check which cells you’ll be entering the data into to make sure that they’re correct and won’t cause issues across all the different sheets.
5. Moving quickly throughout your worksheets
Tabbing over can be helpful as you’re entering data, but it may not be of much help if you need to go to the end of your data set. For this, there are a couple of things you can do. If you want to go to the last row of your data, double click on the bottom of your active cell, which will send you to the last row where data is entered in that column. If you want to go to the furthest column to the right, double click on the right border of your current cell selection.
Alternatively, what you can do is use the CTRL key along with the arrow keys. For instance, if you want to go to the last row in the column that you’re in, use CTRL + DOWN. And if you want to go to the furthest column in the row that you’re in, use CTRL + RIGHT.
6. Make sure to freeze panes
When you’re entering data into many different columns, freezing the headers at the top can make it easy to ensure you’re entering the right information in the correct field. You may forget where you are if you’re working on many different fields and by freezing panes, you can ensure you have enough data to look at regardless of how many rows down you, and that can lead to wasted time. If you’re not familiar with freezing panes, this post can help give you a quick overview.
7. Maximize your white space
If you’re on a laptop or a small screen or just need to see a lot more data, you can hide some unneeded space in your spreadsheet. That can make it easier to enter data and minimize distractions. For starters, you can minimize the ribbon by double-clicking on any one of the heading names:
You can still use the ribbon and access it, but this way it gets out of your way unless you actually click on any of the headings again. To undo this, just double-click a header and you’ll get your full-sized ribbon back.
Next up, click on the View tab where you’ll see a few more things you can change:
Here you can uncheck the Formula Bar and even the Headings, assuming you don’t need to see this, of course.
At this rate, your spreadsheet is looking more like just some plain lined paper. And if you need any more white space, then maybe you’re better off simply investing in a bigger monitor or shrinking your resolution.
8. Use a second window
If you have a second monitor, and even if you don’t, you can create a second window within Excel. Whether you’re entering data into multiple different sheets or even if you just want to enter data into multiple areas within the same sheet, a second window can help. This way, you’ll avoid having to jump back and forth. To this, go back to the View tab where you’ll see an option to open a New Window:
From here, you can move your new window onto another monitor or you can just do a split-screen if you only have one. With Windows 10, you can snap windows to the left or right-side of your monitor, making it easy to see two Excel windows at once.
Got any other tips? Submit yours!
Have any tips of your own that use that improve your data-entry experience in Excel? Feel free to email [email protected] and let us know what you’ve found helpful and if we use it in an updated list, we’ll credit you.
If you liked this post on how to speed up data entry 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.
Last year, Excel released some updates including the unveiling of XLOOKUP as well as XMATCH. In this post, I’ll show you how to use the XMATCH function and also why you may not have a need for it.
For this example, I’m going to use a list of the stocks with the largest market caps on the U.S. exchanges as of Feb.7, 2020. Here’s what my data looks like:
XMATCH can achieve the same results as MATCH does when looking for data, but if you wanted the same functionality you could just use MATCH. Instead, let’s start by looking at some of the other things that Microsoft claims XMATCH can do.
XMATCH is not a suitable COUNTIF replacement
One of the things that XMATCH can supposedly do is when you’re looking up numbers, it will count the number of times that values fall above or below a threshold. For this example, we will look at the number of stocks on this list with market caps of more than $1 trillion.
To do this, you would use $1,000,000,000,000 as your lookup value and set the third argument of the function, called match mode, to 1, which looks at an exact match or the next larger item. Here’s how the formula looks like:
Where L6 is the cell that has the number that XMATCH will search for (1,000,000). This formula correctly gives me five matches that are more than $1 trillion that appear on the list. However, if I include the header, the results change:
This leads me to believe that it’s still looking for the closest match and not really counting the number of values that meet the criteria. And indeed, when I changed some of the market cap numbers so that they were more than $1 trillion, XMATCH didn’t compute them correctly since they weren’t in descending order. I’m assuming what Microsoft is implying with XMATCH is that if your data is sorted in ascending order, it would be able to tell you where the smallest value is that meets your criteria. For example, The sixth row in the data set was $1.02 trillion and that was the lowest entry that was more than $1 trillion. Technically, if the data was in descending order then everything above that will be more than $1 trillion.
However, that’s very different from actually counting the numbers over that threshold. And that’s why COUNTIF is still vastly superior to XMATCH. Here’s how the two functions worked when I added four additional entries (not in order) of more than $1 trillion, bringing my tally to nine:
In the COUNTIF function, it still correctly counted nine instances where there was more than $1 trillion on the list. XMATCH, meanwhile, continued to point to the sixth row.
These issues are confirmed when we look at the number of values below $1 trillion:
The -1 argument in match mode is the opposite of 1, and it looks at the exact match or next smaller item. However, the results, as you can see, were very different and not what I would have expected. It appeared to point me to the closest number to $1 trillion without going over. COUNTIF, meanwhile, continued to correctly count the number of items that were below $1 trillion. And with 1,000 items in my data set, it makes sense that 991 were below if nine were above the threshold. Unfortunately, that same logic doesn’t work with XMATCH.
As a replacement for COUNTIF, XMATCH gets a fail as it’s clear that it’s not really counting the number of instances. Only under very specific circumstances would the function do that, such as if the data was in descending order. And even then, you’d still need to do a calculation for the header or if you’re looking at the number of items below a threshold. It’s more trouble than it’s worth and COUNTIF has the benefit of also being available in older versions of Excel, even going back to Excel 2000. That’s important if you’ll ever need to work on an older version of Excel.
Using XMATCH to search for text is not any better than using MATCH
If you’re using XMATCH for matching text, it won’t be able to count the instances but you can use it to find the first instance of it. Some companies trade under multiple tickers and you’ll notice Google’s parent company Alphabet shows up twice in this list. Here’s what happens when I try to use the XMATCH function to find the first instance:
I’m using a question mark after the text as that’s what Microsoft instructs users to do when looking for partial matches. However, if I ignore that advice and use an asterisk and specify I’m using a wildcard match, then it appears to fix the issue:
You may be wondering how the regular MATCH function did:
Besides changing the last argument, the functions are nearly identical in how they’re used to find partial matches.
Let’s compare how the functions work when we’re looking at exact matches. For this example, I renamed the multiple Alphabet names so that they only spell out Alphabet with no mention of share classes, e.g. so they’re exactly the same. Here’s how XMATCH does on a simple match calculation:
Here again, there’s little distinction between the two functions.
Microsoft also advertises that XMATCH can be used in an INDEX/MATCH combination, but even that seems kind of pointless.
Using XMATCH with INDEX makes little sense
Let’s use these functions to grab the intersect between the company name and its dividend yield. The name is in column B while the dividend yield is in column G. All the headers are in the first row. Here’s how the formula looks like with the use of XMATCH:
In this example, XMATCH correctly pulled the right percentage for Visa’s dividend yield of 0.59%.
That would be really, really cool if the MATCH function didn’t already do the exact same thing. By getting rid of the X in the XMATCH function, thus making it just a MATCH function, and adding a 0 for the third argument, I get the same exact result:
XMATCH doesn’t improve upon anything when it involves the INDEX and MATCH combination. We’re talking a slight change to the syntax, that’s about it. And again, from a functionality point of view, there’s just no reason to swap a new function in when the existing one works just as well, especially since there’s no backwards compatibility on older versions of Excel for XMATCH.
What XMATCH can do well
Everything that the MATCH function can do, XMATCH can do as well. That’s the good news. There is, however, one thing that XMATCH can do better, and that’s look for data in the reverse order. Here’s a simple example of how both functions work when we’re looking for the first value that contains the word Alphabet:
Both functions correctly yield the same results. Again, the change here is mainly to do with syntax. Under the new XMATCH, if I set the third argument (match mode) to 0 and look for an exact match, I’ll get an error. But if I set it to 2, which is wildcard character match, it will produce the correct result: Alphabet, which first shows up on the fifth row. However, it’s easy to see how this will confuse users who are familiar with MATCH and just use 0 for the third argument, which will also produce the correct result in this case. This is another example of where the syntax has gotten more complicated and not given the user any additional advantage.
The one exception to that, however, is if you want to do a search in the reverse order. MATCH currently will go from the first row and work its way down. Once there’s a match, it will stop there. Here’s how the XMATCH function performs when we’re doing a last-to-first search, as indicated in the fourth argument where the value is -1:
This time XMATCH does correctly pull the sixth row, which is where Alphabet would first show up if we were looking from the bottom and moving up. MATCH, unfortunately, doesn’t have the option to do that and a user would have to rearrange their data to get the same result.
The reverse-order search is the only advantage I can see from testing out XMATCH. Unfortunately, the new function doesn’t add anything significantly new and at worse, it can lead to incorrect results, especially if you’re planning to use it to replace COUNTIF.
Why learning new functions may not be worthwhile, at least not initially
It’s possible that in future updates the XMATCH function will work better but for now, there’s not a whole lot of reason to use it. One of the biggest disadvantages of new functions is that they won’t be helpful to you if you’re working on an older file. It’s not uncommon for people to be working on Excel versions that are more than 10 years old. Not everyone needs the latest-and-greatest version, and mastering a new function may not prove to be worthwhile, especially when older functions work just as well, if not better.
If you liked this post on how to use XMATCH, 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’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:
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:
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:
=C5/D5-1
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:
=IF(OR(D5=0,D5=””),””,C5/D5-1)
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:
=IFERROR(C5/D5-1,””)
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:
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:
=AND(F5<0.2,F5>-0.2)
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:
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:
=$C5<1000
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:
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):
=AND(G5>-0.5,G5<0.5)
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:
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…
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:
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:
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.
