In this post, I’ll cover a few more techniques for quickly moving around in Excel. Here’s the link to the earlier post on navigating in Excel.
This screencast covers changing the direction the cursor moves after pressing the enter key, keeping the cursor from moving after pressing the enter key, and the keyboard shortcut for automatically adjusting the column width (running time: 35 seconds).
Two big productivity boosters in Excel are the F2 and F4 keys.
F2 will put Excel into the edit mode. If you need to change something you’ve already typed into a cell, just press F2 to edit the cell. If you change your mind, press Esc to get out of edit mode.
If the cursor is in a cell, then pressing F4 will cycle go through all the different possible versions of the address.
About a year ago, I asked this question on the SuperUser site, but I didn’t get an answer that worked. I’ve finally come up with a solution.
The following is a very common workflow when writing a report:
1. Make tables and charts in Excel
2. Copy and paste these into Word and write additional text around the tables and charts
If you need to update the report regularly, copying and pasting from Excel into Word can take up a lot of time. The solution to this is to paste links to the tables and charts in Word rather than the tables and charts themselves. This way when the tables and charts are updated in Excel, they’re automatically updated in Word.
Most of the time linking between Excel and Word is easy and works very well. However, if the table or pivot table you link to is expanding over time (e.g., you’re adding new data each month), the linked version of the table or pivot table won’t expand in Word. In this post, I’ll show one method for solving this problem.
You should be familiar with the following functions and features: Excel table, pivot table, & function, counta function, indirect function, defined names, and Word field codes.
I’ve created a screencast that shows how to set up dynamic links to Excel tables and pivot tables in Microsoft Word (running time: 4 minutes 4 seconds).
Having leading or trailing spaces around text is one of the most common reasons why vlookup fails. The solution to this problem is simple: the trim function. Trim removes spaces from around text. Wrapping the lookup_value and the table_array inside the trim function makes vlookup much more sturdy. Trim operates on one value at a time. Since the table_array has many different values, you’ll need to enter the trimmed vlookup function as an array formula. If the data is extra messy, you’ll also need to wrap the lookup_value and the table_array with the clean function. In a few cases, you’ll also need to use the substitute function in combination with trim and clean.
Credit: ©iStockphoto.com/James Brey
Calculator is a program that comes with every Windows computer. It’s ok for the simplest of calculations, but it has a lot of limitations. For example, it doesn’t keep a history of your calculations and there is no way to keep it floating on top of the screen. SpeedCrunch is a good alternative to Calculator. SpeedCrunch keeps a running history of your calculations, has syntax highlighting, let’s you create variables, and has many other features. You can download SpeedCrunch here. It’s free, and I recommend you get the portable version.
Many governments and businesses use fiscal years that are different than the calendar year. A fiscal year that runs from April 1 to March 31 of the following year is quite common. In this post, I’ll demonstrate how to create an Excel function that will tell you which fiscal year any date belongs to.
Let’s assume the fiscal year runs from April to March. Here are some examples of what we want to do:
Here is the formula in cell B2 that will convert the date in cell A2 into a fiscal year:
=IF( MONTH(A2)<=3, "FY" & (YEAR(A2)-1) & "/" & RIGHT(YEAR(A2),2) , "FY" & YEAR(A2) & "/" & RIGHT((YEAR(A2)+1),2) )
If you’re interested in how this formula works, please read on.
We want to implement the following two rules:
We’re going to use the following Excel functions: &, IF, MONTH, YEAR, and RIGHT
Rule 1:
If the month is between April and December then the fiscal year begins with the letters “FY” and then the current calendar year and then a forward “/” and then the last two digits of the next calendar year.
Here is the pseudocode:
“FY” + [Calendar Year] + “/” + last two digits of [Calendar Year+1]
Here is the Excel formula (assuming the date is in cell A2):
= "FY" & YEAR(A2) & "/" & RIGHT((YEAR(A2)+1),2)
Rule 2:
If the month is between January and March then the fiscal year begins with the letters “FY” and then the previous calendar year and then a forward “/” and then the last two digits of the current calendar year.
Here is the pseudocode:
“FY” + [Calendar Year-1] + “/” + last two digits of [Calendar Year]
Here is the Excel formula (assuming the date is in cell A2):
= "FY" & (YEAR(A2)-1) & "/" & RIGHT(YEAR(A2),2)
Now we can take the two rules and put them in an IF statement:
Putting the two rules together:
If the month is January, February, or March then use rule 2 otherwise use rule 1
Here is the pseudocode:
If month less than or equal to 3 then use rule 2 ELSE use rule 1
Here is the final Excel formula (assuming the date is in cell A2):
=IF( MONTH(A2)<=3, "FY" & (YEAR(A2)-1) & "/" & RIGHT(YEAR(A2),2) , "FY" & YEAR(A2) & "/" & RIGHT((YEAR(A2)+1),2) )
I can’t find where I originally read someone saying, “Those aren’t warts, they’re battle scars.” It might’ve been in the comments section on some software blog. The quote is in reference to how ugly source code often looks, especially code that’s been in use for a very long time. Back in 2000, Joel Spolsky wrote the definitive blog post on why code looks ugly and why there’s usually a good reason for it.
… you can ask almost any programmer today about the code they are working on. “It’s a big hairy mess,” they will tell you. “I’d like nothing better than to throw it out and start over.”
Why is it a mess?
“Well,” they say, “look at this function. It is two pages long! None of this stuff belongs in there! I don’t know what half of these API calls are for.”
Before Borland’s new spreadsheet for Windows shipped, Philippe Kahn, the colorful founder of Borland, was quoted a lot in the press bragging about how Quattro Pro would be much better than Microsoft Excel, because it was written from scratch. All new source code! As if source code rusted.
The idea that new code is better than old is patently absurd. Old code has been used. It has been tested. Lots of bugs have been found, and they’ve been fixed. There’s nothing wrong with it. It doesn’t acquire bugs just by sitting around on your hard drive. Au contraire, baby! Is software supposed to be like an old Dodge Dart, that rusts just sitting in the garage? Is software like a teddy bear that’s kind of gross if it’s not made out of all new material?
You can read the entire post at Joel on Software.
Credit: ©iStockphoto.com/Zachary Williams
One simple but very handy program that comes with every Windows computer is Notepad. Notepad is an example of a plain text editor. Plain text editors are great for when you need to get rid of formatting from text you’ve copied over from a website. Also, if you need to copy and paste a lot of text between two different programs, sometimes it’s useful to use a text editor as kind of a visible clipboard. One problem that comes up with using Notepad is that when you click somewhere else on the screen, it minimizes to the taskbar. A great alternative to Notepad is a free program called TED Notepad. TED Notepad is a small program that has a ton of features. One of the most useful things it can do is stay visible on the screen even when you click on some other program.
Ted Notepad has a quite a few text editing features too. It’s a good tool to use if you want to write and not get distracted by all of the formatting features in Microsoft Word.
Credit: ©iStockphoto.com/Izabela Habur
If you use Excel a lot, probably the best thing you can do to become more efficient is to learn all the keyboard shortcuts. Switching back and forth between the keyboard and mouse really slows things down. In the following example, I’m going to show how you can use the arrow keys in combination with the ctrl and shift keys to quickly move around and select cells.
If you have a bunch of data in columns, you can use the arrow keys to move the cursor around one cell at a time.
If you hold down the ctrl key while pressing the arrow keys, the cursor will jump to the last cell in the group in the direction of the arrow. In this example, first ctrl and the right arrow are being pressed, then ctrl and the down arrow, then ctrl and the left arrow, and then finally, ctrl and the up arrow.
If you hold down the ctrl key and the shift key while pressing the arrow keys, the cursor will jump to the last cell in the group in the direction of the arrow and select everything along the way. In this example, first ctrl+shift and the right arrow are being pressed and then ctrl+shift and the down arrow.
Credit: ©iStockphoto.com/HandmadePictures
A computer algebra system (CAS) is software that can perform symbolic mathematics. There are two main types of math software: programs designed to do numerical calculations and programs that can manipulate symbols. The first group includes programs like MATLAB, GAUSS, GNU Octave, and Stata. The second group includes programs like Mathematica, Maple, and Maxima.
A CAS will let you solve the following type of problem:
The CAS will tell you that the above is equal to:
There are many CASes out there; two very user friendly ones are Wolfram Alpha and Microsoft Mathematics. Both are free. Wolfram Alpha is web based and it’s made by Wolfram, the same company that makes Mathematica. Here’s how the above example would look like in Wolfram Alpha. Microsoft Mathematics is from Microsoft and it’s designed for students. One great feature of the program is that it can often show all of the steps needed to get to the final answer instead of just showing the final answer.
You can download Microsoft Mathematics here.
