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.
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.
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.
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.
Go to PROPER and enter the account name you want.
( ) + TRIM will take away spaces you don’t want.
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: