Discussion on:

Five of my favorite Excel worksheet tips

For me the big one to get users onto is recognizing the multiple levels that exist within every cell

The formatting display layer
The formula display layer
The value display layer

Be aware of what layer you want to work with and anways use Paste Special so that you are only pasting the applicable layer you want instead of the vulgar crtl+v that blindly over-writes all layers with the copied content.

To enter a sum at the bottom of a column of figures use Alt=
It is much quicker than searching for the Sigma sign or typing in the formula.

Does Excel have a setting for new worksheet locations? It is a bit of a pain to have to drag newly inserted worksheet tabs to the end of the list.

I'm using 2010, but when I press the "Insert Worksheet" button that is next to Worksheet 3, it inserts the sheet at the end, regardless of which worksheet is currently active. I don't remember this being any different in previous versions of Excel.

Say you want the new worksheet to go between Sheet 1 and Sheet 2. Right-click Sheet 2, Click Insert and new worksheet will go before Sheet 2.

@Shadeburst

I know that you can control the insert behavior that way. I wanted to adjust some setting that would cause newly 'inserted' worksheets to be 'appended' to the worksheets collection (at the end)

As mentioned by thomascwhitfield, the Insert Worksheet tab will do just that. Is there a reason you can't use it?

@Susan,

I'm using Excel 2003.

Even if you click on the last tab, "insert" is all that is available, and it inserts between the last two tabs. If you want to insert between two tabs it works fine - just click on the tab on the right of the insert position you want. Inserting from the file menu always places the tab in the first position.

Move or copy is the best method here, so if you keep a blank sheet, you can always Move or Copy (you'd want to tick the copy checkbox) it and determine its insertion point by choosing one from the list, including (move to end).

Regardless as to method, there is indeed an un-aesthetic amount of clicking or dragging.

This is trivially easy to do in VBA.

Public Sub AppendSheet()
Worksheets.Add after:=Worksheets(Worksheets.Count)
End Sub

Is there any way to access the sheet tabs without right-clicking on the tab, or indeed using the mouse at all? I would love to be able to rename a tab and move it to a consolidated workbook all without having to go backwards and forwards between keyboard and mouse.

Use the context menu key. Its between the right-ctrl and right-alt keys; Basically it is a key to emulate right-clicking.

Nevermind. That doesn't work. I thought if you used the shortcut keys to switch between tabs then used the context menu key that would work but it still opens the menu where ever the active cell is. Guess your just stuck with switching between keyboard and mouse, and I can relate to how annoying that is, and not just in Excel.

Thanks ThomasC, gave me an idea. There is another way, namely using Excel Basic, but that means you have to import the code module into the workbook first and that can be just as much of a pain!

Alt-O, H, R lets you rename a tab.
Alt-E,M for Move or Copy
Alt-E, L Delete current sheet.
These are legacy shortcuts from pre-ribbon versions.

Simply press Shift+F11 to add a new blank worksheet,

Simply press F12 to 'save as'

To copy a sheet; hold down ctrl and drag the sheet you want to copy. When you see a little arrow, drop it there. This is for copying sheet within the same workbook.

For workbooks that contain identical pages, such as a form for each day of the week, click on the page tab, hold the control key down and click on multiple page tabs. Any edits made to the first page will be replicated on the others. All sheets must be unprotected for this to work.

Ctrl+s (regularly and frequently)
Ctrl+1 (format as necessary)
Alt+=
Crtl+tab (to tab between w'bks)
F2,F9 - to change a formula to its result.
and of course Ctrl+z.

To quickly move to another tab in a workbook:
Ctrl + PageUp or PageDn

Steps to clone Page Setup of a worksheet
1. Via Print Preview, set up Headers, Borders, Margins, etc., to create a master worksheet
2. Click Close to exit Setup
3. Via the File menu, click Page Setup... and then OK
4. Select one or more other worksheet Tabs
5. Type: Ctrl+Y
All other worksheets will immediately adopt the Page Setup parameters of the master worksheet.
It is also possible to transfer a customized Page Setup to other workbook tabs using Ctrl+Y exactly as described. This procedure will replace any existing Page Setup.
p.s. I've never seen this documented. I discovered it in 1998 and it has worked on every version of Excel since.

Well done! Thank you very much for professional templates and community edition
sesli chat sesli sohbet