Kroll Design
Needham, Massachusetts

781.910.3694
info@KrollDesign.net

KrollDesign.net

Excel Tutorials

 
« Return

See also:

Cell padding
Excel crams left-justified content up against the cell grid line. So if you want to give it a little breathing room like this:



Do this:

Format...Cells...Alignment...Horizontal...Left (indent)...1...OK


Clear contents: what's a quick way to empty the contents of a bunch of cells?
Select all the cells, then hit the delete key on your keyboard.

Conditional formatting: shade every other row
Select all cells. Choose Format...Conditional formatting from menu. Click plus sign. Style: classic. Use a formula to determine which cells to format. Use formula of =MOD(ROW(),2)=1

Conditional formatting: bold each row that has an "x" in column A
Select all cells. Choose Format...Conditional formatting from menu. Click plus sign. Style: classic. Use a formula to determine which cells to format. Use formula of =$A1="X"
(make sure there's no dollar sign before the "1" in "$A1", which makes the row a relative reference)

Date format: how to convert one or more cells from 1/10/11 to 10-Jan-11:
Select the cells then do Control + Shift + #
Use the Control key on both PC and Mac. Don't use the Command key on the Mac.

Date format: (under construction) Examples of using date functions for a given date that is located in cell A2 for example:
2 months later: =EDATE(A2,2)

Date or month series (Mac):
Type the month or date in a cell, then select the cell and drag down in the lower right corner:

If you want the dates to be the same (fill down, not fill series), hold down the Option button as you drag. If you drag with your other mouse button, a popup appears with more options, such as to 'Fill Months'.

DATEDIF function: If you want to use the Excel "DATEDIF" function, and it's giving you an error message of #NUM!, what is probably the problem:
DATEDIF requires the first date specified (the start date) be less than or equal to the second date specified (the end date). So if you try =DATEDIF(A1,A2) where A1 is 1/1/2011 and A2 is 1/20/2011, that's fine, but if A2 is 4/3/1947, then that won't work -- the end date can't be earlier in time than the start date.

Edit cell's existing contents (Mac):
Typing into the currently selected cell blows away whatever was in there, unless you click on the contents in the formula bar and edit there. To be able to edit the existing contents of the current cell, use keyboard shortcut Ctrl-U (not Command-U).

Fill down: a way to do "fill down" with one double click:
Type the first value or formula in the series. Double click in the lower right corner of the cell.

Go to end of worksheet:
Command (Mac) / Control (Win) + Shift + down arrow

Hide unused columns:
Command (Mac) / Control (Win) + Shift + right arrow
right click on selected columns and choose "Hide"

Hide row: How to hide rows in Excel 2011 for Mac:
Format...Row...Hide

Hide unused rows:
Command (Mac) / Control (Win) + Shift + down arrow
right click on selected columns and choose "Hide"

Insert current date (Mac):
Control + ;

Insert current time (Mac):
Command + ;

Insert both current date and time in same cell (Mac):
Control + ; then Spacebar then Command + ;

Line break: force line break in a cell:
Windows: select cell, position cursor in formula line where you want line break, do Alt-Enter
Mac: select cell, position cursor in formula line where you want line break, do Control-Option-Return

Return moves to "next" cell (Mac):
When hit the Return key, you typically move down to the next cell below. If you'd rather that you move to the right, say so in Excel...Preferences...Edit:

In Windows, it's the Enter key, not the Return key, and you get to preferences screen in a slightly different manner, but same idea.

Time format: If you add up a column of times, and the total is displaying incorrectly when the total is more than 24 hours, how to fix:
Right click on the cell containing the incorrectly formatted sum, select 'Format Cells', select 'Custom', then type '[h]:mm'. The square brackets around the 'h' means that it shows values higher than 24.

Time series of every 30 minutes:
Type the first time in the series. In the next cell type in a time of 30 minutes later (or whatever you want). Select BOTH cells and let go. Drag down from the lower right corner of the cell pair.

Today's date, that stays current as time marches on:
=TODAY()

Today's date and time, that stays current as time marches on:
=NOW()

Unprotect cell: How to unprotect a cell that is already protected in Excel 2011 for Mac:
Select cell then choose Format...Cells...Protection, then uncheck "locked" checkbox
Back to top
 



©2012 Kroll Design    info@KrollDesign.net    781.910.3694
Last modified: 07/5/2015 10:06 PM