January 7th, 2011
I met with a client the other day, and during that meeting I needed to take one of their Excel files and import it into their new database system. As we were working together in front of one computer, they got to see me in action. During the process, I had to change some of their Excel data in order to prep it for the import process.
To me, these were basic, every day steps I take in Excel…but to them, this was an “ah ha” moment! So it got me thinking that there is a lot of information I store in my brain and use on a daily basis that can be valuable to you, my audience.
This article will focus on some Excel techniques you can start using today to help you in your regular business tasks. Future articles will focus on other software applications.
Locking in formulas
Have you ever created a formula based on, say, data in the row above, and then resorted your data, only to find that the data has now changed? This is because the formula is still in place and simply moved from one location to another. The way around this is to copy the column where your formula exists, and then do a Paste Special… Paste Values. What this does is replaces the formula with the actual value the formula creates, and allows you to sort your data properly afterwards.
Copying values down a column
Have you ever wondered if there was a shortcut to copying and pasting the same value down a set of rows? For example, you want to put a date in the last column of your Excel file to indicate when the data was imported.
In the bottom right corner of Field 2, row 2, there is a small square when your cursor is in that cell. Move your mouse to that spot, and the “arrow” will change from a thick white plus sign to a thin black plus sign. When this happens, you can double-click and it will copy the data down to the end of the data (in this case the last row of Field 1). Or instead of double-clicking, simply drag down and stop at the last row you want to copy your data to.
If the data appears in “numeric” order instead of a copy of your data, simply change the options that appear in the Auto Fill Options at the bottom right of the data you just copied. You can change it to Copy, Fill Series, and many other options.
NOTE: If the field (column) before the one you are copying data does not have values through to the end, here’s a trick – hide the columns that don’t go all the way to the end, and line up your “field 2” with a field that goes to the end. This will let you copy all the way down, and then unhide your columns!
These are just two examples of shortcuts in Excel that will make your life easier and your work quicker. To learn more, subscribe to this blog and/or follow @howtohelpdesk on Twitter.
To learn more how-to’s for Microsoft Office and other business applications, check out the How-to Help Desk at www.h2hd.net to learn how we can walk you through a scenario and teach you how to do it yourself!