Excel Tips & Tricks – The Third Installment
Published: July 11, 2012
Author: Jaime Sikora
As you already know, I’m sure, I have written two posts on the subject of Microsoft Excel Tips & Tricks to get us SEMs (and, well, everyone else) through the workday. I’m 99% positive you have them bookmarked, of course, but just in case you can’t find them immediately, they are here and here, respectively.
Since this series has been met with such raving acclaim (listen, it is helpful), I’ve decided to return for a third installment of tips and tricks. Enjoy!
Let’s say, for example, you have a report template you update and send off to a client on a regular basis. This report contains the current date. Now, you can very easily manually update the date, but why do that when you can simply insert the following function?
It will simply keep itself updated and give you one less thing to do.
Now, I’ll be honest, this might not be the most practical function, but it’s a fun one. In the instance you have something that you need repeated multiple times, instead of tediously retyping it out, you can simply type =rept(cell name, number of times you’d like it repeated). For example:
Ok, this is a more basic skill, but I realize it’s always good to hit on basics. Let’s say you have information in two cells that needs to be combined into one. An instance in the PPC world that jumps out at me is if you have description lines 1 & 2 in a Google upload and you need to prep it for a Bing upload. If that might as well have been in a foreign language, let’s just imagine you have a first & last name in separate cells and need to combine them into one.
If you have a long list, this would clearly take forever. In order to deal with this issue at hand, you’ll have to concatenate.
(Quick tangent: “Concatenate” means link together or join in a chain. I had never heard this word before. It took a trip to dictionary.com to figure out its meaning. I never hear it outside the world of Microsoft Excel. My goal for the week is to work it into casual, non-computer-related conversation.)
Anyhow, back to business: The simple way is to go up to the function bar (fx) and select concatenate, then select the first cell you’d like to join. In *most* cases, you will want text 2 to be a space (although the choice is yours). Then, a third box will magically pop up and you can select the next bit of text you want to join in the fun.
Now, I don’t know about you, but I often have to populate data into various reports for clients. These reports contain both formulas and constant numbers. I usually work off the previous week’s (or month’s, or day’s, etc.) template. Sometimes, going through cell by cell, deleting the constant numbers, and leaving the formulas can be tedious and time-consuming.
I’ve found a little shortcut to help with this situation. First, I highlight the area of the workbook that I’m changing. Then, I hit ‘CTRL G’. A nice little ‘Go To’ box pops up. I then select ‘special’, in the bottom left corner. Now, as you can see, there’s a lot of untapped potential here, but sticking with the task at hand, one will need to select ‘constants’, then, under formulas, uncheck everything BUT numbers.
This will leave you with the constant numbers highlighted, so you can hit a quick ‘delete’ and clear out the old data whilst leaving your formulas intact. (Side note: if anyone else out there knows more about other tricks ‘CTRL G’ has up its sleeve, please leave them in the comments section.)
Let’s end on a simple note. Let’s say you’re working with a multi-tabbed spreadsheet (who hasn’t been there?) and you want to make one tab really stand out. Well, forgive me if you already know this, but you can color-code the tabs! So, if you want to make a certain tab really jump out, simply go down to the bottom and select the appropriate tab. Then, right click, select tab color, and select your desired color!
Well, that’s all for this installment, folks. Should I come back for another round of Excel suggestions? Should I attempt to tackle pivot tables & vlookup? Anything in particular you’re hoping I touch on? Hit the comments section…or #ppcchat, of course.
– Jaime Sikora, Account Coordinator