tips & tricks – Page 2 – HowtoExcel.net

7 Ways to Fix a Slow Spreadsheet

February 13, 2021

by howtoexcel with No Comment indirect intermediate power query tips & tricks

Is your spreadsheet running slow or constantly freezing and crashing on you? There are many ways you can make it quicker and more efficient. Below, I’ll cover seven different ways you can fix some of the more common reasons your file may not running well.

1. Turn off calculations for the workbook

The first thing you can do is to turn off calculations. If you don’t need your data to constantly be updating, then the easiest way to ease the load of your spreadsheet is to make sure those formulas don’t keep calculating. To do this, you can go to the Formulas tab and under the Calculation group, select Calculation Options and choose Manual:

How to select manual calculations in Excel.

You can always turn the formulas back on and use F9 to do a force calculation. But if you don’t want to re-calculate everything, a quick trick you can do is to use Find and Replace and replace the equals sign (“=”) with equals. It seems redundant but doing this will refresh your formulas and recalculate the range you selected. This can be an easier alternative to enabling calculations for everything and then having to wait for every calculation to update.

The danger with turning off calculations is that you could end up looking at data that hasn’t been updated and potentially incorrect values as a result of that. This is why you’ll always want to be careful when turning off calculations for an entire workbook.

2. Turn off calculations for individual worksheets

If you don’t want to turn off everything, you can turn off the calculations for specific worksheets. Although there isn’t a way from the calculation options to specify which sheets to turn off, you can do this through a macro.

To do this, go into visual basic (ALT+F11) and go into the ThisWorkbook object:

We’ll want to turn off calculations when the workbook is first opened so you don’t have to remember to do it later. The initial subprocedure looks as follows:

Private Sub Workbook_Open()

End Sub

To turn off a calculation, you just need a single line of code:

Worksheets(“Sheet1”).EnableCalculation = False

Then entire subprocedure look as follows:

Private Sub Workbook_Open()

Worksheets(“Sheet1”).EnableCalculation = False

End Sub

Where Sheet1 is the name of the sheet that you want to turn calculations off for. And if you want to turn them back on, you change the value from False to True.

This isn’t the easiest option, especially if you aren’t familiar with macros or don’t want to worry about coding anything. But with VBA you can turn off the calculations and then use a button to turn them back on.

3. Separate the data into multiple tabs

If you have a data set that includes multiple years worth of data, you may want to consider breaking it up. Have one tab for the current year, one for the previous year, and so on.

Using the INDIRECT function, you can refer to different worksheets in your file. By naming a header the same name as a tab (e.g. ‘2020’), you can make your formulas dynamic and pointing to a different worksheet as opposed to one very large tab. Looking up 10,000 rows in one tab versus 50,000 rows in a massive collection of all your data can significantly improve the time it takes for your calculations to run.

4. Separate data into multiple files

A more drastic move than breaking up data into different tabs is moving them onto completely different files. If you’re carrying lots of old data into one big spreadsheet, consider archiving some of it and saving it in another file. Unless you really need access to the old data all the time, it might make a lot of sense to break up your files and to keep your current version as lean as possible. You could even have your data on one file and a separate file for your reporting while using PowerQuery to create a snapshot of your data and simply refreshing it as you need it.

5. Use COUNTA and INDIRECT to limit the scope of your calculations

The INDIRECT function can also help you to ensure your formulas don’t include too many rows or columns. If you select an entire column to run your calculations on rather than just the first couple hundred rows that actually have data in them is another way you can slow down your spreadsheet.

To do this, you can use the COUNTA function on a range to determine how many cells have values in them. The formula is a simple one that you can run on an entire column:

=COUNTA(“A:A”)

Then, using the INDIRECT function, you can write a formula that does something like this:

=SUM(INDIRECT(“A1:A”&B1))

B1 in the example above contains the COUNTA formula and will give you the total number of rows that are used. Rather than doing this:

=SUM(A:A)

and selecting everything in column A to sum up, you can shrink your range down using a combination of the INDIRECT and the COUNTA function. This can be a time-saver when you’re dealing with complex calculations, especially if there are thousands of rows of data.

6. Minimize formulas and utilize pivot tables where possible

Another way to cut down on resources is to make use of pivot tables. Pivot Tables are more efficient than formulas and using them can help make your spreadsheet run smoother. The one drawback is that they’re not as flexible as formulas are. Refreshing the data also takes seconds and you don’t have to worry about turning calculations off.

7. Make sure your worksheets aren’t too big

Large files can take long to open and they freeze up a lot. A common problem I see is that worksheets are sometimes thousands of rows long even though there’s nothing there. This can happen if you download a large data set into a sheet and clear it out later. And although those rows may no longer be occupied with data, they’re still technically taking up space. This can add several MB onto a file and make it a lot more difficult to run.

How can you spot this issue? An easy way is to cycle through the tabs and click CTRL+END. You’ll be taken to the last cell in your sheet. If that takes you far beyond your last row or column of data, then that tells you your sheet is using up more data than it needs to. What you can do is delete all the cells in between the last cell (using CTRL+END) and the last cell that actually contains information in it. Then, save the file. You should see your file come down in size. Do this for each worksheet that has this issue. Even though the cells appear to be empty, they can be making your file unnecessarily large.

If you liked this post on 7 ways to fix a slow spreadsheet please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

How to Extend and Increment Formulas

January 31, 2021

by howtoexcel with No Comment functions intermediate tips & tricks

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:

Using the LARGE function to pull the top 10 amounts in a data set.

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:

Using the LARGE and ROW functions to pull the largest values in a data set.

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.

Hate Merged Cells? Here’s an Easy Alternative!

January 23, 2021

by howtoexcel with No Comment formatting intermediate tips & tricks

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:

Format cells menu selecting 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:

Selecting a cell when text is aligned across multiple cells.

And if I do that, it takes over the center alignment and pushes back the other text into cell A1:

Entering data in a cell that was already center aligned across multiple cells.

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.

Deleting a Formula in Excel? Do This First

January 7, 2021

by howtoexcel with No Comment beginner errors tips & tricks

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:

Spreadsheet that calculates the total by multiplying price by quantity.

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:

Arrows showing multiple dependent cells.

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:

Go to box showing where the dependent cell is located.

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:

Dependent cell that links to another sheet.

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.

How to Create a Time Log in Excel

December 19, 2020

by howtoexcel with No Comment data entry import intermediate power query tips & tricks

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:

=IF(COUNTA(A:A)<(3+(ROW(C2)-2)2),””,INDEX(A:A,3+(ROW(C2)-2)2,1))

For column D, the formula uses row 4 instead of row 3:

=IF(COUNTA(A:A)<(4+(ROW(C2)-2)2),””,INDEX(A:A,4+(ROW(C2)-2)2,1))

And for column E:

=IF(COUNTA(A:A)<(5+(ROW(C2)-2)2),””,INDEX(A:A,5+(ROW(C2)-2)2,1))

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.

Excel Automation: 10 Tasks You Can Automate Today!

November 29, 2020

by howtoexcel with No Comment functions intermediate macros tips & tricks vba

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.

Check out this post to learn about conditional formatting and how you can apply it to your spreadsheet.

6. Updating other workbooks and sheets

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.

Calculate Hours and Minutes Between Two Times

November 21, 2020

by howtoexcel with No Comment dates intermediate tips & tricks

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:

Time difference in hours and minutes and seconds.

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:

Time difference in days, hours and 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.

2 Excel Functions That Can Eliminate the Need for an Amortization Table

November 17, 2020

by howtoexcel with No Comment accounting functions intermediate tips & tricks

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.

How to Make a Line Break in Excel

November 9, 2020

by howtoexcel with No Comment data entry formatting tips & tricks

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:

Wrapped text that's long enough horizontally.

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:

Wrapped text that is expanded vertically rather than horizontally.

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.

How to Do a Vlookup and Extract Multiple Columns

August 28, 2020

by howtoexcel with No Comment arrays tips & tricks vlookup

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:

Multiple vlookup formulas extracting data.

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:

Multiple vlookup formulas versus just one.

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:

Vlookup formula within the transpose function.

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.