Right-click on the navigation arrows in the lower left-hand corner of the screen to display a worksheet selection menu.
Tag: Excel
Dec 16
Update Multiple Cells At Once
Update multiple cells at once: select two or more cells, type an entry in the first cell, and then press Ctrl-Enter.
Dec 05
Copying Cell Contents Down
Double-click fill handle to copy current cell down, stops at the first blank in adjacent column. Or select cells & press Ctrl-D.
Dec 02
AND() Function
The AND function allows you to tests for two or more conditions in an IF statement. =IF(AND(A1>=1,A1<=100),"Between 1-100","Other")
Nov 13
Concatenation Trick
Use an ampersand instead of the CONCATENATE function. The formula =A1&”-“&B1 is much easier than =CONCATENATE(A1,”-“,B1)
Nov 12
Formatting Sets Of Rows
Use this formula with Conditional Formatting to highlight alternating sets of 5 rows: =MOD(CEILING(ROW(),5),2)
Oct 19
Create A Series Of Month End Dates
Create month-end date series- Cell A1: 10/31/09, Cell A2: =DATE(YEAR(A1),MONTH(A1)+2,0), copy down as needed.
Oct 05
Number Formatting Trick
Press Ctrl-Shift-! to apply the Number format, e.g. 1,234.56, to one or more cells that you've selected.
Sep 29
Data Entry Trick
Press Alt-Down Arrow to get an in-cell drop-down list of adjacent entries in the current column
Aug 20
Hiding/Unhiding Columns
Press Ctrl-0 (zero) to hide a column. Use the F5 key to navigate to a hidden column and then press Ctrl-Shift-0 (zero) to unhide.
Aug 10
Resetting Excel 2003 Color Palette
Excel 2003 tip: If you suddenly can't change colors on a cell, reset your palette. Choose Tools, Options, Color tab, and then click Reset.
Jul 30
Workbook/Worksheet Navigation Tricks
Press Ctrl-Tab to switch between open workbooks. Use Ctrl-Page Up/Ctrl-Page Down to switch between worksheets.
Jul 29
Date Series Formula
This formula allows you to create a series of dates that alternate between the 15th and the last day of each month: =IF(B1=DATE(YEAR(B1),MONTH(B1)+1,0),DATE(YEAR(B1),MONTH(B1)+1,15),DATE(YEAR(B1),MONTH(B1)+1,0))
Jul 17
IFERROR Function
Resolve #DIV/0! errors in Excel 2007 with =IFERROR(A1/A2,0) – puts a zero in the formula cell if either A1 or A2 are blank.