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.




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
excel

Labels: Customize, Formats, Tips, Tutorials

Month Day, Day Month

"QDE is a fully-functional Excel Add-in that provides quick input of dates, in all international formats. It handles quick data entry interpretation and reflects the three interacting issues of Date System, Day, Month Year ordering, and number of digits used in the quick date entry. With QDE you enter just as many digits as needed to clearly identify the date, QDE will do the rest."

Also see:

Chip Pearson:
Date and Time Entry

MathTools.net:
Time and Date

And:
Date Arithmetic




See all Topics
excel

Labels: Formulas, Functions, Tips, Tutorials

Shake hands with a worksheet

• Add a Get Started tab to the Ribbon
  
  
• Take advantage of the Quick Access Toolbar
  
  
• Minimize the Ribbon, maximize your workspace
  
  
• Choose your own default font
  
  
• Restore missing worksheet tabs
  
  
• Freeze panes here, there, and everywhere

Labels: 2007, Tutorials

More dimensions than Star trek

When a company accumulates a great deal of information, it becomes un-wieldy to work with just basic Excel or Access databases.

There is a database concept called on OLAP cube (On-Line Analytical Processing).

This multidimensional collection of data can be thought of as a 3-D pivot table viewed from flat land.

MSDN:
Just What Are Cubes Anyway?
(A Painless Introduction to OLAP Technology)

OLAPReort.com:
What is OLAP


Wikipedia:
OLAP

Wang.se (Wang Sweden) a Swedish software company:

Create an OLAP Cube



See all Topics
excel

Labels: General, Tutorials

Split the sheets (?)

Recently a large group of friends and I went on a multi-city tour of Europe that lasted a couple weeks. There was a lot of planning that went into this trip and responsibilities for booking different legs of the trip were spread out across the group. How do you efficiently handle paying back multiple people while getting reimbursed for your costs at the same time?

Split Costs

You can download the spreadsheet here:Split_Costs.zip




See all Topics
excel

Labels: General, Templates, Tips, Tutorials

HEADING

Excel is a great tool to use when you need to take data in one format, manipulate it into another format, and push the results along to another process, e.g. a database. In this context, Excel is a great landing pad or middle man, serving as a data transformation tool to move data from one system to another.

This example considers a sample data set and walks through the steps to clean up the data and perform various transformations on the data set to massage it into a more desirable format.

• Import the data and don't accidentally drop the leading zeros!
  
  
• Formatting SSN
  
  
• Inserting hyphens
  
  
• Fixing up names
  
  
• Lower case E-Mail Address
  
  
• Format Home Phone
  
  
• Inserting parenthesis & hyphens
  
  
• Trim extra spaces from Address
  
  
• Add City values to the new table
  
  
• Make all State value upper case
  
  
• Fill in Postal Code
  
  
• Finalize the values

Labels: Tips, Tutorials

All(most) all you need to know

• Add numbers
  
• Subtract numbers
  
• Multiply numbers
  
• Divide numbers

Labels: Formulas, General, Tutorials

Basic tutorial

"This practical spreadsheet lesson offers easy answers to life's perplexing math problems like How much will my dream car really cost after financing?

Students will calculate the cost of purchasing their very own Lamborghini sports car and determine if the ultimate price tag is really worth the investment. "

Dream Car

Also:

Loan templates
Basic Financial Calculations



See all Topics
excel

Labels: Functions, Tutorials