Excel tip of the day how to#
Excel tip of the day update#
Excel tip of the day download#

A Print Area is actually a Named Range, so you can create a Dynamic Print Area using the OFFSET function (see the article ' Dynamic Named Ranges in Excel').

Excel tip of the day update#

Update the print settings on multiple worksheets at the same time by selecting multiple tabs (hold down Ctrl as you click on the tabs) and go to Page Layout > Page Setup.

If a portion of worksheet is not printing, you may need to redefine the print area via Page Layout > Print Area.

Go to Page Layout > Page Setup to set them up.

Excel can do headers and footers just like Word, including auto page numbering and dates.

Print a square grid in Excel: Ctrl+ a, Arial 10pt font, Column width = 1.71, Row Height = 12.75.

Have a data table spanning multiple pages? Print the header rows on all pages by going to Page Layout > Page Setup > Sheet tab > Rows to Repeat at Top.

Switch to Page Break Preview via the View menu to change the position of the page breaks in your worksheet.

Print a chart object (such as a timeline) across multiple pages by selecting the cells surrounding the chart rather than the chart object itself.

Fit a worksheet to one-page wide by setting the Print Scaling and leaving the number of pages tall blank.

Choose a specific range of cells to print by setting the Print Area.

Undo automatic formatting after inserting a row by selecting 'Clear Formatting' from the paintbrush icon that appears next to the row.

Inserting a new row will copy the formatting from the previous row (as well as sparklines and maybe other stuff) unless you first copy a blank row from elsewhere in your spreadsheet and insert the copied row instead.

If you see '#' in a cell, it means the column is too narrow to display the value.

To enter a value as a fraction like 3/4 without having it autoformatted as a date, include a leading zero and a space (0 3/4).

Or, select a horizontal range of cells first and see what happens when you press Enter. Go to File > Options > Advanced to move in a direction other than down.

After pressing Enter in Excel, you will typically move down one cell.

A menu of fill options will show up after using the Right mouse button to drag a Fill Handle (instead of the Left mouse button).

Press F2 prior to using arrow keys when editing a formula in a dialog box such as a conditional formatting rule (to avoid inserting references).

Right-Click and Drag on the border of a selected cell or range to open a hidden menu that lets you paste values.

Handy for lists of names, comma-delimited data, etc.

To split data in a cell into multiple columns, go to Data > Text to Columns.

Fast Fill/Copy: Double-click the fill handle (the little 'square') of a selected cell to fill (copy) down the same number of rows as the adjacent column.

Press Ctrl+Colon ( Ctrl+ Shift+ ) to quickly enter the current time in a cell.

Press Ctrl+ to quickly enter today's date in a cell.

Use the keyboard shortcut combination F2 then CTRL+ SHIFT+ HOME to quickly select an entire formula within a cell.

Shortcut to Copy the Current Row and Insert the Copied Row above it: SHIFT+ SPACE then CTRL+ c then CTRL+ SHIFT+ +.

enter the 10 and the 20 then select both and drag the fill handle.

To quickly fill a range with a pattern such as 10,20,30,40.

enter the 4 in the first cell and then hold down CTRL as you drag the fill handle. Use Ctrl+ r to copy the cell immediately to the left.

Use Ctrl+ d to copy the cell/row immediately above the selected cell/row.

Use Ctrl+ Enter to quickly fill a range of cells with a value or formula after entering a value in one of the cells.

Excel tip of the day download#

See this article for a list of my favorites and to download a 1-page reference sheet. Learn and use other important keyboard shortcuts.Press ESC while you are editing the text or formula in a cell to exit the cell and cancel any changes that you may have made.Insert a Line Break inside a cell by pressing Alt+ Enter.Press F2 to edit the currently selected cell (will place the cursor at the end of the text/formula).

Learn to use the combination of CTRL, ALT, and SHIFT along with the Arrow Keys to navigate and select text.

Excel tip of the day how to#

However, because of the changes to the user interface in newer versions of Excel, explanations that say how to get to something via a menu (such as how to get to print and page setup options) may be different for different versions of Excel.įor 140+ detailed tips and tutorials that provide the equivalent of taking an intermediate level Excel training course, see the Spreadsheet Tips Workbook. Most of these tips apply to nearly any version of Excel. I've compiled some of these Excel tips below so that you can soak in a bunch all at once. A few years ago, I started publishing occasional Excel tips via Twitter.