Category Archives: Tips & Tricks

Keyboard Shortcuts in Excel for Data Entry & Editing

In this earlier post, a couple dozen keyboard shortcuts were listed to help you navigate around your spreadsheets more quickly and smoothly. The following list provides another set which make data entry and editing quicker. Remember, don’t try to remember the whole list at once. Pick two or three that are common actions you use. Memorize those and practice, practice, practice. Then add a couple more to your repertoire.  One more shortcut list may be found here.

P.S. The CTRL + Shift + ) shortcut to unhide columns has a Windows Vista and Windows 7 operating system conflict but can be adjusted using these instructions.

 

ACTIONSHORTCUT
Edit cellF2
Create formula= (equal sign)
CopyCTRL + C
CutCTRL + X
PasteCTRL + V
Paste SpecialALF H + V + S
Repeat last actionCTRL + Y
UndoCTRL + Z
Fill DownCTRL + D
Fill RightCTRL + R
FindCTRL + F
ReplaceCTRL + H
Enter AutoSum formulaALT + '+' (plus sign)
Show formula/valueCTRL + ~
Start new line in same cellALT + ENTER
Delete cell contentDELETE
Clear cell content & formattingALT + H + E + A
Delete cell and move adjacent cellsCTRL + - (minus sign)
Insert cell and move adjacent cellsCTRL + SHIFT + '+' (plus sign)
Insert row/columnCTRL + SHIFT + '+' (plus sign)
Delete row/columnCTRL + - (minus sign)
Hide rowCTRL + 9
Unhide rowCTRL + SHIFT + (
Enter current dateCTRL + ;
Enter current timeCTRL + SHIFT + :
Spell CheckF7

Keyboard Shortcuts in Excel for Cell Navigation

Keyboard_Shortcuts_2

Driving along winding roads through rural Ireland, my son-in-law assured our family that we were on the right path to our destination. We were glad to hear that the twists and turns were actually “shortcuts” leading to arrival at our destination earlier than planned. Sometimes shortcuts don’t always turn out so well, as this sign at the harbor in Dingle seemed to indicate!

In my work, I am always on the lookout for shortcuts to speed up data entry, such as using Auto Fill functions. For overall navigation, I rely on keyboard shortcuts to keep my hands on the keyboard and reduce the use of the mouse. There are dozens and dozens of keyboard shortcuts and a brief reading of a long list has not been a fruitful learning technique for me. Instead I search the list for two or three actions that I use the mouse for most of the time and then practice using those specific shortcuts until they become second nature. Here’s a group of navigational shortcuts. More shortcut lists may be found here and here.

 

ACTIONSHORTCUT
Move to end of rangeCTRL + arrow keys
Move to cell A1CTRL + Home
Move to column A in same rowHome
Move to cell aboveSHIFT + ENTER
Move to cell belowENTER
Move to cell to rightTAB
Move to cell to leftSHIFT + TAB
Stay in same cellCTRL + ENTER
Move to next sheet tabCTRL + PgDn
Move to previous sheet tabCTRL + PgUp
Move to next workbookCTRL + TAB
Split screenALT + W + S
Move to next pane F6
Freeze paneALT + W + F
Minimize/Restore ribbonCTRL + F1
Close windowCTRL + W
Close ExcelALT + F4
Select a range of cellsSHIFT + arrow keys
Select contiguous rangeSHIFT + CTRL + arrow keys
Select columnCTRL + Space
Select rowSHIFT + Space
Select allCTRL + A

 

How to Center a Spreadsheet when Printing in Excel

When a small worksheet is printed for sharing by PDF or printing on paper, it will appear in the upper left corner of the page like this:

Formatting_2

A more professional presentation would center the information on the page. In Page Layout, there are options to center horizontally, vertically or both:

1. Click on the drop down arrow of Margins in the Page Layout tab. Select Custom Margins.

Centering_on_Page_2

2. Click on the Margins tab and then check Horizontally, Vertically, or both. Then click OK.

Centering_on_Page_3

In this example, centering horizontally looked best.

Centering_on_Page_4

How to Create a Perpetual Calendar in Excel

Perpetual_Calendar_1

Over the course of any workday, I might access up to 5 electronic devices that all synchronize my calendar. Yet if I need to look for a date later in the year, say June 15, I instinctively look at the paper 2013 calendar taped to the wall above my desk. I’m not quite sure what draws me to look at the paper, maybe it’s the alignment of the 7 columns of the days of the week or maybe it’s the perspective of seeing the whole year at a glance.

Nevertheless, it is a very ingrained habit and each year I tape a new calendar on the wall, usually a freebie that I saw online or one printed by a local vendor and given to us with their first shipment of the new year. This year, I decided to make my own in Excel and researched various methods for calculating the start day for each month. The cool thing about creating the months via formulas is that it becomes a perpetual calendar. Next year, all that is needed is changing Cell H7 to 2014 and voila, it recalculates all dates to the correct positions. It is sized to view nicely within Excel or saved to PDF. To print all on one page, use the “Fit all rows on One Page” option to fit the whole calendar on one piece of paper. Enjoy!

Download Perpetual Calendar in Excel

3 Easy Tips for Speeding up Data Entry in Excel

The common cliché that “Time equals money” seems a bit out of place in the non-profit world. Yet when money is not the end goal, the effective use of time can actually be our stock in trade. Entering lots of data into a spreadsheet can not only be tedious and mind-numbingly boring, it can also be fraught with errors. Using one or more of these tips speeds up the process and may also reduce errors. Of course, the speediest data entry method is likely importing data from another source, such as a contact management system or general accounting system or even linking directly to an external source through an API or ODBC. When that’s not an option, I love to make use of several Auto-fill methods in Excel.

Repeat data that already exists in earlier cells

Begin typing an entry of letters or letter – number combinations. When Excel recognizes the new entry as the same as an earlier one, it will begin auto filling. In this example, typing the letter “A” in the sixth row prompts the autofill of Apples. Press Enter to accept Apples or continue typing for a different value, such as Apricots.

Auto Fill 1

Note that Auto Fill will not work in cells containing only numbers, dates, and time.

Fill Down, Fill Up, Fill Right, Fill Left

Copying and pasting from one cell to one or more other cells is often the default action to duplicate data or formulas. However, if the source data or formula is adjacent to the recipient cells, the Fill option is much quicker. The Fill option is in the Editing group of the Home tab.

Auto_fill_6

Select the source cell and then the recipient cells. Then select the following options:

  • Source cell on top. Recipient cells below — Use Fill Down (shortcut – CTRL-D).
  • Source cell on bottom. Recipient cells above — Use Fill Up
  • Source cell on right. Recipient cells to the left — Use Fill Left
  • Source cell on left. Recipient cells to the right — Use Fill Right (shortcut – CTRL-R)

Auto_Fill_7

 Using a Cell’s Fill Handle

The Fill Handle actually provides 3 options in one. First, hovering over the bottom right corner of a cell displays a small cross + which is called the fill handle.

Click on the Fill handle and drag down or to the right and the additionally selected cells will be filled with the originally selected cell (either data or formula).

                 Auto_Fill_3                 Auto_Fill_5              

Releasing the mouse and clicking on the small auto-fill option box on the bottom left will reveal several options: Copy Cells, Fill Formatting only, or Fill without Formatting.

data_entry_tips_4

Second, with multiple rows of data, often a formula is created on one row that will be copied to all rows. Copy/Paste or Fill Down are my go-to methods, but I recently learned a trick that is even quicker. In the example below, the extended price formula in row 1 needs to be filled in every row. Hover over the bottom right of cell D2, then double click on the Fill Handle (+). The formula in that cell will Auto Fill through all of the rows of data. Super quick!

Auto_Fill_8

                                                                                         Auto_Fill_9

Third, often a series of data needs to be created in a column or row of cells. This series might be days of the week, months of the year, sequential years, or even a unique series of your own, such as general ledger categories. Begin with two cells completed as the first two entries of the series. Hover over the bottom right corner of the second cell. Click on the fill handle (+), drag up, down, right or left and the series will fill as many cells as are selected.

             Auto_Fill_10                                     Auto_Fill_11

Excel’s known series include days of the week and months of the year. Numbers, dates, and time will increment in just about any normalized pattern, such as bi-weekly payroll dates or end of quarter tax deadlines. To create a unique series such as budget areas (Sales, Marketing, Operations, Finance, Human Resources):

1. Select Options from the File Menu. Then select Advanced, scroll way down to the General section. Click on the Edit Custom Lists button.

Auto_fill_12                  Auto_Fill_13

2. Now begin typing in the List Entries your unique series, such as Sales, Marketing, Operations, Finance, Human Resources. Click Add and then OK. This list is now available to Auto Fill, not just in this worksheet but in other worksheets created in the same installation of Excel.

Auto_Fill_14