When you have created formulas in Excel, you may want to extend and increment them down to other cells. In many cases, you can just drag down a cell that contains the formula and pull it down so that it automatically gets copied. However, there’s a problem if you need to increment a formula or number in an argument. Below, I’ll show you an example of this and how you can address it in multiple scenarios.
Let’s start with just pulling the largest value in a data set. Using the LARGE function, you can grab the largest value with a formula of =LARGE(A:A,1) — that will give you the largest value in column A. However, you’ll notice quickly that just copying this formula down won’t work as expected, it’ll keep copying the 1 in the second argument down; you will continue extracting the largest value. Sometimes Excel just isn’t good at detecting patterns. But there are ways you can adjust the formula to ensure your formula is incrementing correctly.
Option 1: Using an Additional Column
In a separate column, you could list the numbers 1 until 100 or whatever range you needed to and reference those. In the example below, I can pull the largest 10 items using the LARGE function and a referencing the cells to the left:
The problem with this approach is that it isn’t always practical within a spreadsheet, especially if you don’t have room to spare and don’t want to be creating extra columns for the sake of what should be a simple calculation. It may also be difficult to determine what the upper limit should be. That’s where the second option is going to be a better one.
Option 2: Using the ROW Function
There’s an easy way to adjust the above calculation so that you don’t need to reference the actual number, nor do you need to manually enter it. The trick is to use the ROW function. It returns the row value of a specified cell. If I entered =ROW(A20), it would return a value of 20 for the row value. You don’t need to enter an argument and using =ROW() will just return the row of the cell that contains the formula. The function is particularly useful when you want to increment values because as you drag it down, the results will increase by one each time.
In calculating the largest values, we’ll use the ROW function as the argument to determine which value we want, whether it’s the fifth largest or 20th largest number. Here’s how the initial formula would look like, assuming we wanted to start with the largest value:
=LARGE(D:D,ROW(A1))
In the above formula, the calculation will return the largest value since ROW(A1) will return a value of 1. Now, if the formula is copied down, here’s what it will look like:
By copying down the formula, ROW(A1) changed to reflect the next row. Excel does a better job of detecting patterns when they’re part of a function as opposed to just standalone numbers. Using the ROW function eliminates the need for the extra column. And as you’ll notice, the results are the same as in the earlier example.
This is just one example and there are many others where you can use the ROW() function to your advantage and increment values down. If you need to increment as you’re going to the right instead of down, then you’ll want to use the COLUMN function, which effectively does the same as the ROW function, only it’ll pull the column number that a cell is in.
If you liked this post on how to extend and increment formulas 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.
Merged cells are great for presentation purposes but not so ideal when you’re creating a model or a complex spreadsheet. It can make copying cells or formulas painful as you can get an error or unintended results when doing so. However, there’s a way that you can achieve the same result as a merged cell without actually having to merge anything.
Align cells rather than merge them
When merging cells, you would select the cells you want to merge, and then click on the following button:
What happens in that case is that the cells you selected now become one. In the below example, if I click on Merge & Center, the text would get spread across multiple cells:
And become this:
By doing so, I can no longer enter a value individually in cells B1:F1 as the merged cell effectively takes over. If you never plan to copy over these cells then that’s fine but if you do, then you could end up with some annoying errors along the way.
However, there’s an easier, less intrusive way of accomplishing the same result. I would still select cells A1:F1 but instead of using the Merge & Center button I’ll go into the cell formatting menu (CTRL+1) which will give me the following options:
From here, I’ll adjust the Horizontal Alignment and select Center Across Selection:
Now, my text is spread across those cells but it isn’t merged, it only looks that way. For example, I can still click individually on cell B1 and enter data there:
And if I do that, it takes over the center alignment and pushes back the other text into cell A1:
The advantage of using an alignment rather than merging cell is this avoids the errors that can come when you’re modifying or copying data and you get notifications that the cells are not the same size.
By using the horizontal alignment, you have the flexibility of making your text appear as if you’ve merged it across multiple cells without you needing to unmerge data later on if you want to change the text or add data.
If you liked this post on an alternative to merged cells 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.
When you’re dealing with complex spreadsheets in Excel, it can sometimes be difficult to tell which cells are safe to delete and which ones you need to keep to ensure everything is working properly. Even cells that look empty could contain formulas. And deleting them can cause problems and wreak your spreadsheet. Before you delete a formula, there’s one thing you can do to prevent that mistake:
Check for dependent cells
If you’re not sure if a cell is okay to delete and if it has any other cells that depend on it, you can check for dependents. Before deleting a cell, you can click on CTRL + ] which will highlight any cells that use the active cell in a formula (on the current sheet). Here’s a sample spreadsheet that lists price, quantity, and multiples them to get to a total price:
The formula in column D multiples the value in B by C. So that means the value in D depends on the values in C and B (the exception is the subtotal, which depends on the values above it). If I select cell C2 and click on CTRL+], it takes me to cell D2:
If there is more than one cell that depends on the active cell, then Excel will highlight all of them.
What if there aren’t any dependent cells? In that case, you’ll get the following message:
If you get this message, that means you’re safe to delete the current cell as nothing in the current sheet links to it. However, the one limitation of using the shortcut is that it may not be easy to see all the cells that depend on that one cell. It also won’t tell you if there is a cell on another sheet that uses it.
What you can do is use the Trace Dependents button in Excel, which is on the Formulas tab:
By clicking on this button, arrows will now show up telling me exactly where the dependent cells are:
In this situation, the arrow clearly shows an arrow pointing to cell D2. Let’s say I also use the cell in a formula in some place far off in a the same sheet:
Another line will point to the other cell. If you have a large data model that goes on for many rows and columns, it may not be obvious where the dependent cells are if you use the shortcut key. Using the shortcut can be helpful as a quick check but if you actually want to see all the cells that use the active cell, you’re better off clicking the Trace Dependents button.
Next, let’s go to the subtotal. Here, let’s assume I’m using this total somewhere on another sheet. Using CTRL+] won’t help me much in this case as it will tell me no cells were found (assuming no cells on the current sheet link to it). But if I click on Trace Dependents, it will show that there is a dependent cell on another sheet:
If you double-click on the dotted line (the portion that’s within the cell), the following box will pop up:
This tells us that there is a dependent cell on Sheet2, cell B1. I can go there manually or I can click on the selection and then press OK. Then it will take me directly to the cell:
This isn’t practical on a wide scale as you would have to go one by one and you could have arrows going all over the place. But if you’re not sure about a certain cell, using the Trace Dependents button can be a quick way to see if it’s safe to delete the cell.
If you liked this post on 1 mistake to avoid when deleting formulas 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.
Are you looking for an easy way to log and track your time in Excel? Below, I’ll show you how you can keep track of the time you spend on tasks without the need for a complicated template or to even open up Excel every time to enter in your time. With a combination of Excel and Notepad, you have all the tools you need to quickly and easily track your time and create a log in Excel.
An easy trick to turn Notepad into a log
To make the process of logging time easy, you probably don’t want to have to open up an Excel spreadsheet each time. There’s an easier way to do so and that’s by using Notepad. Open up a new instance of Notepad and write the following in the first line:
.LOG
Save the file as whatever you want, and then close it. Open it up again and you’ll notice there is now a timestamp when you open the file. Because you entered .LOG at the start of the Notepad file, it will now automatically create a timestamp each and every time that you open the file.
Now, when you’re working on a task, just enter in some text, such as “working on Excel,” click save, and close the file. Now, when you’re switching over to another task or want to say that you’ve finished the task, open up the text file again and enter in a new entry. You probably don’t need to say that you’ve ended a task since the start of a new task would effectively tell you that the previous one is over.
The key thing to remember when you’re logging your tasks in Notepad is that you’ll want to save the file once you’ve made an entry, and then close it out. A good place to store the file might be online or on a shared folder, somewhere that you can access it from any computer and that you can easily update from wherever you are. As you keep adding to the log, you’re essentially creating a database of all your entries.
You can create multiple log files depending on what you’re tracking or you can just keep one big list in a single text file. Either way, once you’ve made some entries, what you can do is now extract that time log in Excel, which brings us to the next step:
Pulling the data into Excel
The text file, while useful, isn’t going to be terribly helpful if you want to easily see the time you’ve spent on a given task. This is where Excel can be incredibly useful. To get the information into Excel, go onto the Data tab and import data using the From Text/CSV button.
You can leave the default settings and Load the data as is as it’ll likely leave all your text entries in vertical form, which will still work for our purposes. Here’s a sample of what my log file looks like after importing it into Excel:
If you’re using one of the newer versions of Excel that includes PowerQuery, a connection is created when you import the text file into your spreadsheet. This prevents you from having to re-import the file manually each time to check for changes. You only need to refresh the data and it will pull in the changes for you.
And if you make additional entries to your text file, save it, and refresh the data in the spreadsheet and it will update. Just simply right-click on one of the entries in column A, select Refresh, and the data will update from the file — as long as it remains saved in the same place.
If, after an hour I make another entry to make log file and click on update in the Excel file, the information is up-to-date without having to initiate another import process:
This is where Excel is very powerful and effective in making it easy to pull data from another file. However, the data isn’t in a form that’s terribly useful to us in the form that it’s in now. Let’s move on to the next part: setting up the template in Excel so that the time log will be a lot more user friendly.
Creating a template to populate the information correctly
The data is in column A, and what I’ll do is create headers in columns C:F for the Start Time, the Task itself, the End Time, and the Duration (in minutes). Here’s what that looks like:
Now, I’ll need to enter in formulas to populate all those fields. The start time field will initially pull from the third row in column A, and then it will grab every second row after that. So let’s start with building out that logic.
I’ll start with using an INDEX() formula to pull a value from column A. Since there’s only one column I’ll be extracting data from, the key argument is going to be the row number. The third row is where my first entry is, so for the row number I’ll start with the number three. Here’s what my formula looks like thus far:
=INDEX(A:A,3,1)
I select row 3 and column 1. This will only work for the first value. I need to adjust the formula so that it will automatically adjust based on which row I’m on, so that it knows to take either the first time entry, the second, the third, and so on. The ROW() function is helpful in this case because it will return the row number of the current cell. And since my first entry in the table will be on the second row, I’ll want to remove the first two rows. My row calculation looks like this right now:
3+ROW(C2)-2
For the first entry (on the second row), this will evaluate out to 3, since ROW(C2) will equal 2 and it will minus 2 from that. This still works for the first entry, but if I were to copy this formula down it would not give me the correct result for other entries. For instance, in row 3, the formula would be as follows:
3+ROW(C3)-2 this would evaluate to 3+(3)-2 = 4
But row 4 contains my task description, not the next timestamp. I need to double with each row I go down. I need to adjust my formula for the row calculation back in C2 to be as follows:
3+(ROW(C2)-2)*2
Now, the row number minus 2 will then multiply by 2. If I copy this formula down to cell C3, it’ll look as follows:
3+(ROW(C3)-2)*2 : this would evaluate to 3+(3-2)*2 = 3+(1)*2 = 5
This returns row 5, which is the next timestamp in column A. If I copy the formula down to row 4, then it will return the 7th item in the column, which is again the next timestamp. Now that the formula is correctly returning each odd-numbered row, I can use this formula for the template I’ve created. My full formula in column C2 looks as follows:
=INDEX(A:A,3+(ROW(C2)-2)*2,1)
This will work not only for the initial timestamp but it will also extract entries that come after it. All you need to do is copy the formula down.
I can replicate this for the Task field in column D. The only change I need to make is to use row 4 as my starting point rather than 3. And so my formula for the task column looks as follows:
=INDEX(A:A,4+(ROW(C2)-2)*2,1)
For the end time, I’ll use row 5 as my starting point. The end of one task will be the same as the start of the next task. And then all that’s left is to calculate the task duration in column F. The calculate the difference in times, I’ll start by taking the end time and subtracting the start time. However, this will give me a decimal that isn’t very easy to interpret:
The reason is that Excel converts this into a fraction of a day. A two-minute interval is less than 1% of the 1,440 minutes that are in each day, which is why the number is so low. To convert the duration into hours I can multiply it by 24, and then the number changes to 0.033, which is the fraction of an hour that two minutes represents. But if I want to go further and convert this into total minutes, I’ll multiply this again by a factor of 60. Now my formula looks as follows:
=(E2-C2)*24*60
Now, after rounding off the decimal points, my duration calculation in column F correctly gives me the number of minutes between the start and end time of a task:
The table is now set up and you can just copy these formulas down to accommodate more entries. You’ll end up with a series of zeroes if there’s not enough data in column A. If you want a cleaner solution, what you can do is use the COUNTA() function to determine the number of rows that are in column A and determine whether to apply a formula or not. For instance, in my example, my data goes until the 8th row and so my formulas look fine for the first two entries but after that, there is no end time for the third task and the subsequent entries are full of zeroes:
It’s not a terribly elegant solution at this point. To get around this, I’ll create a rule for each column to say that if there is no entry, it will be blank. For the start time, I’ll add the following to the beginning of the formula:
IF(COUNTA(A:A)<(3+(ROW(C2)-2)*2),””
This will check if there are enough rows in column A to extract a value for the current cell. If not, the value will be blank. Here’s how the full formula looks in cell C2:
For the duration calculation, I will check to make sure there are values in both the start and end time, otherwise, the value will be blank:
=IF(OR(C2=””,E2=””),””,(E2-C2)*24*60)
With these formulas now set up, I can copy them down hundreds of rows down if I want and they won’t result in a series of zeroes or errors:
The data in Excel will now auto-populate as I add more entries to the time log and at the same time it won’t be an eyesore if there is incomplete data.
If you liked this post on how to create a time log 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.
Spreadsheets can allow you to analyze data and create reports efficiently. But sometimes the tasks that are involved can be difficult or appear to be time-consuming. The good news is that there’s a lot of automation you can achieve in Excel, and it isn’t always necessary to know how to code in order to do so. Below, I’ll show you 10 types of tasks that you can automate in Excel either on your own or with our help.
1. Cleaning and parsing data
One of the more challenging things in Excel is when you’re dealing with a dataset that may not be easy to manipulate. For instance, if you’ve got text mixed in with numbers or dates that aren’t in the right format, Excel may not interpret or recognize the data properly. But there are many formulas that can help you with that. Rather than manually fixing the data, you can use functions like TRIM, CLEAN, LEFT, MID, and RIGHT to extract what you need while also getting rid of extra, unnecessary spaces and other characters.
If you’re looking for more of a walkthrough of the process, there’s a detailed explanation in this post of how to parse data.
Through the use of formulas, you can save hours that you might otherwise spend trying to clean up your spreadsheet. And the best part is that once you’ve set it up, you can re-use the formulas as you add more data. You don’t need to use macros or complicated coding to clean up; a well-structured template can be enough to do the job for you.
2. Creating simple reports
One of the best features of using Excel is that once you’ve entered data into a spreadsheet, it’s even easier to create a report from it. One example is through the use of a pivot table, where through just a few clicks you can easily summarize your data and split it along different categories. Slicers can make filtering and summarizing data even easier in pivot tables, especially for users who aren’t very familiar with Excel. Forget any manual work here; just a few clicks and you’ve got a report that can quickly summarize information in a table for you!
Alternatively, you can also insert charts easily and Excel will try and select the best one based on your data set. There’s also lots of formatting you can apply to charts so that they have the look and feel that you’re after. And once you’ve got a look that works, you can re-use it over and over again.
3. Creating dynamic dashboards
Dashboards are incredibly popular but they can be complex to set up. Then there’s also the challenge of updating it and making sure the data is up-to-date. It can easily take you hours every time to make sure the information is accurate.
However, in this post, I show you how to create a dynamic dashboard that not only won’t take you hours but that will automatically update as you add data to it. And then, you end up with a report that looks great to send to management to easily review and update.
4. Routine data entry
One of the biggest headaches people can face when using spreadsheets is when they hard-code calculations. A hard-coded calculation is where you don’t reference any cells and just put the result in the cell; it can make it nearly impossible to decipher how that number was calculated (especially if you’re not the person who entered the value). If you go to re-calculate it or update it, you could spend a lot of time just trying to figure out the calculation.
However, by using a formula, there’s no ambiguity as to how a value was calculated. Not only does that save you the time of entering in data but it also makes it easy to correct and update the figure. Ideally, you should minimize the number of places you’re manually entering data into. By doing that, you’ll have a much more robust template where your inputs are kept to a minimum which will eliminate the need for a lot of data entry and your other calculated fields will update automatically. This type of automation doesn’t require complex coding and just needs an Excel spreadsheet to be carefully constructed so that it is efficient and makes the most of formulas.
5. Conditional formatting
Oftentimes you’ll want to color-code your data to highlight things you should be paying attention to. If you’ve got an aged accounts receivable schedule, it is useful to highlight which accounts are more than 90 days overdue. You could manually filter the data and highlight all the cells or rows in red that are overdue, but you can just use conditional formatting to do that for you.
Through conditional formatting, you can create rules to determine when a cell or row should be highlighted in red, when you may want it to be in yellow, or when you may just want to hide the text so that you can easily skip over it. For example, hiding zero values can make it easy to focus on more important numbers.
You can apply many different formatting rules and can even put in a hierarchy to determine if you want to keep applying formatting rules or whether you want to stop if a specific criteria is met. Conditional formatting can be complex but it can be a huge time-saver by allowing you to focus on just the items that are important to you. And once you’ve set up the rules, you don’t need to worry about making changes every time you add new data.
If you use multiple workbooks, then another area where you can avoid re-entering data is by linking both workbooks. There are numerous ways that you can do this. One approach involves just linking directly to another worksheet where data will automatically pull from another table.
You can also use the INDIRECT function to reference another worksheet or workbook. Just like with a template, once you set up these formulas and connections, they are there to stay and you can avoid having to manually make changes by yourself.
7. Audit tracking by logging changes
One of the neat features of many Office products is they allow you to track changes that are made. This is normally when you share a workbook with other users. However, through the use of macros, you can have a separate sheet that can tell you which values were changed, when, and by who.
Rather than manually noting these changes or relying on people to make the updates themselves, it doesn’t take much effort through a macro to create a log of what’s been changed.
8. Generating PDFs
One thing many advanced Excel users like to do is to use automation to export reports into PDFs. While there is a way to print to PDF, and it’s particularly easy on the newer versions of Excel, it can be a time-consuming process especially when you need to print out multiple sheets. Here again, with a simple macro, you can auto-generate PDFs and save them in a predefined folder all with the click of a button.
9. Sending emails
Another feature many users like is the ability to use automation to send out emails right from Excel. Through the use of macros, this is also possible. You can create a macro that will enter in the email of the recipient, attach a file, enter the body of the message, and even send the email itself. This can be even set up on a large scale, such as sending out invoices to dozens or hundreds of customers, a process that could easily save you hours worth of work.
10. Just about anything else with VBA
The power of programming in Excel can unlock many different possibilities with what you can automate. Whether it’s using automation to help import data and then manipulating it, creating custom reports, or following a series of complicated steps, there are many tasks in Excel that can be expedited with a few clicks of a button. As long as there’s some logic to the process that you can break into steps, then you can also build that into the code and automate it.
Don’t know where to start? Contact us!
There is significant potential in Excel but not everyone knows how to use automation to make the most of it and to make a spreadsheet as efficient as it can be. You can contact us if you have a certain Excel issue that you need help with or if one of the tasks above has perked your interest and you’d like to learn more. We can help create solutions for you that work efficiently and that can save you many hours, perhaps even days every month.
If you liked this post on 10 Tasks You Can Automate Today, 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.
Do you want to know how to calculate how many hours and minutes have elapsed between two times? Below, I’ll show you how to do that with Excel formulas. The difference won’t be in just fractions of days or hours but real minutes and hours that make it easy to measure. To make this work, however, the first step is entering in the time correctly.
How to enter time in Excel
Excel has a built-in time function called TIME where you can enter the hour, minute, and second. For example, if you wanted to enter a time of 6:30 AM, you could enter a formula of TIME(6,30,0). Alternatively, you could type in 6:30 AM — the key is leaving the space between the time and the AM/PM indicator.
However, the one clear limitation here is that this won’t help you if you want to calculate the hours and minutes between times that span more than just one day. By using the TIME function, or entering just the hours and minutes, Excel is always going to assume you’re talking about the current day.
The best way to enter in time is to also factor in the date. Depending on your regional settings you might enter this differently, but this is how I’d enter a time of 8:00 PM on Nov. 20 on my computer:
2020-11-20 8:00 PM
I can also use a 24-hour clock and type in the following:
2020-11-20 20:00
Either way, Excel knows what time I’m talking about. If you always want to return the current time, you can use the NOW function.
For the start time, I’ll set it to the start of the year: 2020-01-01 0:00
Calculating the difference
Just using the minus operator, I can get the difference between these two dates as follows:
By default, Excel will return the number of days, including the fractional days as well. To convert days and calculate the hours instead, we will multiply the difference by 24, since that’s how many hours are in a day. That gives us the following:
That’s 7,796 hours between those two dates and times. It’s a nice round number but what if we changed it so that the end date was at 8:30 pm, or 20:30, this is what the updated calculation would look like:
Now I’ve got that residual 0.50 which indicates half an hour. But I want minutes, not fractions of an hour. The easiest way to do this is to create one calculation for hours, and another for minutes. Then, afterwards, you can concatenate them together. To get the total hours, I’ll adjust my formula to include the ROUNDDOWN function so that it does not include the 0.5. It looks something like this:
=ROUNDDOWN(datedifference*24,0)
Where datedifference is that raw calculation between the two dates and times. In my calculation, I’m still multiplying the time difference by 24 to get to hours, and then I round that to 0 spots, which is indicated by the 0 in the second argument. Now it will only show 7,796.00 for hours.
To calculate the number of minutes, I’ll need to multiply the datedifference by 24 and then again by 60, to convert the difference into minutes. This is what my calculations look like thus far:
My hours are nicely rounded but my minutes include the total minutes, which is not what I want. I only want the minutes that are left over after the hours are factored out. Here I can make use of the MOD function which will tell me the remainder after division. I’ll adjust the minute calculation to calculate the remainder after I’ve divided the total minutes by 60. This will determine what’s left over after pulling out full 60-minute hours, which is that residual 0.50 that I’m after. Here’s what this formula looks like:
=MOD(datedifference*24*60,60)
That gives me the following result for minutes:
Now I get a nice and round 30 minutes. There is the potential that I can also get partial minutes if I have seconds in my calculation. This could be the case if I’m using the NOW function. To correct for this, I can again use the ROUNDDOWN function as I did for hours.
However, let’s assume that you also want to track seconds. We can do that as well. I’ll break out another column for seconds. There, I’ll multiply the difference by another factor of 60, to get the following:
I added 25 seconds to my end date, which is why you’ll notice there’s a slight change in the difference column from this screenshot and the earlier one. Right now, total seconds tells me there are 28,067,425 seconds between these two times. If I want to get the raw number of seconds, then I’ll again use the MOD function and again use 60 as a divisor, since now I want to factor out the minutes:
I now have a clean breakdown between hours, minutes, and seconds between these two times. But if you want to calculate more than just hours between two times, you can also incorporate the number of days as well.
Breakdown of days, hours, minutes, and seconds
If I wanted to take a different approach and break the difference down by days, and then by hours, minutes, and seconds, I’ll first need to break out the days. Since that’s the default calculation for Excel, all I need to do is use the ROUNDDOWN function on the difference. The formula is as follows:
=ROUNDDOWN(difference,0)
And that gives me this:
If I wanted to get the hours that are remaining, what I can do is take the difference, use the MOD function, but this time I’m using a divisor of just 1, since I really only want the decimal place after the full number. Then I’ll multiply that by 24 hours, and again, use the ROUNDDOWN function:
=ROUNDDOWN(MOD(difference,1)*24,0)
Now my hours total looks like this:
I’ve got a nice round 20 hours, which makes sense since 8:00 PM is 20:00 on a 24-hour clock. To calculate the difference in minutes, I can revert back to the earlier calculation where I used the MOD function to determine what’s left over after multiplying the difference by 24 and 60, and then dividing it by 60 minutes:
The seconds calculation will work the same way as well. The only difference in the way to break out hours and days was to adjust the hours calculation to ensure it isn’t taking in the full hour difference, only the residual amount that pertained to the current day.
Now that you’ve got all the chunks broken down between days, hours, minutes, and seconds, you could concatenate that into one large formula, Something like this might work:
=CONCATENATE(daydifference,” Days “,hourdifference,” Hours “,minutedifference,” Minutes “,seconddifference, ” Seconds”)
That produces the following:
For this not to be a messy result, you’ll want to ensure you’re using the ROUNDDOWN function in each of those calculations so that you aren’t keeping any trailing numbers.
If you liked this post on how to calculate hours between two times, 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.
An amortization table is a useful tool when you need to calculate interest payments, principal payments, and to track the balance that’s owed on a loan. However, you don’t have to create a full schedule to get these values and below I’ll show you two functions that can get you that information quickly and easily. First, let’s start with what a typical amortization schedule looks like.
Creating the amortization schedule
When you set up an amortization schedule, you’ll track the balances, interest, and principal payments. It often looks something like this:
You could use the table to determine what the balance is at the end of period 10 or to add up all the interest payments up until that point. However, there’s another way to arrive at those totals, and that’s using two functions that are available in the newest version of Excel: CUMIPMT and CUMPRINC.
Using the functions
In the amortization schedule, we can see that the ending balance of the $100,000 loan by the end of period 10 is $85,016.67. We can use the CUMPRINC function get to that total as well. The function takes on the following arguments:
To calculate the cumulative principal payments, I’ll enter the formula with the following arguments:
=CUMPRINC(0.05/12,60,100000,1,10,0)
This gives me a total of -$14,983.36. When added to $100,000, it nets out to a balance of $85,016.64 — within just a few cents of the amount on the amortization table. The function gives you the flexibility to specify which periods you want to extract and so you aren’t limited in just tabulating the totals for the first 10 periods or starting from the beginning. You can start from period 13, or the second year, and so on.
If you want to calculate the total interest payments, then that’s where you can use the CUMIPMT function. It has the same arguments as the CUMPRINC calculation, so the formula will look very similar to what’s above:
=CUMIPMT(0.05/12,60,100000,1,10,0)
This tells me that the cumulative interest payments during the first 10 payment periods is $3,887.87. This matches what I would get by adding the interest payments in my amortization table over the same period, this time to the penny.
Should you use these functions instead of an amortization table?
On older versions of Excel, you won’t have access to these functions but if you’re using Microsoft 365 or Excel 2019, then these functions are available and can potentially serve as replacements for an amortization table. Now, if you need the table for audit purposes it may not be possible for you to do without an amortization table completely. But if you’re only generating the table just to determine how much you’ve spent on interest or what your balance will be at some point in the future, then these functions can certainly replace doing a full-blown amortization table.
If you liked this post on 2 Excel functions that can eliminate the need for an amortization 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 us on Twitter and YouTube.
Are you entering multiple lines of text in Excel and want to break it up into multiple lines? You don’t have to adjust the cell size to do it and below I’ll show you some ways that you can manage cells that contain a lot of text, including how to make a line break in Excel.
Creating a line break
Here’s an example of a cell that could use a line break:
Currently, this bleeds onto where the start of the next cell should be. But rather than adjusting the length of my cell, I can position my cursor right after the period and before the ‘T’, hit ALT+ENTER, and now my cell looks like this:
Please note that if you want to create another line, you can’t just click on the cell and enter ALT+ENTER, you actually need to be inside the cell entering in values. To get into edit mode you can either click into the formula bar with the cell selected or click F2. Then, it’s a matter of selecting where you want to insert the line break. In the above example, the optimal position is just before the start of the second sentence. Then, once you’re there, you’ll click ALT+ENTER to move the following text down a line. You can repeat these steps to create as many lines as you’d like.
When creating an extra line, Excel automatically expands my cell vertically and selects the option to Wrap Text which is on the Home tab:
Using the ALT+ENTER shortcut tells Excel that you want to wrap your text and create a new line, which is what I’ve done in this example. Once wrap text is selected, your data will automatically conform to its cell size; the contents won’t bleed over into adjacent cells. For example, if I shrink my cell size then it no longer goes into the next cell, it just simply doesn’t show up:
If I were to double-click and auto-fit the column, then my cell would expand horizontally to accommodate the contents:
However, if I were to double-click on the row and use auto-fit there, then the row would get larger and then my cell looks as follows:
As you can see, once you’ve enabled Wrap Text, you don’t have to worry about your cell’s values moving into other cells. But at the same time, you may not necessarily want Wrap Text enabled for every cell since there’s the possibility that text gets cut off.
A good benchmark I normally use, especially for headers and where text may span multiple lines is to set the row height to 30. If that’s not enough, then I would at that point look at expanding the cell horizontally.
Another option that you have at your disposal if you want to accommodate a large value of text is to use Merge Cells. Generally, I’m not a big fan of merging cells because it can be problematic with formulas. But if you’re reserving this primarily for headers and text where there won’t be numbers in or near it, then it could be a practical alternative. That being said, I’d still keep this as a last resort.
If you liked this post on how to make a line break 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.
VLOOKUP is a powerful function for extracting data from another sheet. And while most users will use it simply for pulling just one field, it can do a lot more than just that. Below, I’ll show you how you can extract multiple columns from just a single vlookup formula, potentially saving you from having to repeat the same formula over and over when you need more than one field.
Let’s start with the basics
First, I’ll setup a regular vlookup formula and then show you how, with a simple adjustment, you can pull a lot more data into your spreadsheet.
For this example, I’m going to use data from NationMaster, showing the number of vehicles in use by country. In addition to raw numbers, the data set also shows the year-over-year growth and the five-year compounded annual growth rate (CAGR). Here’s what the data looks like in my Excel sheet:
In a normal vlookup formula, you might have something like this setup if you wanted to extract all of the fields:
Cell H4 is where I’ve entered the country name. The vlookup works just fine if you want to pull data from the vehicles column. And if you want to grab the other fields you can just repeat the formula for the YOY% and 5-Year CAGR fields and just change the column number. However, there’s a much easier way to extract all those fields using just one formula.
Modifying the VLOOKUP formula
All I need to do to make this formula accommodate multiple columns is to change the column number. Rather than this:
=VLOOKUP($H$4,$A:$D,2,0)
I’ll enter in this:
=VLOOKUP($H$4,$A:$D,{2,3,4},0)
Using the curly braces, you can specify the different column numbers that you want to extract. Since this is an array formula, on older versions of Excel you may need to enter ALT+SHIFT+ENTER for the calculation to work properly.
Here’s the difference in formulas:
The columns you want to extract also don’t need to be sequential. You can extract columns 2 and 4 rather than all three. All you need to do is separate the column numbers that you want with a comma.
Retrieving the fields vertically instead of horizontally
Depending on how you’ve got your sheet set up, you may prefer for the data to come back in rows rather than columns. This too, is an easy fix.
All that you need to do is wrap your existing formula within the TRANSPOSE function. Here’s what the updated formula looks like:
=TRANSPOSE(VLOOKUP($H$4,$A:$D,{2,3,4},0))
This again is an array formula so you may need to use CTRL+SHIFT+ENTER on older versions of Excel. But doing it will allow you to retrieve the values vertically:
There’s a lot of flexibility with how you can use vlookup to extract data that can allow you to not only simplify your spreadsheet but also result in having to create fewer formulas.
If you liked this post on how to extract multiple columns from vlookup, 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.
There are many ways you can pull and count unique values in Excel, and below, I’ll show you five ways you can do so and when you should use each method.
For this example, I’m going to use a dataset from data.gov relating to consumer complaints about financial products. It’s a large list and by listing the unique values we can see how many different complaints there are and which are the most common ones.
Using the UNIQUE function to extract values
The UNIQUE function isn’t available on all versions of Excel. You can try to type it in as a formula to see if you have access to it. If you’re able to use it then that’s great news, because this is the easiest way to pull unique values.
In my data set, I want to extract all the unique issues customers have had. I can quickly generate a unique list of values by using this function and just selecting the entire range of cells in column D. The formula is as simple as this:
=UNIQUE(D:D)
It returns an array and the unique results are now in the column where I entered the formula (E) and I’ve highlighted the list in yellow:
This gives me the entire list of unique values but it doesn’t tell me how many unique items there are. I could, of course, just use a count function to tally up all the values in column E. But there’s also another, more efficient way to do that. I can enclose the UNIQUE function within the COUNTA function that counts nonblank cells. Here’s what that formula looks like:
=COUNTA(UNIQUE(D:D))
This formula tells me that there are 167 unique values in column D. I don’t even need to first run the UNIQUE function on its own, I can do this all in one cell and quickly get the number of unique values in the list. This is by far the simplest and most elegant way to do this in Excel.
If I wanted to see the number of times a unique value appeared on the list in column D, I could use the following formula:
=COUNTIF(D:D, E2)
Where E2 is the issue that I want to count.
Let’s move on to another approach, one that’s more common in newer Excel versions.
Using the remove duplicates button
Another way that you can pull the unique values from a list is by clicking on the Remove Duplicates button located on the Data tab:
However, the caveat here is that this button will actually remove items from the existing list that aren’t unique. And so what you’ll want to do first is copy your list of values to another column, and then select that column and click on the button.
At that point, you’re back to having to count the number of duplicates in the list. A function like COUNTA could do the job. And then to tally the totals by item would involve using the COUNTIF function again.
Going old school with the advanced filter trick
If you’ve worked on older versions of excel where you didn’t have the luxury of buttons filtering out duplicate values for you, you may have used an advanced filter. How this works is you enter the header into some other adjacent range like so:
Then, click on a value in column D, and then click on the AdvancedFilter button on the Data tab, which looks like this:
The advanced filter should automatically detect the range you’re looking to filter. You’ll then see a pop-up box as follows:
I’ll tick off the Copy to another location button and I’ll select F1 which is where my header in the adjacent range is located.
Ticking off the box for Unique records only is what makes the filter pick up only the unique values, which after clicking on OK will now populate column F:
The advantage of doing it this way is you don’t have to copy a list and then remove duplicates, and essentially the end result is the same. This is the method you may want to use on older versions where you don’t have access to the newer options.
There’s also one another way that you can filter out unique values, and it’s one of the more common approaches I’ve seen.
Using pivot tables
I cringe a little when I see people using pivot tables solely for the purpose of create unique lists. It’s overkill, especially given the methods listed above that do the job just fine. But it does do the job and if you’re wanting to do some sort of analysis, it’s a great way to do it all in one shot.
For example, using my data set I can quickly turn it into a pivot table and not only create a unique list but also do a count by issue and sort it from highest to lowest:
Rather than using COUNTIF, I can just let the pivot table do the counting for me. If you want to calculate the number of unique values, you can again use the COUNTA function. You can use it to count the number of values in the Row Labels section of the pivot table. You’ll of course want to adjust for any headers and grand totals to ensure you aren’t counting too many rows.
Use our FREE Add-in!
Whether you need to count unique values or create a list of them, you can easily do them through our free add-in. You don’t have to worry if you have the right version of Excel and it’ll work with the click of a button:
And that’s just one part of a much larger set of macros and buttons that can simplify your workflow:
When should you use a particular method?
There are many options to choose from here when generating a unique list of values. Here’s a quick breakdown of each method and when you’ll want to use each one:
The HowtoExcel.net Free Add-in. This will simplify many of your tasks beyond just populating and counting unique values.
The UNIQUE function. Outside of the add-in, this should be your go-to option if it’s available on your version of Excel. It’s easy and quick to not only generate a list of unique values but to also count them. The advantage of this method is that it will also auto-refresh if your data changes since it’s a formula. You won’t have to re-run the formula to get a fresh list of unique values.
The Remove Duplicates button. This is the next-best option to use if you aren’t able to use the UNIQUE function. It involves just the click of a button and you just need to remember to copy the list.
The Advanced Filter. Use this if you’re working on an old version of Excel and don’t want to install an add-in.
A Pivot Table. There’s no reason to use this method unless your list of values changes often and you need a quick way to refresh the list. However, if you’re also looking to analyze the data then using a pivot table could quickly jump to the top of this list as it’ll help you sort by unique values and also do calculations very quickly.
If you liked this post on how to extract and count unique values 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.
