99.3% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
How To Insert, Delete, Format, Hide, Copy And Protect Worksheets In Excel
Sun 19th September 2010
One example of when it may be a good idea to colour a worksheet is if you need to group them or draw attention to a particular one. When I worked on a data team where lots of data was requested, I would always add a sheet which contained a dump of the SQL code used to extract the data from the database. This meant that when somebody wanted the data again with different parameters or I needed to be sure of how I got the data in the first place, I could easily access the code. I always coloured this tab red as a warning, renamed it 'code' and locked the cells so they could not be changed by the user. It is also possible to hide a sheet so the users are unaware of its presence. This is useful if you do not want them to tamper with it.
In Version 2010, there is an icon tab to the right of the worksheets. Click this to automatically insert a new sheet. In pre 2010 versions, right click any tab and select Insert and then select Worksheet. There is no definite limit to the number of sheets you can have on an Excel workbook, but it is unlikely the average user will ever run out! To rename a worksheet, double click the tab name and type a meaningful name. Consider the name of sheets carefully. If you choose long names then the tabs will appear very long to accommodate, meaning less are visible in one go and you will have to scroll to see them all. The maximum number of characters in a tab name is thirty one, but you have to use at least one character. There are some protected characters which cannot be used. A message will appear if the chosen name is not accepted.
Just as it is possible to add tabs when required, unnecessary tabs can and should be deleted to make the final workbook look and feel professional. To delete a tab, right click the chosen tab and choose delete. If there is data present on the tab then a warning message will appear. Beware that once deleted a tab cannot be recalled and data will be lost.
To protect a tab so it cannot be altered it is possible to add a password. In Version 2010, right click the tab and choose Protect. There are various options and a box for the password. In previous versions, go to the tab requiring protection. Go to Tools, Protect and then enter a password in the box. A user is required to enter the password twice and they must match exactly in order to prevent a spelling mistake stopping access to the sheet. The tab is now protected and unless the protection is removed with the correct password, no changes can be made.
If further protection is required then hide the tab by right clicking and choosing Hide. To show the tab again in version 2010 then go to the Home section on the ribbon and click on the format icon. Under the visibility section is Hide and Unhide. Select this and unhide the required sheet. In previous versions go to Format, Sheet, Unhide.
Sometimes it helps to colour the tabs. This creates definition between them and can allow a user to instantly find the tab they are looking for by colour rather than reading the names. Tabs can also be grouped together in colour groups if they contain related information. If for example there are a number of sheets relating to the sales of one office then they could be the same colour for easy identification. In version 2002 onwards, right click a tab and choose Tab Colour to select a colour for the tab. Related tabs can be moved together by clicking on them and dragging them to a new position.
There have been several occasions when I have had a sheet full of data that I need to experiment with, but am reluctant to do it in case it does not work. It is of course possible to save a copy of the file, but it is also possible to create a quick copy of the entire tab including the data. This is also useful if you are repeating data or format every month with formulas etc. I have a monthly account sheet already set up that I copy and alter each month. Right click a tab and choose Move or Copy. There are various options and a check box to select a copy. This will create an exact duplicate of the sheet next to it. It is a good idea to rename the sheet with the same name and then add '_copy' if you are simply playing and experimenting in order to avoid confusion. The automatic name will be the original sheet name with a number two in brackets.
Using the described techniques can help to organise your workbook and give it a professional finish. It will help a user navigate through the information more easily and demonstrate your advanced knowledge of Excel.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
PSI CRO AG
I really liked the trainer and her aproach and expertise. The contents are accurate and interesting.
Royal College of Physicians
Best training experience I've had. Thoroughly enjoyed it, thank you.
Crown Prosecution Service
Senior Personal Assistant
The trainer was very knowledgable. I have done several courses before and Caroline was the best trainer I have had