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!

The Insert Function Icon

  This might seem basic but then again it may not. Have you ever created a complex IF statement that doesn't work? Probably. Now, how do you fix it? I usually see people delete it and start all over.
 Sometimes you may want to start all over and sometimes you have to start all over but  frequently you just need to edit a small component of your formula.

So, here is the easy way to edit your formula.

Click on the cell containing the formula and then click on the Insert Function icon on the formula entry bar.
This will automatically bring up the formula in the Functions Argument box.

 In the example below, I left out the quotation marks around snowshoes in my SumIF formula so the answer displayed was 0. To correct it, I just clicked on the fx icon and then added the quotation marks in the Functions Arguments box that popped up.

This works even if you originally typed the formula in manually. My example is pretty simple but if you have a nested IF statement or other complex function this is the best way to edit it.  Just be aware - if your formula has a huge logic error in it, Excel will not open the Functions Argument dialog box. 

If you have a complex function and are using multiple functions and want to fix a specific component of it, click on the function name in the formula entry area and than click on the fx icon and Excel will just bring up that part of the formula rather than displaying the entire formula. This works great if you have a nested IF and are trying to edit one of the IF statements.

SkyDrive and Office Web Apps

Wow. It's been awhile since I have blogged. - That's what a holiday vacation will do for you. I hope your holidays were fun.

We went and visited my family in Boston and of course we got to experience the Blizzard of 2010 which is always fun. Unlike New York, the area we were in was plowed and passable within a day though so that of course gave my daughter lots of time to shop. Because we just love the snow and cold so much we drove an extra 5 hours and spent a few days at Smugglers Notch in Vermont skiing. If you have kids, this is absolutely the best place - every year it is awarded all the prizes for Best Family Resort.  This year my daughter and I tried snowboarding. My muscles still ache and I now have a much greater respect for snowboarders than I had. I think that I will remain a skier though.

I did so some work related stuff while on vacation and that included some reading. I just finished a book called Using the Microsoft Office Web Apps and thought I would share some information about it for those of you who have not heard of the Web Apps or SkyDrive.

 SkyDrive is a free online storage platform that is available from Microsoft. It allows you to upload and then share files- PDF, video, Office documents, photos etc.

 

This should be of particular interest to small firms or sole proprietorships that can’t afford a lot of IT expense.
This is also great if you travel – you no longer have to panic if you can’t find your flash drive with your presentation on it or you need to suddenly access or share a file while on the road!

 

With Skydrive:

•  You get 25 GB free online storage
• You can create folders
• You an upload existing Office documents and in many cases create new office documents online using Office WebApps which are basically stripped down versions of Office applications.
• You can password protect your folders and then allow access to individuals or groups.

 To find out more about it, go to http://explore.live.com/windows-live-skydrive-get-started. You access everything through Windows Live so if you already have a Live email or a Hotmail email you can use that otherwise you have to create one. It has a lot of potential. It is a little work to set it up but you can access it from anywhere and share it with anyone. How cool is that.

 

Have a great weekend.