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


  Wednesday, January 27, 2010 – Permalink –

Color Scales

Conditional colors

Here is some information on Conditional color scales in Office 2010.

Microsoft Excel 2010 Blog 1

Microsoft Excel 2010 Blog 2

Microsoft Excel 2010 Blog 3



See all Topics

Labels: , , ,


<Doug Klippert@ 3:42 AM

Comments: Post a Comment

  Wednesday, December 30, 2009 – Permalink –

Chart Null Data Gaps

Fill in the spaces



When creating a chart, data can sometimes be missing. You have a choice of leaving the cells empty or making them zero.

By default, an empty cell will leave gaps in your graph. Zero entries will send a line graph down to the base line.

One way to force Excel to interpolate the data is to enter the function =NA() in the empty cells.

Another way is to go to Tools>Options and choose the Chart tab.

Pick how you want data plotted - with gaps, as zero, or interpolated.






Microsoft also has this suggestion:

Gaps between the dates
"If the datasheet data for the category axis contains date number formatting, Microsoft Graph automatically uses a special type of axis in your chart called a time-scale axis.
A time-scale axis shows a blank category for dates for which you have no data. If you do not want to see these gaps — for example, if you have data for 1-Jan, 15-Jan, 3-Feb, 12-Feb, and 2-Mar, and you want to plot the days next to each other - you can change the time-scale axis to a standard category axis.
On the Chart menu, click Chart Options, click the Axes tab, and then click Category under Category (X) axis."

Note, For 2007+, on the Design tab, click Select Data in the Data area, and then click Hidden and Empty Cells on the Select Data dialog box.




See all Topics

Labels: , , , ,


<Doug Klippert@ 3:18 AM

Comments: Post a Comment

  Sunday, November 29, 2009 – Permalink –

New Conditional Formatting

Much more capabilities


Pre-2007 Excel was limited to only 3 conditions. The new Office is more generous and versatile.
Here are some of the features:



Format all cells based on their values
Use this to create a data bar, 2-color or 3-color color scale, or icon set rule.
Format only cells that contain:
Use this to create the Excel 2003-style rules and more (format cells greater than, less than, greater than or equal to, less than or equal to, equal to, not equal to, between, not between). This is also the entry point to create rules of type: specific text, date occurring, blanks, non-blanks, errors, non-errors.
Format only top or bottom ranked values:
Use this to create top n, top n%, bottom n, bottom n% rule types.
Format only values that are above or below average:
Use this to create above average, below average, 1 or 2 or 3 standard deviation above, or 1 or 2 or 3 standard deviation below rule types.
Format only unique or duplicate values:
Use this to create rules that format unique or duplicate values.
Use a formula to determine which cells to format:
Use this to create Excel 2003-style rules where you can enter a formula to determine whether a format should be applied.


2007+ Conditional Formatting




See all Topics

Labels: ,


<Doug Klippert@ 3:48 AM

Comments: Post a Comment

  Tuesday, November 24, 2009 – Permalink –

Formatting Overview

Looking good


The judicious use of formatting can make data easier to understand as well as pleasant to see.
Scott Lowe put together a series of articles on how to format data in Excel.

The Articles are on TechRepublic.com

Anatomy of Excel formatting: Part 1
  • Boldface, italicize and underline cell content
  • Change the size and font of your text
  • Apply a default Excel style (i.e. dollar, percent, etc) to cells
  • Use date and time formatting in your spreadsheet
  • Apply shading
Anatomy of Excel formatting: Part 2
  • Apply borders
  • Resize rows
  • Resize columns
Anatomy of Excel formatting: Part 3
  • Text formatting
  • Justify cell contents
  • Change the direction of the text in your spreadsheet
  • Word wrap text
Anatomy of Excel formatting: Part 4
  • Automatically format cells based on their contents
  • Change the margins for your printed page
  • Add a header and footer to your printer spreadsheet




See all Topics

Labels: , ,


<Doug Klippert@ 3:39 AM

Comments: Post a Comment

  Saturday, October 17, 2009 – Permalink –

Shortcut Borders

Keyboard trick


Use Ctrl+1 to bring up Excels Formatting dialog box. Shift+B will take you to the Borders tab.

Now you can use the following key strokes (2002+):

ALT+T
Apply or remove the top border.
ALT+B
Apply or remove the bottom border.
ALT+L
Apply or remove the left border.
ALT+R
Apply or remove the right border.
ALT+D
Apply or remove the downward diagonal border.
ALT+U
Apply or remove the upward diagonal border.
ALT+H
Apply or remove the horizontal interior border.
ALT+V
Apply or remove the vertical interior border.
ALT+O
outlines the cells.
ALT+I
give interior (vertical and horizontal).
ALT+N
removes all borders.
ALT+C
brings up the color palette.



Shortcuts for Applying Borders

Ctrl+Shift+7 will outline a cell without having to display the Format dialog.




See all Topics

Labels: , ,


<Doug Klippert@ 3:28 AM

Comments: Post a Comment

  Wednesday, September 02, 2009 – Permalink –

Dynamic Tabs

Change tab names automatically


Changing the names of tabs is easy, just double click the tab or right click and choose rename.

Allen Wyatt has a small piece of code that will automatically update the tab name based on the value of a cell in the spreadsheet.


Sub myTabName()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

Allen also has some error checking code on his site:

Dynamic Worksheet Tabs


Dick Kusleika suggests another way using a change event:

Naming a sheet based on a cell




See all Topics

Labels: , , , ,


<Doug Klippert@ 3:33 AM

Comments: Post a Comment

  Tuesday, June 30, 2009 – Permalink –

Thirtieth Condition Formatting

Three is not always enough


Pre-2007 Excel gives the user the ability to specify up to three conditions under Format>Conditional Formatting.

If that is not enough, Frank Kabel and Bob Phillips of xlDynamic.com offer a free download that extends the conditions to 30!




Extended Conditional Formatter

Also see:
Conditional Formatting (including 2007)




See all Topics

Labels: , , ,


<Doug Klippert@ 3:09 AM

Comments: Post a Comment

  Friday, May 15, 2009 – Permalink –

Tabs with the Number of the Week

Count to 52



Excel no longer has a theoretical limit on the number of worksheets in a workbook. One common use of this ability is to add a worksheet for each week in the year.

Here's a macro that does the trick:
Sub YearWorkbook()
Dim iWeek As Integer
Dim sht As Variant
Application.ScreenUpdating = False
Worksheets.Add After:=Worksheets(Worksheets.Count), _
Count:=(52 - Worksheets.Count)
iWeek = 1
For Each sht In Worksheets
sht.Name = "Week " & Format(iWeek, "00")
iWeek = iWeek + 1
Next sht
Application.ScreenUpdating = True
End Sub

ExcelTips.VitalNews.com:
Naming tabs for weeks




See all Topics

Labels: , ,


<Doug Klippert@ 3:58 AM

Comments: Post a Comment

  Wednesday, April 15, 2009 – Permalink –

Date an Octothorpe

Date an Octothorpe


Some more of those things I'm sure I used to know

The keyboard combination of Alt+Shift+D inserts the current date in MS Word and PowerPoint. Ctrl+; (semicolon) does it in Excel and Access.

If you do not like the date's format, select a different one with Insert>Date and Time and, if you would like to make that permanent, click on the Default button in the lower left corner of the dialog box (in PowerPoint it's in the lower right corner).

In Excel, Ctrl+Shift +# formats the entry as day-month-year. Ctrl+1 will display the "Format cells" dialog box.

BTW, the "hash, pound or number" sign # is also called an "octothorpe".

The person who named it combined Octo for the eight points and Thorpe for James Thorpe.

"Bell Labs engineer, Don Macpherson, went to instruct their first client, the Mayo Clinic, in the use of the new (touch tone phone system). He felt the need for a fresh and unambiguous name for the # symbol. His reasoning that led to the new word was roughly that it had eight points, so ought to start with octo-. He was apparently at that time active in a group that was trying to get the Olympic medals of the athlete Jim Thorpe returned from Sweden, so he decided to add thorpe to the end."

While we're at it, the "backwards P, Enter mark" is actually named a "pilcrow".

The pilcrow was used in medieval times to mark a new train of thought, before the convention of using paragraphs was commonplace.

Also see:
Geek-speak names for punctuation marks

Wikipedia:
Punctuation




See all Topics

Labels: , ,


<Doug Klippert@ 3:36 AM

Comments: Post a Comment

  Wednesday, January 14, 2009 – Permalink –

Spreadsheet Design

Make it work and look good


Timothy Miller uses the nom de screen of "Jethro" (Moses' Father-in-Law).

His SpyJournal.biz site/blog gives some tips on how to present an Excel solution


Design and layout

One of the easiest ways to set up spreadsheets that calculate or generate results that need to be reported is to separate the function from the form. Just like a shiny exterior on a car hides the internal engine and wiring. I always create my reports and front end menus to look good and generate results and calculations in more functional sheets.
Hiding unnecessary sections

If you must have calculations and working sections visible, then hide the unnecessary bits. Hiding a row or column is only one way of doing this. Using the group function you can rollup whole rows of information, e.g. components that add to a subtotal or constants and variables such as exchange rates, interest rates, and other indexes.
Use of colour and graphics

I like to use the company logo or other graphic as a design element in my spreadsheet. Sometimes I do this by using the corporate colours, other times by using the graphic itself. If I have a spreadsheet with a lot of macro buttons, I may use command objects and use the logo as a picture on the button.
Removing excel components

There are a number of excel components that you can turn off. Menu screens and reports screens may not need horizontal or vertical scroll bars, sheet tabs or row and column headings. Using macro buttons to return to a menu can overcome the need for sheet tabs. Not displaying gridlines will give a clean uncluttered look to a layout, and then using borders as necessary can create emphasis in the right areas.

You'll find the complete text here:
Design Presentation Tips
Also see:
SpreadsheetStyle




See all Topics

Labels: , ,


<Doug Klippert@ 3:02 AM

Comments: Post a Comment

  Sunday, December 14, 2008 – Permalink –

Format Numbers

It's your choice



Here is an almost forgotten sample spreadsheet. It was constructed, for Microsoft, back in 1997 by Lori B. Turner, and is still relevant.

The sheets are protected. In order to see the cell formatting, you need to go to Tools>Protection to Unprotect Sheet . . . There is no password required

Formatting sample Workbook

Also:

OzGrid:
Custom Formats

If you need to format numbers that will be displayed connected to text, here is one way to do it:

  1. In cell A1, enter the text "You still owe the sum of".
  2. In cell A2, enter the number "5434".
  3. In cell A3, enter the text "for invoice # 2232 from 6/15/2001".
  4. In cell A4, enter the formula =A1&" "&TEXT(A2,"#,##0")&" "&A3.

You still owe the sum of 5,434 for invoice # 2232 from 6/15/2001


Excel Tips
Formatting concatenated numbers and text

Or:



Also:
Excel Format Tips

Also:
A very well produced examination of the subject by Jon Peltier:
Number Formats in Microsoft Excel



See all Topics

Labels: , ,


<Doug Klippert@ 3:14 AM

Comments: Post a Comment

  Sunday, November 30, 2008 – Permalink –

Time Without Limits

No Delimiters


Excel is most happy when you enter dates and times with the correct separators.

1/1/2004 is a good date. So is 1-1-2004.

If you just entered 112004 in a cell formatted as a date you'll get:

Wednesday, August 27, 2206

the 112,004th day since January 1, 1900.

Chip Pearson has come up with VBA code, using the Worksheet_Change event procedure, that will allow you to enter dates without dashes or slashes.



See:
Date And Time Entry



See all Topics

Labels: , ,


<Doug Klippert@ 3:45 AM

Comments: Post a Comment

  Saturday, November 15, 2008 – Permalink –

Locate Duplicates with Conditional Formatting

Highlight entries


Conditional formatting can be set up by selecting the whole range, or for the first cell in the range and then copy down that conditional format. I find it is usually just as easy to select the whole range to start with. The formula will adjust itself.

In this example, cell B2 has a heading of Product Numbers.

Select cell B3 (or the entire targeted range) and from the menu.

Select Format > Conditional Formatting.

The Conditional Formatting dialog opens with the initial dropdown saying
"Cell Value Is".

Click the arrow next to this, and choose
"Formula Is".

After selecting "Formula Is", the dialog box changes appearance.
Instead of boxes for "Between x and y", there is now a single formula box.

You can type in any formula as long as that formula will evaluate to TRUE or FALSE.

The formula to type in the box is
=COUNTIF(B:B,B3)>1

Conditional Formatting

This says, "look through the entire range of column B.

Count how many cells in that range are the same value as what is in B3."
(In the graphic, B7 is the Active cell.)

That same comparison will be made in every cell that contains the conditional formatting.

(If your data is in column E and you are setting the first conditional formatting up in E5, the formula would be =COUNTIF(E:E,E5)>1.)

Anytime a duplicate appears in the range, it will receive the special formatting.

In this example, any time a duplicate number appears anywhere in column B, even if it is not itself formatted, the selected range will reflect the duplicate.

=COUNT(B:B,B3)>2 would count entries that appear more than two times.
=COUNT(B:B,B3)=2 would count entries that appear twice.

If you want only a part of the column in the formula, it is easier to use absolute addresses, such as =COUNT($B$3:$B$200,B3)>1

Adapted from MrExcel.com

Also see:

Chip Pearson's discussion of duplicates:
Duplicate And Unique Items In Lists

and:

Contextures.com:
Conditional Formatting
(See Hide Duplicate Values)



See all Topics

Labels: , ,


<Doug Klippert@ 3:45 AM

Comments: Post a Comment

  Wednesday, August 13, 2008 – Permalink –

Value of Cell in Header

or Footer



The header and footer cannot contain a link to a cell. You can create and run a macro that will put the value of a cell into a footer or header. You could run this macro each time the contents of the specified cell changes.

Sub AssignCenterHeaderToValueInA1OnActiveSheet()
ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
End Sub

Or use it as an Event code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.PageSetup.CenterHeader = Range("A1").Value
End Sub

OzGrid.com:
Information from a cell in a worksheet


Erlandsen Data Consulting:
Insert headers and footers


Chip Pearson:
Headers and Footers


Jan's Computer literacy 101:
Excel Basics: Setup Header/Footer


University of Wisconsin at Eau Claire:
Modifying Header and Footer Information


See all Topics

Labels: , , ,


<Doug Klippert@ 4:27 AM

Comments: Post a Comment

  Friday, August 01, 2008 – Permalink –

Rank Formatting

Highlight the best



Use Conditional formatting to highlight the rank of items in a list.
Select the range. Go to Format>Conditional Formatting....

Change the first box to "Formula Is".

Enter the following formulas. (Click Add to set the 2nd and 3rd Condition.)

=RANK($A2,$A$2:$A$13)=3
=RANK($A2,$A$2:$A$13)=2
=RANK($A2,$A$2:$A$13)=1

Rank Conditional Formatting

(Notice the three way tie for third.)

Does a tie for first or third make sense?

If you want a unique rank, try a formula like:

=RANK(A2,$A$2:$A$13)+COUNTIF($A$2:A2,A2)-1

This will rank the numbers in the order they appear in the list.

For a detailed discussion of ranking see:

Chip Pearson:
Ranking Data In Lists
(There is a workbook you can download)



See all Topics

Labels: ,


<Doug Klippert@ 2:42 AM

Comments: Post a Comment

  Monday, July 28, 2008 – Permalink –

Formatting Codes for Headers and Footers

Roll your own


From Microsoft support:

The following list contains the format codes that you can use in headers and footers.


Codes to format text ("&" is an ampersand - Shift+7)

&L
Left-aligns the characters that follow.

&C
Centers the characters that follow.

&R
Right-aligns the characters that follow.

&E
Turns double-underline printing on or off.

&X
Turns superscript printing on or off.

&Y
Turns subscript printing on or off.

&B
Turns bold printing on or off.

&I
Turns italic printing on or off.

&U
Turns underline printing on or off.

&S
Turns strikethrough printing on or off.

&"fontname"
Prints the characters that follow in the specified
(font. Be sure to include the quotation marks around the font name.)

&nn
Prints the characters that follow in the specified
(font size. Use a two-digit number to specify a size in points.)

Codes to insert specific data


&D
Prints the current date

&T
Prints the current time

&F
Prints the name of the document

&A
Prints the name of the workbook tab (the "sheet name")

&[File]
Also prints file name

&[Path]
Prints path

&[Picture]
Opens dialog box to select graphic (2003)

&P
Prints the page number

&P+number
Prints the page number plus number

&P-number
Prints the page number minus number

&&
Prints a single ampersand

&N
Prints the total number of pages in the document


In a macro, to use multiple lines in a header, use either of the following methods:

  • Use CHR(10) to insert a linefeed character.
  • Use CHR(13) to insert a carriage return character.


The article also includes the VBA to create a macro that will insert header/footer information.
Microsoft KB213618


Also:

Daily Dose of Excel:
Formatting Footers in VBA



See all Topics

Labels:


<Doug Klippert@ 3:26 AM

Comments: Post a Comment

  Sunday, June 29, 2008 – Permalink –

Customize Date in Footer

Formatting



This subroutine inserts the current date in the footer of all sheets in the active workbook. This process can be accomplished without a macro, however, you'll need the macro if you want to specify the formatting of the current date. An example of the return generated by running this macro is Saturday, March 05, 2005.

Sub PutCurrentDateInCenterFooterAllSheetsInWorkbook()
For Each oSheet In ActiveWorkbook.Sheets
oSheet.PageSetup.CenterFooter = Format(Now(), "dddd mmmm dd, yyyy")
Next
End Sub


You can change the word CenterFooter to CenterHeader. You could also use LeftHeader, RightHeader, LeftFooter, or RightFooter.

Microsoft KnowledgeBase:
Macro to Change the Date/Time Format in a Header/Footer



See all Topics

Labels: , ,


<Doug Klippert@ 3:11 AM

Comments: Post a Comment

  Sunday, May 04, 2008 – Permalink –

Conditional Formatting

If it's Tuesday, it must be mauve


Conditional formatting is one of Excel's better features. It allows you to preset certain font styles, colors, and cell-background colors based on cell values.

This can be very useful for highlighting important information and values outside an accepted range or providing a visual cue to associate value ranges with color codes.

The best part is that conditional formatting is very easy to set up.

Just click the cells you'd like to format and select Format >Conditional Formatting. The Conditional Formatting dialog box lets you set up the conditions by which the formatting of the cell will occur.

You pick the operator (between, equal to, less than, etc.) and the value or range of values. Click Format to open the Format Cells dialog box, where you can select the colors and styles to be used.

Each cell can have several conditional formats. For example, you might say that if a certain cell's value is between 20 and 50, the text should be blue on a yellow background.

However, you can format that same cell to exhibit red, bolded text on a green background if it contains a value between 51 and 100.


Conditional Formatting

Before 2007, you could use up to three conditions, but earlier versions of Excel can be tricked to use more if it should become necessary.

Oz Grid:
Excel VBA Macro Code to Get Around Excel's 3 Criteria Limit in Conditional Formatting

GR Business Process Solutions:
Graham Barrow and Ray Blake
Highlight the current or past month in Excel with conditional formatting

Chip Pearson:
Conditional Formatting

Also

Compatibility in Excel 2007

Conditional Format Functions in Excel 2007

Adding Customized Rules to Excel 2007

Demo:
Data takes shape with conditional formatting



See all Topics

Labels: , , ,


<Doug Klippert@ 7:03 AM

Comments: Post a Comment

  Saturday, March 29, 2008 – Permalink –

Week Numbers

Who's counting?


For most purposes, weeks are numbered with Sunday considered the first day of the week. This works most of the time, but it can be a little confusing certain years.


2004 has 53 weeks. January 1 is the only day in the first week of 2005. Week 2 starts on Sunday 1/2/2005.


Chip Pearson is the Date and Time guy:
Week Numbers In Excel

"Under the International Organization for Standardization (ISO) standard 8601, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.

While this provides some standardization, it can lead to unexpected results - namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.

An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec."


The first week of 2005 should start on January 3. The first and second would be part of week 53 of 2004.


Wikipedia:
Week Dates

If your week starts on a different day, you can use the Analysis ToolPac function:
=WEEKNUM(A1, 2) for a week that starts on Monday, =WEEKNUM(A1) if it starts on Sunday.


Also this from ExcelTip.com:
Weeknumbers using VBA in Microsoft Excel

"The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function shown here will calculate the correct week number depending on the national language settings on your computer."


In Access:
DatePart Function


If your work week is always Saturday through Friday then

datepart("ww",[DateField],7,1)


will return 1 for 1/1/2005 through 1/7/2005, 2 for January 8-14/2005, etc.
Otherwise use 1 for Sunday through 7 for Saturday.


The last number sets these parameters:

1, Start with week in which January 1 occurs (default).
2, Start with the first week that has at least four days in the new year.
3, Start with first full week of the year.



See all Topics

Labels: , ,


<Doug Klippert@ 7:24 AM

Comments: Post a Comment

  Monday, February 25, 2008 – Permalink –

Legacy files from 2007

Go back


Read this article closely. If you work in a situation where you need to work with legacy (pre-2007) files, it may be handy.

If you do most of your work in 2007, I wouldn't bother.


"When you use Windows Explorer or the desktop to create a new 2007 Microsoft Office file, a new Office file is created in an XML file format (.dox or .xlsx). For example, this behavior occurs when you right-click the desktop, you point to New, and then you click Microsoft Office Word Document. By default, files that you create in the 2007 Office system are in XML file formats.

This article is about how to create legacy Office files, such as .doc files, .xls files, .ppt files, or .mdb files in the 2007 Office system. You can create legacy Office files without opening any Office applications. To do this, you must modify some settings. The modified settings will apply to all the users who log on to the computer."

Knowledgebase 935787



See all Topics

Labels:


<Doug Klippert@ 8:01 AM

Comments: Post a Comment

  Sunday, January 13, 2008 – Permalink –

Match Format Paste

Copy/Paste formatting in Word, PowerPoint or Excel



When you copy information from a Web page or another document, the formatting will also be copied.

To match the formatting of the target document, copy the text and place the cursor where you want to insert the copy.

Then, go to Edit>Paste Special, and select the Unformatted Text option.
(Click the arrow under Paste in the Clipboard group on the Home tab in 2007)

The clipboard text will be pasted to match the target.

Another way when using Word 2002 + is to click on the "Smart icon" that appears at
the lower right corner of the pasted text. You can then choose to keep the original formatting, match the destination formatting, keep text only, or apply a new style.

An additional way to transfer just the formatting between documents is to highlight the text with the formatting you wish to copy and then hold down the Ctrl key and the Shift key and press the C key (Ctrl+Shift+C). Release the keys. Select the text you want to have formatted. Hold down the Ctrl key and the Shift key and press the V key (Ctrl+Shift+V). Only the formatting is copied, not the text.
In Excel use Edit>Paste Special and select the "Formats" option.

TechTrax:
What's So Special About "Paste Special"?
by Linda Johnson, MOS

Paste Special can also be used with graphics.

You can change Word's default behavior; choose whether to paste Inline or Floating.

Microsoft Word MVPS FAQ

[Edited entry from 10/31/2004]



See all Topics

Labels:


<Doug Klippert@ 6:47 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

  Monday, July 02, 2007 – Permalink –

Data Validation

Control input



Mark Rowlinson provides a discussion by Kid Van Ouytsel that does an excellent job explaining data validation. He has also constructs a sample workbook that you can download and play with.

Data Validation
"Data validation is a tool that can help you control the input/changes someone can make in a spreadsheet. It can help you or your users to make choices, guide them to make relevant input/changes, or restrict input to a specific type of data or structure. It can help you or your users to save time and to keep formulae working properly."


Also:

Contextures:
Excel -- Data Validation

Ozgrid:
Data Validation and Conditional Formatting

MrExcel:
Data Validation

Microsoft Office Online:
Overview and samples of data validation

Microsoft Downloads:
Sample: Data Validation



See all Topics

Labels:


<Doug Klippert@ 5:41 AM

Comments: Post a Comment

  Friday, June 08, 2007 – Permalink –

Location Indicator

Point to the spot


Here's a link to the code that produces conditional formatting on the fly to the cells in the current row and column.



Color banding location



See all Topics

Labels: , ,


<Doug Klippert@ 5:55 AM

Comments: Post a Comment

  Monday, May 07, 2007 – Permalink –

Hide Dups

Format don't show



Duplicate entries can be formatted to "disappear", but still be available for computation.
  1. Select the range

  2. Goto Format>Conditional Formatting

  3. Select Formula Is

  4. Enter
    =A2=A1

  5. Click the Format button.

  6. Select a font color to match the cell background color.

  7. Click OK and OK


Dups can also stand out:

  1. Select the range

  2. Go to Format>Conditional Formatting

  3. Choose Formula Is

  4. Enter
    =COUNTIF($A$2:$A$100,A2)>1

  5. Click the Format button.

  6. Select a font or background color for highlighting.

  7. Click OK and OK


Hide Duplicate Values

Also:
Hide Records with Duplicate Cell Entries



See all Topics

Labels: ,


<Doug Klippert@ 6:52 AM

Comments: Post a Comment

  Tuesday, February 13, 2007 – Permalink –

Open 2007 in 2003-2000

Not everyone is going to jump at once


How to open and to save Word 2007, Excel 2007, and PowerPoint 2007 files in earlier versions of Office
Microsoft Office Word, Microsoft Office Excel, and Microsoft Office PowerPoint versions 2000 through 2003 cannot natively open documents that are stored in the Office Open XML Formats in 2007 Microsoft Office programs.

You can install the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats to open or to save 2007 Office files.

Microsoft Office XP and 2003
Word, Excel, and PowerPoint

After you install the Compatibility Pack, you can use your existing version of Word, Excel, and PowerPoint to open, edit, and save the file formats that are new to Word 2007, Excel 2007, or PowerPoint 2007. For example:
  • You can open Word, Excel, or PowerPoint 2007 files by double-clicking them exactly as you do with your existing Word, Excel, and PowerPoint presentation(s).
  • You can save Word, Excel, or PowerPoint 2007 files by clicking the Save button in your version of Word, Excel, or PowerPoint.


Microsoft Office 2000
Word, Excel, and PowerPoint

Word 2000
  • After you install the Compatibility Pack, you can open, edit, and save the document file formats that are new to Word 2007 within Word 2000.
  • You can open files in the formats that are new to Word 2007 by double-clicking the files.
  • You can save files in the formats that are new to Word 2007 by clicking Save in Word 2000.


Excel 2000 and PowerPoint 2000
  • After you install the Compatibility Pack, you can open and save the file formats that are new to Excel 2007 and to PowerPoint 2007 from the Microsoft Windows operating system.
  • You can open files in the formats that are new to Excel 2007 and to PowerPoint 2007 by double-clicking the file on the desktop, in the My Documents folder, or in Microsoft Windows Explorer.
  • You can save files in the formats that are new to Excel 2007 and to PowerPoint 2007 by right-clicking an Excel 2000 file or a PowerPoint 2000 file and then clicking Save As.


Compatibility Pack Functions

Compatibility Pack Download




See all Topics

Labels: ,


<Doug Klippert@ 7:29 AM

Comments: Post a Comment