Excel是一个非常强大的程序。如果您是经常使用电子表格的人,那么学习如何操作Excel会产生巨大的影响。但是,即使您已经了解基本知识,也总有一些技巧可以改善您在应用程序中工作的整体体验。
学习的方便技巧之一是可用于在Excel(Excel)中的工作表之间快速切换的不同方法。我们将从简单的选项开始,例如手动操作,然后逐渐转向其他更高级用户的替代方案。
我们还为我们的YouTube 频道制作了一个(YouTube channel)简短的视频教程(short video tutorial),您可以查看。
在 Excel 中的工作表之间切换的键盘快捷键(Keyboard Shortcuts To Switch Between Sheets In Excel)
因此,您面前有一个带有多个工作表的Excel 文档。(Excel document)当您必须在它们之间切换时,显而易见的选择是通过手动选择工作簿底部的不同工作表来完成。但是,该选项可能不可用。
如果您不能使用鼠标或触摸板(mouse or touchpad),或者您的工作量很大并且不想在上面浪费时间(t waste time),您可以使用键盘快捷键在(keyboard shortcut)Excel中的工作表之间切换。
您需要在这里学习两个快捷键:Ctrl + PgDn(向下翻页)用于移动到右侧的工作表,以及Ctrl + PgUp(向上翻页)用于移动到左侧的工作表。或者只需按照以下步骤在工作簿中的工作表之间切换:
- 为了向右移动一张纸,请按住Ctrl,然后按PgDn键。
- 要继续向右移动,请再次按PgDn键。
- 为了向后移动或向左移动一张纸,请按住Ctrl,然后按PgUp键。
- 要继续向左移动,请再次按PgUp键。
这只是一个有用的Excel键盘快捷键(keyboard shortcut)示例。学习一些更重要的捷径可以帮助提高您的工作效率。例如,它会在您浏览 Excel或执行诸如更改程序中的行高和列宽等日常任务(changing row height and column width)时节省您的时间(save you time while navigating Excel)。
使用转到命令 (Use The Go To Command )
使用 Go To 命令是另一种在Excel 工作表(Excel worksheet)中移动的便捷方式。如果您必须每天处理包含大量行和列的 重载Excel文档,您将特别喜欢这种方法。(Excel)
要使用转到命令移动到工作簿的不同空间,请执行以下步骤:
- 打开您的 Excel 工作表。
- 从屏幕顶部的菜单中选择编辑。(Edit)
- 从下拉菜单中选择转到...选项。(Go To…)您将看到打开的转到窗口。您可以使用它从文档中的一个位置跳转到另一个位置。
- 在对话框窗口中,找到参考(Reference) 线并输入(line and type)您的单元格参考(cell reference),例如“C22”或“H134”。然后按OK。
通过将活动单元格框(cell box)移动到该特定单元格上,这会将您带到您选择的位置。
使用键盘快捷键启用转到命令(Enable Go To Command Using Keyboard Shortcuts)
在Excel(Excel)中使用“转到”命令的更快方法(quicker way)是学习正确的键盘快捷键。这里有两个不同的选项,您可以按F5键或使用Ctrl + G键盘快捷键(keyboard shortcut)打开“转到”窗口。
开始使用“转到”功能后,您会看到它还将您以前的所有搜索存储在那个小窗口中。因此,您可以在参考(Reference)线上方的顶部框中找到所有先前单元格引用的列表。如果您发现自己在Excel 文档(Excel document)的特定区域之间来回切换,这是一个有用的功能。
但是,请记住,一旦您关闭Excel 工作簿(Excel workbook),此信息就会消失。因此,如果您认为将来需要它以供参考,请确保在退出文档之前将其保存在其他位置。
使用 Go To 在 Excel 中的工作表之间切换(Switch Between Sheets in Excel Using Go To)
如果您不想跳转到不同工作表中的特定单元格(specific cell),而只想打开整个内容,也可以使用“转到”命令来执行此操作。
为了在Excel 工作簿(Excel workbook)中的工作表之间切换,请按照下列步骤操作:
- 打开您的 Excel 工作簿。
- 使用快捷方式之一或手动(Bring)调出“转到”窗口。
- 在参考(Reference)行中,键入所需的工作表名称(sheet name)以及单元格参考(cell reference)。例如,如果您在工作表 2(Sheet 2)上并想要移动到工作表 3 ,则需要在(Sheet 3)参考行(Reference line)中键入“工作表 3 + 感叹号 +单元格引用(cell reference)” ,例如 Sheet3!B5。此处的感叹号(exclamation mark)用作工作表名称(sheet name)和单元格编号(cell number)之间的分隔符,因为您不能在单元格引用中使用(t use)空格。
- 该命令会将您带到不同的工作表并将活动单元格框(cell box)移动到您选择的单元格。要返回上一张工作表,请重复整个过程,但更改您在“转到”窗口中键入 的工作表名称。(sheet name)
使用超链接更改工作表(Use Hyperlink To Change Worksheets)
您可以在Excel 工作簿(Excel workbook and jump)中导航,也可以使用超链接从一张工作表跳到另一张工作表。在Excel 中(Excel),有不同的超链接类型,您可以链接到计算机上的现有文件、网页或电子邮件地址。
如果要创建超链接并使用它移动到不同的工作表,请执行以下操作:
- 右键单击它并从下拉菜单中选择超链接。(Hyperlink)
- 从框中间的选项中,选择Document。
- 移动到下面的Anchor 行(Anchor line),找到它旁边 的Locate...按钮并单击它。(Locate…)
- 在弹出窗口(pop-up window)中,选择您要链接到的工作表,然后输入您希望超链接将您带到 的单元格引用。(cell reference)
- 按 OK 后,您将被带到上一个窗口,您可以在其中更改您将在文档中看到的超链接的文本。在Display下输入它。
您可以稍后删除或编辑此超链接以更改其目的地或外观(destination or appearance)。
使用 VBA 脚本在工作表之间移动(Use a VBA Script to Move Between Sheets)
虽然上述所有选项都非常适合在Excel(Excel)工作表之间切换,但您可能会发现自己处于需要更多工作表的情况。例如,如果您正在处理包含许多工作表的Excel 文档(Excel document),并且经常需要在它们之间来回移动。在这种情况下,您可以创建一个 VBA 宏或脚本来(create a VBA macro or script to automate that process)为您自动执行该过程。
Excel允许您使用Visual Basic for Applications ( VBA ) 脚本自动执行许多不同的功能和命令。它是Excel用来创建宏 的编程语言。(programming language)
如果您对该主题完全陌生,请查看我们的 VBA 初学者指南(beginner’s guide to VBA)和MS Excel 高级 VBA 指南,(advanced VBA guide for MS Excel)了解有关Microsoft Office中(Microsoft Office)DIY 编程(DIY programming)的所有信息。
如果您已经知道一两件事,那么您可以使用如下宏来移动您的Excel 工作簿(Excel workbook)。
VBA 宏向右移动(VBA Macro To Move To The Right)
Sub Change_Sheets_Right()
Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index
If CurrentSheet < SheetNum Then
Sheets(CurrentSheet + 1).Activate
Else
Sheets(1).Select
End If
End Sub
VBA 宏向左移动(VBA Macro To Move To The Left)
Sub Change_Sheets_Left()
Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index
If CurrentSheet > 1 Then
Sheets(CurrentSheet - 1).Activate
Else
Sheets(SheetNum).Activate
End If
End Sub
在您的工作簿中安装(Install)这两个代码,以便能够在两个方向上移动,一次左右移动一张。
成为 Pro Excel 用户(Become a Pro Excel User)
现在,您离掌握Excel(Excel)又近了一步,同时还能为自己节省大量工作时间和精力(time and effort)。当然,总会有新的提示和技巧可供您了解有关此软件的信息。就像与同事共享 Excel 文件(ways of sharing an Excel file with your colleagues)或学习使用密码保护Excel文件的多种方式一样。
然而,自动化您的日常工作流程是您进入Excel世界的第一步,也可能是最重要的一步。
您知道和使用哪些Excel 自动化(Excel automation)技巧?在下面的评论部分与我们 分享!(Share)
How to Switch Between Worksheets in Excel
Excel is a very powerful program. If you’re someоne who uses spreadsheets often, learning how to operate Excel can make a huge difference. But even if you already know the basics, there are always a few more tricks that can improve your overall experience working in the app.
One of the handy tricks to learn is the different methods you can use to quickly switch between sheets in Excel. We’ll begin with the simple options like doing it manually and then gradually move on to other alternatives for more advanced users.
We also created a short video tutorial for our YouTube channel, which you can check out.
Keyboard Shortcuts To Switch Between Sheets In Excel
So you’ve got an Excel document in front of you with multiple worksheets. When you’ve got to switch between them, the obvious choice would be to do it by manually selecting a different sheet at the bottom of the workbook. However, that option might not be available.
In case you can’t use your mouse or touchpad or have a big workload and don’t waste time on that, you can switch between sheets in Excel using a keyboard shortcut.
There are two shortcuts that you’ll need to learn here: Ctrl + PgDn (page down) for moving to the sheet on the right, and Ctrl + PgUp (page up) for moving to the sheet on the left. Or simply follow these steps to switch between sheets in the workbook:
- In order to move one sheet to the right, hold down Ctrl, then press the PgDn key.
- To keep moving to the right, press the PgDn key again.
- In order to move back or one sheet to the left, hold down Ctrl, then press the PgUp key.
- To keep moving to the left, press the PgUp key again.
That’s just one example of a useful keyboard shortcut for Excel. Learning a few more essential shortcuts can help improve your productivity. For example, it will save you time while navigating Excel, or doing such routine tasks like changing row height and column width in the program.
Use The Go To Command
Using the Go To command is another handy way of moving around your Excel worksheet. You will especially appreciate this method if you have to deal with overloaded Excel documents with lots of rows and columns on a daily basis.
In order to move to a different space of your workbook using the Go To command, follow these steps:
- Open your Excel worksheet.
- Choose Edit from the menu on top of the screen.
- Select Go To… option from the drop down menu. You will see the Go To window open. You can use that to jump from one place in your document to another.
- In the dialog window, find the Reference line and type in your cell reference, like “C22” or “H134”. Then press OK.
This will take you to the place you’ve chosen by moving the active cell box onto that specific cell.
Enable Go To Command Using Keyboard Shortcuts
A quicker way to use the Go To command in Excel is by learning the right keyboard shortcuts for it. There are two different options here, you can either press the F5 key or use the Ctrl + G keyboard shortcut to bring the Go To window up.
Once you start using the Go To feature, you will see that it also stores all of your previous searches in that little window. So you can find a list of all your previous cell references in the top box above the Reference line. A useful feature if you find yourself jumping back and forth between specific areas of your Excel document.
However, remember that this information will disappear once you close your Excel workbook. So if you think you’ll need it for future reference, make sure you save it elsewhere before exiting the document.
Switch Between Sheets in Excel Using Go To
If you don’t want to jump to a specific cell in a different worksheet, but just want to open the whole thing, you can also use the Go To command to do it.
In order to switch between sheets in your Excel workbook, follow these steps:
- Open your Excel workbook.
- Bring up the Go To window using one of the shortcuts or manually.
- In the Reference line, type in the desired sheet name along with the cell reference. For example, if you’re on Sheet 2 and want to move to Sheet 3, you’ll need to type in “Sheet3 + exclamation mark + the cell reference” in the Reference line, like Sheet3!B5.
The exclamation mark here serves as a separator between the sheet name and the cell number, as you can’t use spaces in cell references.
- That command will take you to a different sheet and move the active cell box to the cell you’ve chosen. In order to move back to the previous sheet, repeat the whole process but change the sheet name that you type in the Go To window.
Use Hyperlink To Change Worksheets
You can navigate around your Excel workbook and jump from one sheet to another using hyperlinks as well. In Excel, there are different hyperlink types, and you can link to an existing file on your computer, a web page, or an e-mail address.
If you want to create a hyperlink and use it to move to a different worksheet, do the following:
- Select a place in your sheet (a cell) where you want to insert the hyperlink.
- Right-click on it and choose Hyperlink from the drop-down menu.
- From the options in the middle of the box, choose Document.
- Move on to the Anchor line below, find the Locate… button next to it and click it.
- In the pop-up window, choose the sheet you want to link to and type in the cell reference where you’d like the hyperlink to take you.
- After you press OK, you’ll be taken to the previous window where you can change the text of the hyperlink that you’ll see in the document. Type it in under Display.
You can later delete or edit this hyperlink to change its destination or appearance.
Use a VBA Script to Move Between Sheets
While all of the above options are great for switching between Excel sheets, you might find yourself in a situation when you need more. For example, if you’re working with an Excel document of many worksheets and constantly need to move back and forth between them. In that case you can create a VBA macro or script to automate that process for you.
Excel allows you to automate many different features and commands using Visual Basic for Applications (VBA) scripting. It’s the programming language that Excel uses to create macros.
If you’re completely new to the topic, check out our beginner’s guide to VBA and an advanced VBA guide for MS Excel to learn everything about DIY programming in Microsoft Office.
If you already know a thing or two about it, then you can use macros like the following to move around your Excel workbook.
VBA Macro To Move To The Right
Sub Change_Sheets_Right()
Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index
If CurrentSheet < SheetNum Then
Sheets(CurrentSheet + 1).Activate
Else
Sheets(1).Select
End If
End Sub
VBA Macro To Move To The Left
Sub Change_Sheets_Left()
Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index
If CurrentSheet > 1 Then
Sheets(CurrentSheet - 1).Activate
Else
Sheets(SheetNum).Activate
End If
End Sub
Install both codes in your workbook to be able to move in both directions, right and left one sheet at a time.
Become a Pro Excel User
You’re now one step closer to mastering Excel and saving yourself a bunch of time and effort at work at the same time. Of course, there will always be new tips and tricks that you can learn about this software. Like the many ways of sharing an Excel file with your colleagues, or learning to password protect your Excel files.
However, automating your everyday workflow is the first and probably the most important step you can take diving into the world of Excel.
What Excel automation tips do you know and use? Share them with us in the comments section below!