Learn How To Summarize Your Data Instantly

If you want to summarize your data fast use Subtotal command to calculate automatically each similar groups and their grand total.

Info: Ensure yourself that your data is labeled and sorted properly before using Subtotal command.

You can find the subtotal command in the Data menu. Excel opens a dialogue box once you click it and displays all the labels in the drop menu ‘At each change in’ in the top of the dialogue box.

Now you need to select one label from the list. The label you selected becomes the Index and other labels are considered as items.

Indexing is done to create a tracking order for calculating subtotal of other items. If there is a change in data in the index Microsoft Excel stops summarizing the values till that point, makes a total and continues the summarizing next similar data.

You can use any one of the FUNCTIONS (COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR & VARP) in the dialogue box to find value of items. You can find this option in the middle of the dialogue box.

In the bottom of dialog box all the labels are displayed in the scroll menu ‘Add subtotal to’ and Excel ask you to select one or more than one item that you want to summarize. Selected items are summarized by the FUNCTION based on Indexed label.

The other features in this dialogue box are:

Replace Current Subtotals: In case you summarized a particular field using Subtotals and want to change the index label or summarizing function or items, open subtotal dialogue box make necessary changes in inputs and enable ‘Replace Current Subtotals‘ option. Excel will remove existing subtotal and replaces with the new one based on your input.

Page break between Groups: If data taken to summarize is huge and it is required to take print out for each item, you need to enable the option ‘Page break between Groups’. Excel prepares page break between each group.

Summary below data: This option helps you to get summary details below each summarized item.

Tips: If you want to remove subtotals and get back original data, open subtotal dialogue box and select ‘Remove All’.

The result will have outlines that helps you to overview the data. Outlines help you to expand or collapse the nested items without affecting the data. You can turn off outlines if not required. Open options dialogue box and disable ‘outline symbols’ from windows options in the view tab.

Tips: Press Ctrl key and numerical key 8 at the same time to turn ON or OFF outlines.

Want To Manage WorkBook Easily?

If you aware about Excel file structure, the workbook handling task would become very easy. How a normal textbook has its name and number of pages within it, a Workbook also has a title and number of worksheets within it.

You can see worksheets in the sheet tab, which is just above the status bar. The default number of worksheet in a Workbook is three. Using the following path you can customize the number of worksheets to be opened every time when you create a new file.

Tools => Options => General tab => Sheets in new workbook and change the number.

Inserting worksheets:

To insert an additional worksheet, follow any one of the options below:

1. Press Shift and F11 key at a time.
2. Select ‘Insert worksheet’ from Insert Menu
3. Keep your mouse on sheet tab, which is above status bar and right click the mouse. A pop-up menu appears. Select ‘Insert’ from the pop-up menu. (Recommended option)

Tip: Using this pop-up menu option, you can even insert multiple worksheets. Hold shift key to select many worksheets and select ‘Insert’ from the pop-up menu.

Move or Copy Worksheets

Worksheets can be moved within workbook. It can also be moved to an existing workbook or a new workbook.

To move a worksheet, follow any one of the options below:

1. Select ‘Move or Copy’ from Edit menu.
2. Drag the sheet directly into the desired location.
3. Keep your mouse on sheet tab, which is above status bar and right click the mouse. A pop-up menu appears. Select ‘Move’ from the pop-up menu. (Recommended option)

A dialog box appears now. You need to choose the exact location where you want to move. Use ‘To book’ drop menu if you like to move it to an existing worksheet or a new worksheet.

Tip: To make a similar copy of worksheet please enable the option ‘Create a copy’ in the dialog box.

Info: Always save your workbook before performing this command.

Delete Worksheets

A workbook must have minimum one worksheet. Keeping any one worksheet available in the workbook you can delete one or more number of worksheets.

To delete a worksheet, follow any one of the options below:

1. Select ‘Delete sheet’ from Edit menu.
2. Keep your mouse on sheet tab, which is above status bar and right click the mouse. A pop-up menu appears. Select ‘Delete’ from the pop-up menu. (Recommended option)

Tip: Using this pop-up menu option, you can even delete multiple worksheets. Hold your shift key to select many worksheets and select ‘delete’ from the pop-up menu.

Info: Remember deleted sheets will be lost and unrecoverable so always save your workbook before performing deleting operations.

Hide and Unhide Worksheets

You can hide a worksheet if you do not like to keep it in display. You can unhide it whenever you want. Click ‘Format’ Menu, click ‘Sheet’ submenu and find Hide or Unhide options.

Info: Worksheets that are hidden by Visual Basic program cannot be Unhide by this method. We will discuss soon how to unhide a workbook that is made hidden by visual basic program.