Excel Tip: The intelligent Format Painter

The solution: The intelligent Format Painter
I started by showing the user how to save time by using the Format Painter. We formatted the first row as light green, selected that row, and double-clicked the Format Painter. (It's the icon in the Standard toolbar that looks like a paintbrush.) Then, to "paint" additional rows, all we had to do was click on the row's number, and the Format Painter copied the "light green fill" attribute onto the target row.

After the user gushed for a bit about how cool she thought the Format Painter was, we kicked it up a notch. Because the Format Painter is smart enough to recognize patterns when you've selected two or more rows, we were able to format every other row in her spreadsheet with one swipe of the mouse.

First, we selected two rows—the first, plain row of data and the row that had been formatted with a light green fill. Next, we clicked the Format Painter once, and clicked on the first row number below our selection, and dragged down the column. Figure B shows what the sheet looked like while we were copying. (Notice the paintbrush icon near the bottom of the range we were formatting.) Figure C shows the final result.

Figure B
When you select two or more rows and click the Format Painter, the program copies the formatting from each row.

Figure C
When you use the Format Painter to apply formatting from two or more rows, Excel formats subsequent rows using the same patterns. (In this case, the pattern is one plain row followed by one shaded row.)

Excel Tip: Love your [Ctrl] key--for three reasons

Love your [Ctrl] key--for three reasons
This three-fer will demonstrate why you should love and use your [Ctrl] key:
Reason 1--Fast navigation. When you press [Ctrl] and any arrow key (north, east, south, or west), you jump to the last populated cell in that direction. Think of using the [Ctrl]-arrow key shortcut as an alternative to pressing [Page Down] to find the bottom row of a data set or pressing [Tab] to find the last column. Bonus tip: Hold down the [Shift] key while you press any [Ctrl]-arrow key shortcut to select all the cells between where you are and where you jump with the [Ctrl] key.
Reason 2--You can make noncontiguous selections. That's a fancy way of saying you can select any cells you want, regardless of whether they're contiguous--next to each other in a row or column. Hold down the [Ctrl] key while you click on a cell or click and drag through a range of cells. As long as you hold down the [Ctrl] key, you can click and select to your heart's content.
Combine this tip with tip #7, and you can use the AutoCalculate tool to analyze any combination of individual cells or blocks of cells. Figure O shows our screen when we used AutoCalculate to sum the cells we selected while holding down the [Ctrl] key.
Figure O
When you hold down the [Ctrl] key, you can select any cell or block of cells, and AutoCalculate will return results based on those noncontiguous selections.
Reason 3--Fast data entry. Suppose you want to put the same string, number, or formula into two or more cells. Using the old-fashioned approach, you'd type the string, number, or formula into the first cell and then copy and paste that entry into the destination cells. But there's a little-known time-saving tip that makes short work of placing the same entry in multiple cells. First, select all the cells you want to populate. Type the entry, but don't press [Enter]. Instead, press [Ctrl][Enter]. When you do, Excel will copy what you typed into all of the selected cells.

Excel Tip: AutoSum Shortcut key

AutoSum Shortcut Key

Instead of using the AutoSum button from the toolbar,

you can press Alt and = to achieve the same result.

Try it here :

Move to a blank cell in the Total row or column, then press Alt and =.


Select a row, column or all cells and then press Alt and =.

Jan Feb Mar Total

North 10 50 90 150

South 20 60 100 180

East 30 70 200 300

West 40 80 300 420

Total 100 260 690 1050

Excel Tip: Age calculation in excel

You can calculate a persons age based on their birthday and todays date.

The calculation uses the DATEDIF() function.

The DATEDIF() is not documented in Excel 5, 7 or 97, but it is in 2000.

(Makes you wonder what else Microsoft forgot to tell us!)

Birth date : 9-Jan-09

Years lived : 0  =DATEDIF(C8,TODAY(),"y")

and the months : 10  =DATEDIF(C8,TODAY(),"ym")

and the days : 21  =DATEDIF(C8,TODAY(),"md")

You can put this all together in one calculation, which creates a text version.

Age is 0 Years, 10 Months and 21 Days

 ="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"

Another way to calculate age

This method gives you an age which may potentially have decimal places representing the months.

If the age is 20.5, the .5 represents 6 months.

Birth date : 19-Feb-79

Age is : 30.78  =(TODAY()-C23)/365.25

Excel Tip: SUM using names

You can use the names typed at the top of columns or side of rows in calculations
simply by typing the name into the formula.
Try this example:
Go to cell C16 and then enter the formula =SUM(jan)
The result will show.
This formula can be copied to D16 and E16, and the names change to Feb and Mar.
Jan Feb Mar
North 45 50 50
South 30 25 35
East 35 10 50
West 20 50 5
Total 130 135 4
"=Sum(Jan)" "=Sum(Feb)" "=count(Mar)"
If it does not work !
The feature may have been switched off on your computer.
You can switch it on by using Tools, Options, Calculation, Accept Labels in Formula.