Book

Suggestions


Enter your email address:

Delivered by FeedBurner


Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal









Subscribe here
Add to My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com


  Friday, April 18, 2008 – Permalink –

Excuse Me Your Formula's Showing

Formatting slip



Try this experiment. On a sample worksheet, enter some arbitrary data in say the A1:B5 range.
Select the C column and format it as Text.
(Right click choose Format Cells - on the Number tab choose Text)

In cell C1 enter a function, such as =sum(a1:b1)

With C1 still selected, double click the Fill handle
(the tiny box at the lower right corner of the cell.)


The formula is filled down the column as long as there is data in an adjacent column.


But wait! I don't see the value. I see the formulas!
In addition, the formulas are still in lower case and the relative references have not been updated.


Easy to fix, I hear you say. Just reformat the column as General.


Nothing happens.


To fix the problem, make sure the column is formatted as General.


Select the first cell. Click in the formula bar and hit the Enter key.


Now double click the Fill handle.


(You could also use Edit>Replace to replace = with =. However, Relative references will be incorrect and unless you have reformatted the whole column as General, any new formulas will still display as text.)


The reverse also causes a problem. In a column formatted as General, enter some formulas.

Now reformat the column as Text. The formulas still work, but if you edit one of them, it reverts to a text display.

Microsoft KB:
Cell Linked to Text-Formatted Cell Shows Formula Not Value

Formulas can, of course, be toggled using CTRL+~ (Tilde)
(Though it really should be called CTRL+` (Grave Accent), since the Shift key is not used.)



See all Topics

Labels: , ,


<Doug Klippert@ 7:31 AM

Comments: Post a Comment

  Saturday, January 12, 2008 – Permalink –

Spreadsheet Diet

Formatting bloat



Avoid applying formatting to more than just the active area of your worksheet. Extraneous formatting will confuse Excel about the last cell in the spreadsheet.

Depending on your OS and specific configuration, you could see symptoms ranging from Excel not responding to various error messages concerning page faults, low virtual memory, and access violations. You may see a warning message; "Too many different formats."

To resolve this issue, make sure you select only the particular range of cells you want formatted when you apply specific formatting or select Format>AutoFormat.

You can use Ctrl+End to see where Excel thinks the last cell is.

For more information, check out:

How to reset the last cell in Excel 2007

Microsoft Knowledge Base article 211478

Also see:

Spreadsheet Diet

Beyond Technology:
Identifying the Real Last Cell

Microsoft:
Formatting Cleaner Add-in



See all Topics

Labels: , ,


<Doug Klippert@ 8:32 AM

Comments: Post a Comment