Wednesday, August 7, 2013

Excel Wizardry

As with so many things in life, what seems like “Wizardry” in Excel is really a matter of knowing some Simple, albeit powerful, Tricks.

#1 - Multiple Lines of Text within a Cell
Have you ever wanted to add multiple lines of text to an individual cell? How can this be done, you ask? The answer is so easy, you’ll laugh.

When you want to insert a line break within a cell to break up your text for readability, just hold down Alt and hit Enter. You are also turning on Wrap Text when you do this. Cool!

#2 - Delete Blank Rows
If you have blank rows of cells within your database or table, it can potentially cause issues when deriving information (and who needs that…). Select a column, press F5, click on Special, select the Blanks option, and click OK.

Now that you have the blanks targeted, click over to Excel’s Home tab and go to the Cells group. Select Delete Sheet Rows and Bamm, no more blanks!

#3 – When Numbers are Not Numbers
Excel will occasionally store numbers like a “0” as Text instead of a numeral, (often when importing from another program). This can be problematic, especially if you have a sizeable database. The solution, once again, is so easy you’ll laugh!

  First, simply type a “1” into an empty cell outside of you data. Select and copy it to your clipboard, and then select the range of numbers you wish to fix. Right-click and choose Paste Special, select Multiply, and click OK. Problem Solved!

Simple tricks that look like Wizardry. Everything is simple when you know how!

No comments: