Monday, August 23, 2010

Manage Large Number of Spread Sheets

Did you ever notice how many characters excel allows to name a sheet? In office 2007 the maximum character allowed for naming a sheet is 31. This means in some cases you can’t name your sheets meaningfully.
At some extent this restriction is a blessing. For example when a Workbook with 40 sheets it is better to give the sheet name with numbers or mere alphabets like 1,2,3…40 or a,b,c….an. Thus you can show maximum number of sheets visible without using the scroll bar.

But there is a big question in front of you.
How do you know which sheet need to be opened for a specific data? If you are the author of the workbook you might have some idea. Otherwise you may get annoyed seeing these sheet names.

If you want to find some specific text you can do it by using the find command (Press Ctrl+F, Expand by clicking on Options)
See the screen shot below to use the find command to find specific text from entire sheets.
This command will instantly search for the content within the entire workbook and find the text you have typed (if it is there). If the text you have typed repeats in several sheets you have to check all the sheets by hitting enter.

Here the case is different. You have large volume of data stored in different sheets. For example in one sheet you have a table with details of sales made in a particular month of a particular area, in another sheet some other area likewise. Here you don’t want to find any particular text. At this juncture the below tip is useful

Create a table of content that includes all sheet names with description and linking this sheet with other sheets may solve the dilemma.

Steps to create a table of content with link
Right click on the first worksheet name and insert a blank worksheet. Double click on the sheet name and rename it as index/table of content.
Type all the sheet names in this worksheet. Here sheet name from 1 to 40 (No needs to type all sheet names. Just type 1,2 select both cells, point and drag it)
Give detailed description against each sheet names


Sheet |Name Description
1 |Sales of TV (North Zone)
2 |Sales of TV (East Zone)
40| Purchase of Consumables (First Week)

Link it with the relevant work sheet
Let us see how to do it. It is very simple. Go to the cell where you want the link to be appeared (Sheet Name 1, Sheet Name 2...Sheet Name 40)

Press Ctrl+K to insert Hyperlink
Under Link to:
Click on Place in this document
Click on the sheet name
Click OK (Refer the above screen shot)

Repeat the steps until you have inserted link to all sheets
Get Free Updates:
We will send a confirmation mail. Please check your inbox/spam folder to confirm it
Related Posts Plugin for WordPress, Blogger...

Copyright © 2011 All Rights Reserved | Designed by Amith | Post RSS | Home | About | Disclaimer | Privacy Policy | Contact