Microsoft Excel and VBA Tips
Expand and View all Microsoft Excel and VBA Tips
Creating Line Breaks in an Excel Worksheet Cell
Quick tip on creating line breaks within a single cell in MS Excel.
Here's a quick tip that is an easily forgotten and slightly obscure Excel tip. Hitting Enter in Excel will of course advance you to the next cell. If you are entering text in a cell and you want to create a line break within that cell, use Alt+Enter to create a line break and be able to keep entering text into that one cell.
Here's a quick tip that is an easily forgotten and slightly obscure Excel tip. Hitting Enter in Excel will of course advance you to the next cell. If you are entering text in a cell and you want to create a line break within that cell, use Alt+Enter to create a line break and be able to keep entering text into that one cell.
Knowing When and How to Use VLookup
Recognizing when you have a need that calls for VLookup and knowing how to use it.
References
Creating a list of Unique Values from a Populated Column
References
Sum Values in Column B if Corresponding Value in Column A Matches Criteria for a Range of Rows Using SUMIF
Tip on global Excel function SUMIF to add column B values accross a range of rows if Column A on corresponding rows matches our criteria.
=(SUMIF(Cell_Range_Haystack,"Needle",Cell_Range_Corresponding_Value_In_Another_Column))
=(SUMIF(Cell_Range_Haystack,"Needle",Cell_Range_Corresponding_Value_In_Another_Column))
References
Defining and Editing Cell Range Names
Creating complex spreadsheets more efficiently through the use of Cell Range Names.
References
Creating One Header For All Worksheets in an Excel File
Trick to creating a consistent header/footer for all worksheets in an Excel project.
I have been using advanced Excel functions for years but I recently rediscovered a trick that I had completely forgotten about.
You can change the page setup including margins, headers, and footers for multiple Excel worksheets in the same project/file by holding Ctrl while you select multiple worksheets. After you have the multiple worksheets selected, click on File > Page Setup to make changes that will apply to all selected worksheets.
I have been using advanced Excel functions for years but I recently rediscovered a trick that I had completely forgotten about.
You can change the page setup including margins, headers, and footers for multiple Excel worksheets in the same project/file by holding Ctrl while you select multiple worksheets. After you have the multiple worksheets selected, click on File > Page Setup to make changes that will apply to all selected worksheets.
Printing An Entire Excel Workbook to PDF
Solution for the problem of multiple pdf files being created when trying to print an entire Excel workbook.
Recently I was trying to print an Excel file with about 10 different worksheets/tabs to a single pdf file by selecting the option in Excel to print Entire Workbook instead of the active sheet. I have both PDF995 and PDF Creator installed on my system and I use PDF 995 most often when I am printing something to a pdf file.
The problem I ran into printing multiple worksheets in one Excel file to pdf is that PDF 995 was grouping what seemed to be like an arbitrary number of worksheets into one pdf file and it wanted to create 4 pdf files for my one workbook. This was frustrating because there was no real logic about how the worksheets were being grouped into a pdf file and having multiple pdf files as output to my one Excel file was not a good result.
Next I tried to print the workbook to my rarely used PDF Creator but the same problem occurred. I did not know what the issue was behind this problem so I began searching the web to find others who have encountered this problem and hopefully find their solution.
The art of searching the web is all about how you phrase the search term or question, and this can be easy or challenging depending on the content you are searching for. For this problem, it was a little more challenging to find the right search term. I tried a handful of terms that did not return results related to my problem...
After a few unsuccessful attempts, I came up with the search phrase that brought back the results I was looking for...
The trick was keeping PDF995 out of the search term because the problem was related to Excel, not PDF995. One of the first page results on the last search phrase was this page on pdfforge.org... Can't print entire Excel Workbook as one PDF which explained that for some unknown reason Excel groups the worksheets when outputting them to a print driver.
The article suggested using the Wait/Collect feature in PDF Creator and then Combining the separate files before outputting them as a single pdf file. This worked perfectly and I already had PDF Creator so it just came down to learning about and using features that I hadn't previously worked with.
Background / Problem
Recently I was trying to print an Excel file with about 10 different worksheets/tabs to a single pdf file by selecting the option in Excel to print Entire Workbook instead of the active sheet. I have both PDF995 and PDF Creator installed on my system and I use PDF 995 most often when I am printing something to a pdf file.
The problem I ran into printing multiple worksheets in one Excel file to pdf is that PDF 995 was grouping what seemed to be like an arbitrary number of worksheets into one pdf file and it wanted to create 4 pdf files for my one workbook. This was frustrating because there was no real logic about how the worksheets were being grouped into a pdf file and having multiple pdf files as output to my one Excel file was not a good result.
Next I tried to print the workbook to my rarely used PDF Creator but the same problem occurred. I did not know what the issue was behind this problem so I began searching the web to find others who have encountered this problem and hopefully find their solution.
The art of searching the web is all about how you phrase the search term or question, and this can be easy or challenging depending on the content you are searching for. For this problem, it was a little more challenging to find the right search term. I tried a handful of terms that did not return results related to my problem...
Unsuccessful search terms
- why is pdf995 printing my excel workbook to separate pdf files?
- separate files when printing with pdf995?
Solution
After a few unsuccessful attempts, I came up with the search phrase that brought back the results I was looking for...
- why is my excel workbook print job printing to separate files?
The trick was keeping PDF995 out of the search term because the problem was related to Excel, not PDF995. One of the first page results on the last search phrase was this page on pdfforge.org... Can't print entire Excel Workbook as one PDF which explained that for some unknown reason Excel groups the worksheets when outputting them to a print driver.
The article suggested using the Wait/Collect feature in PDF Creator and then Combining the separate files before outputting them as a single pdf file. This worked perfectly and I already had PDF Creator so it just came down to learning about and using features that I hadn't previously worked with.
Quick Macro To Delete Blank Excel Rows
When copying and pasting data into Excel, its common to find the data rows separated by blank rows. I created a quick solution for this...
When copying and pasting data into MS Excel, its common to find blank rows between the rows of data pasted in the worksheet. This has been a mild irritant to me for many years, but it finally got to me enough to do something about it.
I created a quick VBA function and inserted it in my PERSONAL.XLS project, so it will be available for every spreadsheet I work on. I can now click on Tools > Macro > Macros and select the Delete_Blank_Rows macro to quickly remove the blank rows from any spreadsheet.
Please note that this macro assumes there is data in column A for any all data rows. If you'd like to use make this macro available for quick use in your copy of Excel, feel free to follow the steps below ...
Launch the Visual Basic for Applications (VBA) editor from Microsoft Excel by clicking on Tools > Macro > Visual Basic Editor (or ALT + F11).
Locate your PERSONAL.XLS project (this project is loaded automatically every time you launch Excel). If you have a module in the PERSONAL.XLS project, open it - else create a new module.
Paste the following code in the module:
Sub Delete_Empty_Rows()
' DELETE BLANK ROWS FROM CURRENT SPREADSHEET
' MACRO RECORDED ON 12/13/2008
' W303.COM .:. W303.COM .:. W303.COM
' FIND THE LAST ROW POPULATED WITH DATE IN COLUMN A
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
' INITIALIZE VARIABLE FOR CURRENT ROW
Dim CurrentRow
' LOOP FROM ROW 1 TO LAST ROW OF DATA
For CurrentRow = 1 To LastRow
' USE DO WHILE IN CASE THERE ARE TWO CONSECUTIVE BLANK ROWS
Do While IsEmpty(Cells(CurrentRow, 1))
Rows(CurrentRow & ":" & CurrentRow).Select
Selection.Delete Shift:=xlUp
Loop
Next CurrentRow
End Sub
After you have inserted the code into your PERSONAL.XLS module, save it and exit the editor.
In your Excel spreadsheet, select Tools > Macro > Macros (or ALT + F8). Select Delete_Empty_Rows from the list of available macros and click Run.
I noticed a little bug in the original code that I published yesterday and made the correction. I was previously using an If statement in the For loop, which is fine if there is exactly one blank row between each row of data. If for some reason there are two blank rows of data, the If statement would skip the second row because the code deletes the first blank row, thereby bringing all subsequent rows up one position. However, the CurrentRow variable was still being advanced by one (+1).
There were two quick ways I could think of to resolve this issue. The one I chose keeps evaluating the current row until there is data in cell AN, by using a Do While loop.
The other option that I did not choose was to decrease the current row variable by one (-1) when the conditions of the If statement are met - so the next consecutive row that became the CurrentRow after the deletion would still be evaluated for deletion if it was blank.
The two solutions were both simple, so I flipped a coin and chose the first option of using a Do While loop on the CurrentRow.
When copying and pasting data into MS Excel, its common to find blank rows between the rows of data pasted in the worksheet. This has been a mild irritant to me for many years, but it finally got to me enough to do something about it.
I created a quick VBA function and inserted it in my PERSONAL.XLS project, so it will be available for every spreadsheet I work on. I can now click on Tools > Macro > Macros and select the Delete_Blank_Rows macro to quickly remove the blank rows from any spreadsheet.
Please note that this macro assumes there is data in column A for any all data rows. If you'd like to use make this macro available for quick use in your copy of Excel, feel free to follow the steps below ...
Step 1 - Launch VBA Editor
Launch the Visual Basic for Applications (VBA) editor from Microsoft Excel by clicking on Tools > Macro > Visual Basic Editor (or ALT + F11).
Step 2 - Insert Code Into PERSONAL.XLS Module
Locate your PERSONAL.XLS project (this project is loaded automatically every time you launch Excel). If you have a module in the PERSONAL.XLS project, open it - else create a new module.
Paste the following code in the module:
Sub Delete_Empty_Rows()
' DELETE BLANK ROWS FROM CURRENT SPREADSHEET
' MACRO RECORDED ON 12/13/2008
' W303.COM .:. W303.COM .:. W303.COM
' FIND THE LAST ROW POPULATED WITH DATE IN COLUMN A
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
' INITIALIZE VARIABLE FOR CURRENT ROW
Dim CurrentRow
' LOOP FROM ROW 1 TO LAST ROW OF DATA
For CurrentRow = 1 To LastRow
' USE DO WHILE IN CASE THERE ARE TWO CONSECUTIVE BLANK ROWS
Do While IsEmpty(Cells(CurrentRow, 1))
Rows(CurrentRow & ":" & CurrentRow).Select
Selection.Delete Shift:=xlUp
Loop
Next CurrentRow
End Sub
After you have inserted the code into your PERSONAL.XLS module, save it and exit the editor.
Step 3 - Run Delete_Empty_Rows Macro
In your Excel spreadsheet, select Tools > Macro > Macros (or ALT + F8). Select Delete_Empty_Rows from the list of available macros and click Run.
12/14/08 Update - Minor Change to Code
I noticed a little bug in the original code that I published yesterday and made the correction. I was previously using an If statement in the For loop, which is fine if there is exactly one blank row between each row of data. If for some reason there are two blank rows of data, the If statement would skip the second row because the code deletes the first blank row, thereby bringing all subsequent rows up one position. However, the CurrentRow variable was still being advanced by one (+1).
There were two quick ways I could think of to resolve this issue. The one I chose keeps evaluating the current row until there is data in cell AN, by using a Do While loop.
The other option that I did not choose was to decrease the current row variable by one (-1) when the conditions of the If statement are met - so the next consecutive row that became the CurrentRow after the deletion would still be evaluated for deletion if it was blank.
The two solutions were both simple, so I flipped a coin and chose the first option of using a Do While loop on the CurrentRow.
Using the NPV Function in Excel
Tips on effective use of the NPV function in Microsoft Excel.
Today I thought I would share a quick tip on using the Net Present Value (NPV) function in Microsoft ExcelŽ. While business schools teach a variety of ways to do financial calculations, there is often a lack of focus on Excel, and for fairly good reason. Excel, at lease prior to and maybe including Office '07, has some significant deficiencies when it comes to advanced financial calculations. This can be frustrating considering how commonplace Excel spreadsheets are in the business world.
These challenges certainly don't mean we can't make Excel work for our advanced financial calculations, just that it takes a little extra energy to understand the quirks in the program.
Today I thought I would draw attention to the fact that Excel cannot handle negative cash flows in its NPV calculations. NPV is a common method for making capital expenditure decisions and is growing in popularity in relation to internal rate of return (IRR). Essentially, both of these methods evaluate the value of a series of cash flows. Often, there is a single cash outflow at time zero and a series of positive cash flows that follow.
One example might be purchasing a bond where you pay a certain amount for the bond today and receive coupon payments for a fixed period of time until the bond reaches maturity and you receive the face value. We could expand on this example and say that we purchase a bond for $980 today that will make annual coupon payments of $75 for three years, at which time will receive the face value of $1,000. If our cost of capital, or in this case the return we expected from our investment was 8%, this investment would have an NPV of $7.11.
The positive NPV indicates to us that purchasing this bond would give us more than our required return of 8% and we should make the investment. The cash flows associated with this investment would be -$980 at time zero, $75 at the end of year 1, $75 at the end of year 2, and $1075 at the end of year 3 (the coupon or interest payment for that year plus the bond's face value of $1,000).
On a financial calculator, this is fairly straight-forward as we would enter each of these cash flows in the appropriate time period and set each to a frequency of 1 to get our NPV of $7.11. However, in Excel, we cannot include the negative cash flow at time zero as the NPV function cannot handle negative cash flows. Instead, we would have to calculate the NPV of the positive cash flows and subtract what we paid at time zero from the NPV of those positive cash flows. If we were to do this, the NPV function in Excel would return $987.11 for the positive cash flows by discounting them back to time zero at 8%. In subtracting what we paid for the bond ($980) from the NPV of the positive cash flows ($987.11), we would get the actual NPV of this investment ($7.11).
In a simple example like this one, the shortcomings of the Excel NPV function are not critical. However, in complex capital expenditure analysis projects where the cash flows could net to be positive in some years and negative in others, it becomes a more significant issue. In these more complex situations, we cannot net out the cash flows in each year, but must leave them separate and calculate the NPV of each to determine the difference, or the NPV of the entire project.
I hope this helps some of you out there who may have struggled with why the NPV function in Excel was not returning the answer you expected. Incidentally, we would expect the IRR in our example problem to be just more than our required rate of return (8%) because the NPV was small, yet positive. The IRR for our example problem is 8.28%, which is in line with our expectations. We'll look more at NPV, IRR, and Profitability Index (PI) on another day.
Today I thought I would share a quick tip on using the Net Present Value (NPV) function in Microsoft ExcelŽ. While business schools teach a variety of ways to do financial calculations, there is often a lack of focus on Excel, and for fairly good reason. Excel, at lease prior to and maybe including Office '07, has some significant deficiencies when it comes to advanced financial calculations. This can be frustrating considering how commonplace Excel spreadsheets are in the business world.
These challenges certainly don't mean we can't make Excel work for our advanced financial calculations, just that it takes a little extra energy to understand the quirks in the program.
Today I thought I would draw attention to the fact that Excel cannot handle negative cash flows in its NPV calculations. NPV is a common method for making capital expenditure decisions and is growing in popularity in relation to internal rate of return (IRR). Essentially, both of these methods evaluate the value of a series of cash flows. Often, there is a single cash outflow at time zero and a series of positive cash flows that follow.
One example might be purchasing a bond where you pay a certain amount for the bond today and receive coupon payments for a fixed period of time until the bond reaches maturity and you receive the face value. We could expand on this example and say that we purchase a bond for $980 today that will make annual coupon payments of $75 for three years, at which time will receive the face value of $1,000. If our cost of capital, or in this case the return we expected from our investment was 8%, this investment would have an NPV of $7.11.
The positive NPV indicates to us that purchasing this bond would give us more than our required return of 8% and we should make the investment. The cash flows associated with this investment would be -$980 at time zero, $75 at the end of year 1, $75 at the end of year 2, and $1075 at the end of year 3 (the coupon or interest payment for that year plus the bond's face value of $1,000).
On a financial calculator, this is fairly straight-forward as we would enter each of these cash flows in the appropriate time period and set each to a frequency of 1 to get our NPV of $7.11. However, in Excel, we cannot include the negative cash flow at time zero as the NPV function cannot handle negative cash flows. Instead, we would have to calculate the NPV of the positive cash flows and subtract what we paid at time zero from the NPV of those positive cash flows. If we were to do this, the NPV function in Excel would return $987.11 for the positive cash flows by discounting them back to time zero at 8%. In subtracting what we paid for the bond ($980) from the NPV of the positive cash flows ($987.11), we would get the actual NPV of this investment ($7.11).
In a simple example like this one, the shortcomings of the Excel NPV function are not critical. However, in complex capital expenditure analysis projects where the cash flows could net to be positive in some years and negative in others, it becomes a more significant issue. In these more complex situations, we cannot net out the cash flows in each year, but must leave them separate and calculate the NPV of each to determine the difference, or the NPV of the entire project.
I hope this helps some of you out there who may have struggled with why the NPV function in Excel was not returning the answer you expected. Incidentally, we would expect the IRR in our example problem to be just more than our required rate of return (8%) because the NPV was small, yet positive. The IRR for our example problem is 8.28%, which is in line with our expectations. We'll look more at NPV, IRR, and Profitability Index (PI) on another day.
Keywords: excel, pdf, printing, formulas, line break, cell, header, footer, multiple worksheets, vba, macro, delete blank rows, net present value, npv, incorrect solution
Comments ...
May 5, 2009 Mike