• #### 科普

SCIENCE

#### 英语

ENGLISH

#### 科技

TECHNOLOGY

MOVIE

FOOD

#### 励志

INSPIRATIONS

#### 社会

SOCIETY

TRAVEL

#### 动物

ANIMALS

KIDS

#### 卡通

CARTOON

#### 计算机

COMPUTER

#### 心理

PSYCHOLOGY

#### 教育

EDUCATION

#### 手工

HANDCRAFTS

#### 趣闻

MYSTERIES

CAREER

GEEKS

#### 时尚

FASHION

• 精品课
• 公开课
• 欢迎下载我们在各应用市场备受好评的APP

点击下载Android最新版本

点击下载iOS最新版本

扫码下载译学馆APP

#### 6个Excel实用小技巧

6 Excel Tips from the Mr Excel Book Giveaway Winners

Welcome to ExcelCampus.

My name’s John, and in this video I’m gonna share 6 tips that were submitted by you.

So I recently held a Giveaway on the blog for the “Mr Excel 40 Greatest Tips” book,

and, to enter the contest, you just had to submit a tip, your favorite excel tip.

So, here are the 6 winners from the post,

and I’m gonna share the tips that you submitted here in a video tutorial.

So everyone can learn from it.

So the first one here is submitted by Lynne, it’s Ctrl Period, we are gonna go with that.

Rajendra, sorry if I pronounce your name wrong,

submitted a tip on how to use Pivot Tables for working with numbers.

I’m gonna show one of my favorite tips, which is Report Filter Pages.

Tom Crouch submitted a tip about Excel Tables and Ctrl+T, another great one.

Diane Smith Double Clicking the Format Painter, another awesome tip.

Matthew likes to use F2 to Edit the Text or Formula in a cell.

and Debre Holcomb uses Ctrl Semicolonto enter today’s date.

So let’s go over all of these tips.

So the first one Ctrl Period to move around a selected range.

This is a great one that I use all the time.

So I’m just going to go to a sheet of data here,

I’ll just select a small range, so you can see how this works.

Basically hold down the Ctrl key on the keyboard,

and then press the Period button,

that will move the selection around the corners of the range right here.

You can see I’m just pressing the Period,

and the selected cell is moving around the corners of the range.

Now where you can actually use this, or use this to your advantage?

I use this all the time when I’m copying and pasting data.

So here in this sheet I have some new data.

I’m just gonna hit Ctrl Shift End to select all this,

and then I’m gonna hit Ctrl C to copy it.

And now basically I’m wanna paste it over this data right here on this sheet.

So I hit Ctrl V to paste right now.

You can see that it’s pasted.

But I’m not exactly sure if it’s pasted all over all of the existing data,

I want to paste over all of the existing data.

So if I hit Ctrl, if I hold down the Ctrl key and hit Period right now,

that will jump me over to the right corner, the top right corner of the selection,

over here to the shipping fee column,

and so I can see that that has been covered.

Now if I hit Ctrl Period again,

that’ll jump me down to the bottom.

So without having to scroll,

this has jumped me down to the bottom of the paste range,

the range that I just pasted.

And I can see that there’s still data below this,

so I still have some existing data below this there,

that I would want to go delete.

So I want to delete those rows, and again if I hit Ctrl Period one more time,

that’ll jump me to the bottom left corner,

and I can again still see that there’s data down below that I did need to delete,

so I didn’t copy over everthing.

So that’s a great one, Ctrl Period is a great tip to use.

Now, another one of my favourites, working with Pivot Tables,

one of my favorite Pivot Table tips,

And Szilvia Juhasz, who co-authored the book with Bill Jelen loves this one as well.

I know this is one of her favorite,

because she actually showed it to Bill Jelen.

So, this is a great one,

which is showing the report filter pages.

So, if you have a Pivot Table like this.

I have the salesperson up here in the filters area,

and basically I could go and filter this report down here by salesperson.

So I could just chose a salesperson’s name,

click ok, and now we filter this entire Pivot Table, for that specific salesperson.

But what if you wanted to create a report for each salesperson in this list?

Well you can actually do that with a feature of the Pivot Table.

So if you select any cell in the Pivot Table,

and then go up here to the Analyze tab or the Options tab in the ribbon,

and then in this Options drop-down here.

There is an option that says show report filter pages,

just click on that,

and then this box will appear,

and it’s gonna ask you which field within the report filter here you want to run this on.

And there’s only one field, the salesperson.

So we gonna press OK.

And when I press OK,

it’s actually gonna create a sheet for each salesperson in that list, in out data set.

So I’m gonna hit OK,

and you can see now very quickly down here at the bottom.

I now have a sheet with the pivot table on it,

that’s filtered for each sales rep in that list.

And it also names the sheet name with that item,

that pivot table item, in this case it’s sales person’s name.

So this is a great feature here.

You can see as I scroll through my list of sheets,

let’s move this over,

I have all of my sales rep, sheets for all of my sales reps here.

So within just a few seconds,

you can create this entire report, this book with all these reports in it.

So the great tip, show report filter pages,

the one I use all the time.

Now the next tip was submitted by Tom,

about using Excel tables and using Ctrl T to create a table.

So here, I have this set of data.

This is currently not formatted as an Excel table.

But if you select any cell within the range here,

the range that’s gonna be the table,

and you hold down the Ctrl key and press T on the keyboard,

that would actually create the table,

or bring up the create Table prompt.

Now if I exit out of this,

this is also located on the insert tab here of the ribbon.

With the table button right here, you can see that I hover over that.

It shows the keyboard shortcut there, Ctrl T.

So that’ll allow us to very quickly ro create a table.

I’m just, uh, press OK,

as long as you have any cell selected inside the range there,
Excel会自动帮你选择整个区域
Excel will typically automatically find that for you the entire range.

And just press OK,

and that’ll convert that to a table.

And one another little quick tip,

if you don’t like the formatting here,

you can see my header formatting,

it’s kind of messed up from the previous formatting.

You can right click here on the Actual Cell Style, or the Table Style.

And click Apply and Clear Formatting.

And now we clear all that previous formatting,

and just apply that new table style.

So that’s another little quick tip when you are creating tables there.

Now Diane had a great suggestion about the double clicking the Format Painter.

So if you’ve nerver used the Format Painter,

I just have a real simple report here,

with some cell that has some special formatting in it.

Right here in the home tab of the ribbon,

there’s this little paintbrush icon.

That’s called the Format Painter.

If you click it once,

it will basically allow you to copy all the formatting of the cell you have selected.

So I had that cell selected. And I’m gonna escape so you can see that again.

I have cell B5 selected.

Just gonna click the Format Painter,

and now, any cell that I select, so if I select this cell here,

it will apply that formatting, all that formatting to this cell.

Now with Diane’s tip,

basically if you double click on the Format Painter here,

by double click it,

now, when I hover over and select the cell,

the Format Painter stays enabled.

So I can now select another cell,

and apply that formatting as well.

So now I can just continue to apply that formatting anywhere I want.

Once I’m done, you can either hit the escape key,

or you can go up here and click the Format Painter button again.

And that’ll exit out the Format Painter mode.

And then Matthew had a another great tip,

on using the F2 key on the keyboard.

This is another one I use all the time.

If you have any cell selected,

you can have a text, or you can have a formula.

This cell here has text.

Hit F2 on the keyboard,

that’ll jump you into this edit mode for this cell.

And you can actually start typing here,

So this cell here contains a VLOOKUP formula,

If I hit F2 on the keyboard,

that’ll jump me into the edit mode to be able to edit this formula.

And then I can make changes to it,

and go in here and edit it however I like.

So, that’s a great one.

And then when you done making the changes, you hit Enter,

if you don’t wanna make any changes, you can hit the Escape key.

And that’ll get you out of edit mode.

And then you can go back and select cells.

So, another great tip from Matthew.

and then finally, Debra had a tip about entering today’s date in a cell.

And you can use the Ctrl Semicolon keyboard shortcut to do that.

So any cell here blank cell just hold the down Ctrl key,

and press the Semicolon key.

And that will enter today’s date, right there.

and then if you just hit Enter,

that’ll basically enter it in the cell.
Ctrl+Shift+; 会插入当前时间
Ctrl Shift Semicolon will enter the current time.

So that’s how you can enter the current time right there in a cell as well.

So those are two great keyboard shortcuts that can save you a lot of time,

when entering dates and times.

So I hope you enjoy that.

that’s the six tips from the winners from Mr.Excel book give away.

You can check out this blog post that contains over 350 additional tips,

submitted by you, the fans, and readers of excelcampus.com.

Again I really appreciate you participating in this.

I’ll be sharing more tips from this blog post in the future.

So we can all learn from these tips,

and save time with our everyday tasks in Excel.

Don’t forget to subscrib to my free email news letter for more tips and tricks like this.

Thanks again for watching,

and I’ll see you soon.

null;

mo