Top 3 Products & Services
Dated: Jun. 06, 2013
Related CategoriesComputer Beginners Guides
Excel is a very useful tool and if you regularly work on computers you should know as much as possible about it. In the following text two options in Excel will be explained, grouping subtotals and hiding worksheets.
You are working on sales in stores that are located on various locations. The information about the sales are input by a chronological order in which they were acquired, and next to the main sum, you want to add between sums that refer to individual stores. The first thing you need to do is to chronologically sort the inputted data and group them by stores: choose one of the cells in the column Stores and click on Sort & Filter in the section editing on the Home ribbon.In the offered menu choose Sort A to Z (the same result can be achieved with the help of a fitting icon in Data tools on the Data ribbon). After sorting, on the Data ribbon, in the Outline section click on the Subtotal icon. A dialog will appear that will recognize what column is added, but it will often miss by what key it is added.The key of grouping is chosen in the drop down list At each change in. The first offered point in the function that is chosen is adding that we need in this case, but in the case of some other process, you can decide for counting, finding extreme values or some statistical analysis. The thing that follows is defining the processed column. If it doesn't guess by itself what is processed, you need to check the wanted column in Add subtotals to. After the confirmation, rows with subtotals will be inputted and at the end the total. On the right side of the work sheet the Outline column will appear that is used for showing or hiding detailed rows that formulate subtotals. Clicking on the pluses and minuses enable easier viewing the accumulations, especially for large tables. If you click on the number 1, the whole table except the main total will be hidden. By clicking on 2, only the subtotals and the total will be shown. Once hidden rows can be reappeared by clicking on fitting pluses that represent them, and all data is opened by clicking on 3. Nifty, right?If you want to clean the table at the end, click again on the Subtotal icon and then the key Remove All.
Hidden rows and columns are really handy when we want to remove some less important information that crowd the table, but sometimes we want to really hide some information in the files we send our colleagues. Even whole worksheets can be hidden. By click the right mouse button on the strip with the name of the sheet, right under the table and choose the option Hide from the popup menu. Neither the work sheet nor the strip will be visible, until somebody activates this popup menu where the option Unhide appears that will simply return the work sheet into a document. There is a way for so called "strong hiding". Open the same popup menu and in it you will find the option View Code. In Microsoft visual Basic dialog find Project – VBA Project window and in it choose the work sheet that you want to hide. Under the Project – VBA Project window is the Properties window in which you should change the lowest point, Visible option by choosing 2 – xlSheetVeryHidden. After this the chosen work sheet doesn't appear in the menu Unhide. To return the work sheet, repeat the whole process and in Properties of the work sheet return the Visible option to -1 – xlSheetVisible. The process of bringing it back is not difficult, but count on the fact that most users don't know about this option. It is not a guarantee of safety – for real security you should encode the whole file with a password, but sometimes this works well too.
Now that you've gotten free know-how on this topic, try to grow your skills even faster with online video training. Then finally, put these skills to the test and make a name for yourself by offering these skills to others by becoming a freelancer. There are literally 2000+ new projects that are posted every single freakin' day, no lie!