Working with MS Excel means working in the worksheets in Excel.
A worksheet is an area that has all the cells where you can store data, enter formulas, insert charts and create reports and dashboards.
When you open a new Excel workbook file, by default there is only one worksheet. Earlier versions of Excel (2013 or 2016 used to have 3 worksheets by default)
There are some simple shortcuts and techniques that you can use to quickly insert new worksheets in the same workbook in Excel.
In this tutorial, I will show you a couple of methods that you can use to insert a new worksheet in the same workbook (one at a time).
I will also show you a method to quickly insert worksheets in bulk, in case you want to add 5, 10, or 20 worksheets in one go (using a simple VBA code).
So let’s get started!
This Tutorial Covers:
Keyboard Shortcut to Insert a New Worksheet
If you are a fan of keyboard shortcuts, this is probably the fastest way to insert a new worksheet in an already open workbook in excel.
Below is the keyboard shortcut to insert a new worksheet
SHIFT + F11
For this shortcut, hold the SHIFT key and then press the F11 key.
Another keyboard shortcut that does the same job is ALT + SHIFT + F1 (hold the ALT and the SHIFT keys and press the F1 key)
Personally, I find using the keyboard shortcut to be the best way to insert a new worksheet in Excel. Even if I have to insert multiple worksheets (say 3 or 5 or 10), I can still do that very quickly
Insert New Sheet Using the Plus Icon
If you’re not a big fan of keyboard shortcuts and prefer using the mouse instead, this method is for you.
At the bottom of your worksheet (in the area that has all the sheet names), you will see the plus icon.
Clicking on this plus icon will immediately insert a new worksheet.
Insert New Sheet Using the Insert Dialog Box
Another way to insert a new sheet in Excel is by using the Insert dialog box.
Below are the steps to do this:
- Right-click on any of the sheets
- Click the Insert option
- In the Insert dialog box, make sure Worksheet is already selected (which is also the default option).
- Click on Ok
While this is not the fastest way to add a new sheet in an Excel workbook, it’s good to know as it gives you access to some other things as well.
Apart from inserting a regular worksheet, you can also use this to insert a ‘Chart Sheet’ or a ‘Macro Sheet’ using the insert dialog box.
If you’re wondering, a Chart Sheet is just like a worksheet but is meant only to hold a chart. A macro sheet is something that was used earlier before the VBA days and is no longer used.
In most cases, you won’t be needing these, but it’s good to know.
There is also the Spreadsheet Solutions tab that holds some of the templates. You can also create and get your own templates here. So the next time you need to quickly insert a template, you can do it from here.
Adding New Worksheets Using the Insert Tab in the Ribbon
And finally, you also have an option in the Excel ribbon to add a new worksheet.
To do this:
- Click the Home tab
- In the Cells group, click on the Insert option
- Click on the Insert Sheet option.
This will insert one new worksheet in the open workbook
How to Insert Multiple Worksheets in One Go (One Line VBA Code)
So far, the methods that I have shown you insert one new worksheet at a time.
In case you want to insert multiple worksheets, you would have to use these methods again and again (i.e., use the keyboard shortcuts multiple times or use the plus icon multiple times).
While this works fine in most cases, if you have to insert worksheets in bulk, saying so 10 or 20 or 30 worksheets in one go, then this could be time-consuming and error-prone.
So let me show you a better way to insert multiple new worksheets in one go.
This can be done easily using a simple one-line VBA code:
The above code would instantly add 10 new sheets in the workbook in which it’s run (if you want any other number of sheets to be added, just change the value in the code)
Below are the steps to run this macro code:
- Click the Developer tab (or use the keyboard shortcut ALT + F11) and then click on Visual Basic
- If you don’t see the immediate window in the VB Editor, click on the View option and then click on Immediate window
- Copy and paste the above code in the immediate window
- Place the cursor at the end of the VBA code line and hit Enter
As soon as you hit Enter, it would instantly insert 10 new sheets in the workbook. In case you want to insert more (say 20 or 30), just change the number in the code
The good thing about using this code is that you can be sure that it would insert the right number of sheets (no chance of human error if the code is executed without any errors).
In case you need to do this quite often, you can use the below code and paste it into our Personal Macro workbook.
Once in Personal Macro Workbook, you can add it to the Quick Access Toolbar so you always have access to it in the workbook.
This way, you can easily add 10 or 20 sheets with just a single click.
Changing the Default Number of Sheets with New Excel Workbooks
If you always have a need for more worksheets in the workbook, you can change the default number of sheets you get when you open a new Excel file.
For example, you can change the setting so that you always get 5 or 10 sheets by default with every newly opened workbook.
Below are the steps to change this default setting:
- Open any Excel workbook
- Click the File tab
- Click on Options. This will open the Excel Options dialog box
- In the Excel Options dialog box, make sure the General option is selected in the left-pane
- In the ‘When creating new workooks’ section, enter the number of sheets you want (in the Include this many sheets value).
- Click OK
Now when you open a new Excel workbook, it will have the specified number of worksheets.
Note that an Excel file can have a maximum of 255 sheets.
These are all the ways you can use to insert a new sheet in Excel. In most cases, you only need to add one or a couple of new sheets, so you can use the keyboard shortcut or the plus icon in the worksheet.
And in case you have a need to insert many new sheets in bulk, you can use the VBA code. Alternatively, you can also change the default number of sheets in any new Excel workbook.
I hope you found this tutorial useful!
Other Excel tutorials you may also like: