Flipping Your Data

Flipping Your Data

How often have you typed your data in and then wished that it was presented differently?  The good news is that it is extremely easy to flip or transpose your data.

In the spreadsheet to the left, I have sales for each salesperson by month which is pretty standard way to set up your data.  But, I wonder if the data might be more useful if it was presented by month for each salesperson. Rather than retyping or dragging everything around, you can use the Paste Special command and transpose all the data at once.

Here is how to do it.

• Select the data. In this case A3.G12
• Copy the data
• On the Home Tab
• Click on a cell where you want the new data to begin displaying (upper left cell of the data)
• Click the dropdown arrow under Paste
• Select Paste Special
• Click Transpose
• Click OK

Voila- Your data is flipped!

Shading Unlocked Cells

It really used to annoy me that Excel did not highlight unlocked cells the way Lotus did. Lotus would automatically shade unlocked cells a nice green so that they were easy to identify when working in a protected worsheet.  In Excel, you don't get that. If you press the TAB key, it will take you from unlocked cell to unlocked cell; however, sometimes it is nice to visually see the layout so you can make sure you know where to enter the data and what is going on.

So, if you use protected worksheets and want users to see the unlocked cells, before you protect the sheet, do the following:

• Select the entire workbook
• Click on Conditional Formatting
• Select New Rule
• Click on Use a formula to determine which cells to format
• Type =Cell("protect",A1)=0
• Select Format
• Select Fill
• Select a light color
• Click OK
• Click OK
  

Any cells that have been 'unlocked' will be shaded.
The 0 denotes unlocked cells. If you change the 0 to a 1 it would show locked cells.

Another way to copy!

Everyone has their favorite way to copy. I have lost count on the how many ways there are to copy in Excel - but here is one that many of you may not be aware of. It is a quick way to fill down a column or across a row.

• Select the range first
• Enter the formula or value that you want to replicate
• Press Control and Enter at the same time

Whatever you typed in the first cell will carry down throughout the entire range you selected.  This only copies the formula or value typed - it does not copy any pre-existing  formats that were in the first cell. If you format the data in the first cell and then press Control and Enter, the formatting will also replicate.

An Excel Budget Tip

I just redid my budget for 2011 and thought you might find this tip useful. If you have a great spreadsheet all set up, naturally you want to use it again. I mean, why recreate the wheel?

What you can do is have Excel select all the non-formula numbers in the spreadsheet and then you can delete them. This leaves all the formulas and text in place and all that is left are the input cells where you want to enter numbers.

Click on the Find and Select Icon ( or press F5)
Click on Go To Special....
Put a checkmark in Constants

Leave the checkmark in Numbers and uncheck anything else

Click OK

Excel will then select all the cells in the spreadsheet that are just input cells (cells with just numbers - no formulas)

Press Delete

Presto! A clean spreadsheet to start the new year with.

Excel's 25th Birthday

Can you believe it?
Excel is 25 years old !

Click here to go to Microsoft Excel's facebook page and watch the video of its history.

How geeky are we?

I still remember the day I was told that I had to move from Lotus to Excel ... I spent a lot of time kicking and screaming ........  oops... I'm showing my age... I won't even mention Multiplan!