Microsoft Excel 12 TIPS and TRICKS

Keep values separate and in their own cells. Never put values in your formulas.  Anytime you’re trying to make changes to your formula, it will be a lot more difficult if you’ve included values. This is more complex than having formulas that reference and link to cells.

COLUMN WIDTH

• Do this from the top ribbon, CLICK on the column itself and change the width from the popup, or
• DOUBLE CLICK the column that takes the column to the largest column width possible.

ANCHORING VALUES IN FORMULAS

Say you want Price X Units Sold.  In your spreadsheet, you have a column specifying your ACCOUNT, UNITS SOLD, REVENUE:

• Select the REVENUE column and clear out any content
• Go to NO FILL, NO BORDERS
• Click on the CELL you want to use as the ANCHOR and HIT F4
• This will anchor the cell
• No matter what you do in other cells, this will stay consistent.

POPULATE CELLS With FORMULAS

You can DRAG the bottom right of the formula, DOUBLE CLICK and it will automatically populate the cells with the data you put in the formula.

NAVIGATION SHORTCUTS

If you have hundreds or thousands of lines of data, how do you get to the bottom of your spreadsheet without scrolling and scrolling?

• Hit CONTROL and ARROW DOWN (on the bottom right of your keyboard) and it will take you to the bottom of your spreadsheet.
• When you want to go back to the top hit CONTROL and ARROW UP.
• CONTROL and PERIOD will let you jump back and forth between the top and bottom.

SUMS

• Cell + Cell + is the long way to do this.
• EQUAL SUM Feature: Type in SUM, drag over the data you want to sum and it will calculate this.
• AUTO SUM Feature in the top ribbon: Put the Mouse underneath the data hit ENTER and the Sum will appear in the cell.

SEE HIDDEN DATA

• Select your cells and go to WRAP TEXT in the top ribbon and leverage the NAVIGATION WINDOW. The data you couldn’t see will now appear.

CLEAN UP

• INSERT COLUMN
• Go to PROPER and enter the account name you want.
• ( ) + TRIM will take away spaces you don’t want.

TROUBLESHOOTING

To Get Data Back:

• COPY and PASTE Values rather than Formulas.
  • COPY values >PASTE SPECIAL and paste the VALUE, or
  • RIGHT CLICK the MOUSE, HOLD and DRAG DATA where you want.

QUICKLY COPY TEXT

ENTER TEXT, e.g. “January”  DOUBLE CLICK and Excel is smart enough to populate cells with the rest of the months.

ENTER UN-IDENTICAL INFORMATION INTO NON-ADJACENT CELLS    

• SELECT which cells you want to enter data in
• CONTROL+ENTER the number of clients you added, and all the cells you selected will populate.

DATE YOUR WORK

• SELECT the cell you want
• = TODAY or
• = NOW to always show the current date  

For more information on Excel Tips or IT Services and Support contact:

Stephen Riddick
VP, Sales and Marketing
CSP, Inc.
sriddick@cspinc.com
919-424-2019