Courtesy of an article written by “Mr Lincoln Spector” of – Windows Secrets Newsletter fame..

 

Tips & Keyboard Shortcuts for Excel 2016

If you work a lot with spreadsheets, you know how difficult they can be once you get beyond simple tables and equations. And you know that data entry can be boring beyond belief. The more complex the table, the more confusing it is to evaluate. Shouldn’t there be quicker and easier way to do some of these tasks?

Microsoft Excel has a lot of nifty shortcuts that can help relieve the tedium of data entry and provide clarity with complex tables. Here are six features built into Excel 2016 that can ease creating spreadsheets and understanding the ones you (and other people) create.

And after those six, I’ll treat you to 20 keyboard shortcuts that – if you can memorize them – will make your jobs lighter.

Some of these tricks will be found in earlier versions of Excel; others are exclusive to Excel 2016.

Tell Me What You Want To Do

Do you always remember how to create a pie chart, freeze the left-hand column, or name a range? I didn’t think so. Excel’s “Tell me what you want to do” can help you complete a chore even while you’re suffering from a brain freeze.

You’ll find the “Tell me what you want to do” feature on the tab bar, just above the ribbons, and to the right of the View tab. You can’t miss it; it has a light bulb icon and the words “Tell me what you want to do.”

You can also get there by pressing Alt-Q.

 

 

Once you’re in the field, type whatever you want: pie chart, freeze, name a range, or whatever. Excel will guess what you want, and if it’s not sure, it will offer options. Once you select what you want, the appropriate dialog box will come up.

Easing Data Entry

Nothing is more tedious than data entry. Typing names, dates, and numbers is about as interesting as counting individual grains of rice.

Luckily, Excel has features that can ease and speed up this dull job.

AutoFill enters values based on a pattern. It’s been around for a long time, and you probably already know about it. But in case you haven’t, I’ll give you a quick rundown.

Type 1 in a cell, then 2 in the one below it. Select the two cells. Move your mouse pointer to the bottom-right corner of the selected block of cells. When the mouse pointer turns into a dark cross, pull down to select other cells below these two. Those cells will contain 3, 4, 5, and so on.

AutoFill can do a lot more than 1, 2, 3. If you enter 3 and 6 and pull down, you’ll get 9, 12, and 15. If you enter 52 and 54,AutoFill can continue the series by twos. See how first I entered 52 and 54, then selected the block of cells I wanted filled in:

 

And then Excel filled in the remaining cell values for me.

 

This also works with dates, and even spelled-out months (January, February, etc.). But it doesn’t work with everything. When I tried it with Groucho and Harpo, Excel proved that it knew nothing about Chico and Zeppo Marx.

Flash Fill is a smarter version of AutoFill. It looks at patterns and guesses how to fill things in.

Try this: Enter a series of full names in a column. Then, in an adjacent column, enter the first name of the first person on the list. Then select Data>Flash Fill. Everyone else’s first name will appear in that column.

Flash Fill has other tricks. You can use it to convert a ten-digit number into a phone number separated with hyphens. Or you could separate the zip codes from a list of addresses.

The Flash Fill icon isn’t always easily identifiable on the ribbon – it depends on the size of your window. Its immediately to the right Text to columns. You can also launch it from the keyboard by pressing Alt-A, followed by FF.

One more data entry trick: You can type two lines in one cell. After typing what’s on the first cell, press Alt-Enter to get a new line in the same cell.

 

Understanding the Numbers

You’ve got a beautiful spreadsheet. The names and numbers are all correct. But can you easily study the data and make the decisions?

Quick Analysis helps you focus on the important numbers. When you select a range, you might notice a strange little icon in the lower-right corner of the selection. Click it, and up pops a selection of options that may help you see the data better. Quick Analysis can show charts, colors, and other visual aids to help you understand your data.

 

Not every Excel range will show the Quick Analysis icon. The reason is simple: Not every Excel range has the right kind of data.

You probably already know that Excel’s charts can help you analyze your spreadsheet, but do you know how easy it is to Pull up a quick chart?

All you need do is select the data in question, and press F11. The chart will pop up in a new tab to the left of the tab you were on. To get back to the numbers, just click the original tab. (I’m talking, of course, about the tabs at the bottom of the Excel window.)

Keep Your Hands on the Keyboard – Useful Keyboard Shortcuts

Once you’ve memorized keyboard shortcuts, they can speed up your work significantly. Here are some useful Excel shortcuts you may not know.

Some of these are discussed above, but I’m repeating them here so you can easily find them.

Ribbon shortcuts bring up the ribbon of your choice, and display addition shortcut prompts, in black squares, so you can select an option on the ribbon without grabbing the mouse.

Home tab Alt-H
Insert tab Alt-N
Page Layout tab Alt-P
Formula tab Alt-M
Data tab Alt-A
View tab Alt-W
Choose a fill color Alt-H, H
Center contents of current cell(s) Alt+H, AC
Flash Fill Alt-A, FF
Tell me what you want to do Alt-Q

Navigation and data entry:

Moves focus to last cell in arrow direction that’s not empty Ctrl-any arrow key
Expands focus to last cell in arrow direction that’s not empty Ctrl-Shift-any arrow key
Moves from one tab to another Ctrl-PageUp or PageDown
Inserts a new line within the cell Alt-Enter

Formatting cells and numbers:

Open Format Cells dialog box Ctrl-1
Converts any number(s) into currency Control-Shift-$
Converts any number(s) into dates Control-Shift-#
Hide selected rows: Ctrl+9
Hide selected columns Ctrl+0
Pull up a quick chart F11

Courtesy of an article written by “Mr Lincoln Spector” of – Windows Secrets Newsletter fame..

Comments are closed.