Excel Tips Weekly

Author

Updated

11/13/2018

Released

1/16/2015

This tips-based course will show Excel users productivity-boosting tricks, cool hidden features, need-to-know functions, and advanced content on subjects such as using PivotTables for data analysis. Tune in every Tuesday for a new tip from expert Dennis Taylor. Each tutorial is a short, self-contained lesson guaranteed to give you new insights into Excel.

Note: Because this is an ongoing series, viewers will not receive a certificate of completion.

Skill Level Appropriate for all

25h 8m

Duration

1,695,009

Views

Show MoreShow Less

- [Instructor] Excel has a ton of keystroke shortcuts.Most of them involve the Control key,but a number of them do involve the Alt key.And there's no special significance to using the Alt key,it's just that a number of these are not widely known.On some keyboards and particularly on laptopsyou might be able to zoom in and outholding down Alt + Ctrl+ press the minus key to zoom backor the plus key to zoom in.Just trust that doesn't work on all keyboards.AutoSum, really handy and there's a button for it.On the next worksheet over if we want a total in G2we can either press the AutoSum button,a lot of you know it's on the right side of the Home tabor on the left side of the Formulas tab,but we can also press alt + =.

And in this situation we'd press Enter.And similarly, that works the samewith the button or the keystroke shortcuts,this data's highlighted, how do we get a total?Alt + =, like that.When you've got multiple cells highlighted,and by the way you couldbe doing this strictly keyboard here,I wanna highlight this cell and the three below it,I'd hold down the Shift key, press the down arrow.And now for the total, Alt + =.And pressing Control + Z to undo that.If these cells were highlighted, like this,we could press Alt + =, or the AutoSum button, once.Alt + =, there we go.

Now I wanna say the following,do not type = S-U-M.Of course, it's not wrong,but why bother if you can press Alt + =, and you can.I might be wanting to add up cellsfrom different parts of the screen here,but why type this first?Instead press Alt + =,then highlight the cells that are relevant.I wanna add up those cells, comma,and some of these right here, Enter.And in fact, could we say never type = S-U-M?Well, we probably could,because Alt + = gets us there faster.

When you're creating data on a new listsometimes you have some wrap text needs.You don't have to go into any commands though.For example, I've got a new list here,I'll put in Name right there.And here I want to put in 2018 Salary,but I want 2018 on top of Salary within the same cell.Type 2018, I'll press Alt + Enter,and then Salary.And in some situationsyou might even be pressing Alt + Enter againwhen you've got three or four wordsyou wanna stack up on top.Press Enter, looks like this.You might consider later too selecting row oneand on the Home tab here usethose vertical alignment buttons.

In other words, instead of on the bottom,how about in the middle or on top?Something like that.So Alt + Enter allows us to create a new line within a cell.And it's always best within a listto have your titles in a single row.That makes working with Excel sortingand other data management tools much simpler and easier.I've got another list to the right here, HR List,and I wanna copy some of this data to a new sheet,send it to somebody else eventually,but I don't want to copy columns D and E.So I'll drag across columns D and E, right-click,and Hide that data.

And for this particular copyI don't wanna be copying the peoplefrom the Logistics department,so I'll highlight these rows right hereand Hide the data.Right-click, Hide.When you copy data from a listif you've got hidden rows or columnsyou're gonna be getting everything,unless this is a filtered list.This is not a filtered list,I simply hid the data.So how do we select all the data here?Control + A, that's handy.We'll copy this with Control + C.I wanna go to a new sheet.Bottom of the screen in the status bar there's a plus,we add a new sheet.

I'll do the paste here, Control + V to paste.And what do we get?Well, we got the social security and phone,we also got the Logistics people.So that didn't work the way we would've wanted at all.So let's go back to the list.If you're about to copy a listand it's not a filtered list,if you have any hidden rows or columns within the listyou're gonna get everything unless you selectthe visible cells only by pressing alt + ;,that selects the visible cells only.Then we'll press Control + Cand I'll go back to that same sheet to the rightand click out here.

Control + V to pasteand there we see it, without phone numbersand social security departmentand without the Logistics people.Alt + ; selects visible cells only.And on this last sheet here called Sales Reportwe'd like to have a chart heredisplaying this data right here.And a quick way to create a chartafter the data's highlighted is Alt + F1.There it is.A Design tab up there.Might use a black background, that shows up better.We can move this chart around by draggingjust inside the border or the border,but when we're aligning the edges,this works best from the corners,when you're dragging a corner you can make this wider,taller, et cetera, that sort of thing,but if you hold down the Alt keythis is as if the edges have like a magnetic attractionto the cell boundaries in the background.

And when it looks good let go of the mouse first.And this works best doing from opposite corners.So I want this to be over here somewhere.Hold down the Alt key while I'm doing this,there we go, let go of the mouse, there we are.And so those edgesare perfectly lined up with cell boundaries.Not the most critical thing we do in Excel,but handy at times.And when we're finished working with Excelwhat do we do?We want to close it.How about Alt + F4?And that takes us down that path.I'm not gonna do that right now,but I'll zip back to that first sheet therejust as a reminder.These are some handy keystroke shortcuts,what they all have in common is they use the Alt key.

Resume Transcript Auto-Scroll

Q: Why can't I earn a Certificate of Completion for this course?

A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.