You are here: Home / Archives for November 2008

A macro to print only hidden sheets in a workbook

November 25, 2008 By admin

If you have a workbook with a number of worksheets, some visible and other hidden, and you want to print only the hidden sheets, then you can use the macro shown below.
Because Excel will not allow the macro to be printed while it is hidden, this macro will make the sheet visible, print it and then hide it again.

Sub PrintHiddenSheets()
‘*********************************************************
‘ Print only hidden sheets in the active workbook *
‘*********************************************************
Dim wSheet As Worksheet
Dim CurStat As Variant
For Each wSheet In ActiveWorkbook.Worksheets
If Not wSheet.Visible Then
CurStat = wSheet.Visible
wSheet.Visible = xlSheetVisible
wSheet.PrintOut
wSheet.Visible = CurStat
End If
Next
End Sub

This code is also available in the Downloads page.

Copy the code to a new module in Visual Basic Editor, then run it like any other VBA macro.

Check this post if you want to know How to write a VBA macro.

Filed Under: Printing, Visual Basic Tagged With: Hidden, Printing, Workbook, Worksheets

How to show the top 10 items in a range

November 22, 2008 By admin

The autofilter feature in Excel allows you to show a subset of your data based on a specified criteria. The top 10 is such a criteria.

To show only the top 10 values (or any other number) for a particular column follow these steps :

1. Click Data –> Filter –> Autofilter.

2. In the column which have the data to be evaluated, click the arrow of the autofilter.

3. From the drop-down list select Top 10.

4. The Top 10 window will open.

The Top 10 Window has three combo boxes which allow a lot of flexibility in specifying the required subset of data. To avoid confusion I will take some possible scenarios as follows:

1. You want to show the top 10 items (default). Leave everything as it is and click OK. The top 10 items will be displayed.

2. You want to show the top 15 items. In the middle combo box click the bottom arrow until you reach 15, or just type 15 then click OK. The top 15 items will be displayed.

3. You want to show the bottom 10 items. In the left combo box select “Bottom” then click OK. The bottom 10 items will be displayed.

4. You want to show the top items, up to 25% of your data (i.e. the top quarter of your data). In the left combo box select “Top“, in the middle combo box type 25 and in the right combo box select “percent“, then click OK. The top quarter of your data will be displayed.

Note:

No sorting is performed here. The top/bottom items will be shown as they appear before the filtering.

Applies to: Excel 2003

Filed Under: Data Tagged With: Autofilter, Filter, Top 10

How to sum values based on multiple criteria

November 18, 2008 By admin

The SUMIF function is a direct way to sum values based on a single criteria. If we want to sum or add  values based on multiple criteria  however, then we need to take extra steps. One alternative for this is to use the SUM function and the IF function.

In the example below we want to sum up the total sales for “John” in quarter 2 (Q2). i.e. our criteria is “John” in column B and “Q2″ in column C. We will use the following formula for this purpose:

=SUM(IF($B$2:$B$17=”John”,IF($C$2:$C$17=”Q2″,$D$2:$D$17,0),0))

However this formula should be an “Array formula”. To make it an “Array formula” you should press CTRL+SHIFT+ENTER while you are in the edit mode of the formula. It should then look like this :

{=SUM(IF($B$2:$B$17=”John”,IF($C$2:$C$17=”Q2″,$D$2:$D$17,0),0))}

This formula will give us a result of 9547 (summing sales in the yellow rows).

Note:

The formula presented in this post is generated using the conditional sum wizard. You can try the wizard for yourself.

If you have the Analysis Toolpak installed, then the wizard should be available in the Tools menu. If not then read more on: How-to-install-and-load-the-analysis-toolpak.

Filed Under: Data, Formulas Tagged With: Excel Formulas, Functions, Sum

Calculate the number of months between two dates

November 16, 2008 By admin

If you want the number of months occurring regardless of the day of the month then continue reading. If you want to calculate ‘whole month intervals’ by considering the day of the month, then check this post.

To calculate the number of months between two dates, there are two possibilities:

1. Both dates occur in the same year.
2. Dates occur in two different years.

Case 1 – both dates occur in the same year

Use the MONTH function to extract the month from each date, then subtract the two months from each other.

Example (using format:dd/mm/yyyy):

Suppose you have the date 25/09/2007 in cell A1 and the date 31/12/2007 in cell B1 (both cells formatted as date). To calculate the number of months between these two dates you use the following formula:

=MONTH(B1) – MONTH(A1)

This should give a result of 3.

Note that the cell where you write the formula must be formatted as number and not date.

Case 2 – dates occur in two different years

Use the the YEAR function and the MONTH function.

Example (using format:dd/mm/yyyy):

Suppose you have the date 25/09/2007 in cell A1 and the date 31/03/2008 in cell B1 (both cells formatted as date). To calculate the number of months between these two dates you use the following formula:

=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)

This should give a result of 6.

Again the cell where you write the formula must be formatted as number and not date.

Filed Under: Date and Time, Formulas Tagged With: Date, Dates, Month

How to calculate the difference between two dates

November 14, 2008 By admin

To calculate the difference or the number of days between two dates, you simply subtract one date from the other.

Example (using format:dd/mm/yyyy):

Suppose you have the date 25/11/2007 in cell A1 and the date 15/01/2008 in cell B1 (both cells formatted as date). To calculate the difference between these two dates you use the following formula:

=B1 – A1

This should give a result of 51 ( 5 days in Nov + 31 in Dec + 15 in Jan).

Note that the cell where you write the formula must be formatted as number and not date.

Filed Under: Date and Time, Formulas Tagged With: Date, Dates

How to sum values based on a criteria or condition

November 13, 2008 By admin

To sum values based on a specified criteria or condition you use the SUMIF function, and its syntax is as follows:

SUMIF(range,criteria,sum_range)

Range is the range of cells that you want to be evaluated using the specified criteria.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added or summed.

Sum_range are the actual cells to add if their corresponding cells in the range match criteria.

In the following example if we want to sum all the sales for Product3 then we will use the following formula:

=SUMIF(A2:A13;”Product3″;C2:C13)

I.e. we will search for “Product3″ in the range A2:A13. When it is found then the corresponding value (in the same row) from the range C2:C13 will be added to the sum. 

This will give a result of 465 (77 + 200 + 188).

Filed Under: Functions Tagged With: Criteria, Functions, Sum, SUMIF

A macro to print only visible sheets in a workbook

November 11, 2008 By admin

If you have a workbook with a considerable number of worksheets, some visible and other hidden, and you want to print only the visible sheets, then you can use this macro:

Sub PrintVisibleSheets()
‘***************************************************
‘Print only visible sheets in the active workbook *
‘***************************************************
Dim wSheet As Worksheet
For Each wSheet In ActiveWorkbook.Worksheets
If wSheet.Visible Then wSheet.PrintOut
Next
End Sub

This code is also available in the Downloads page.

Copy the code to a new module in Visual Basic Editor, then run it like any other VBA macro.

Check this post if you want to know How to write a VBA macro.

Filed Under: Printing, Visual Basic Tagged With: Printing, Visible, Workbook, Worksheets

How to summarize data using subtotals

November 10, 2008 By admin

One of the options in the Data menu is the Subtotals. It allows you to create subtotals for a specific column in your worksheet, based on a key in another column.  The subtotals will be added to the original data which will be retained as it is.

The best way to explain how to create a subtotal is by using an example.

In the image below you see a list of products, salesmen and total sales for a product / salesman.

To create subtotals of the “Total Sales” by product, you perform the following steps:

1. Prepare your data table, make sure you have columns headings and sort the data by your key or category (Product in our example).

2. Select the data range or simply click on a cell within the range.

3. In the Data menu click Subtotals. The Subtotal dialogue will open.

4. From the combo box labeled “At each change in:” select the category on which you want to base the subtotals.

5. From the combo box labeled “Use function:” select the function to be used in calculating the subtotals. Usually you will use the SUM function, but in some cases you may just want to know “how many items” so you will use the COUNT function instead.

6. From the list labeled “Add subtotal to:” select the column to be summarized into subtotals.

7. Click OK.

8. The new list of your data will look like this.

9. When the subtotals are created, Excel also defines groups based on the rows used to calculate the subtotals. The groupings form an outline of your worksheet based on the criteria that you used to create the subtotals.

At the top left corner of the image above you will see the numbers 1 2 3. This is the outline of your subtotals.

  • Click on 3 and you will get all details of your data.
  • Click on 2 and you will only get the subtotals by category.
  • Click on 1 and you will only get the grand total.

This is what you get when you click “2″.

Note:

To remove subtotals:

1. In the Data menu click Subtotals.

2. When the Subtotal dialogue opens, click the button labeled “Remove All”.

Filed Under: Data Tagged With: Data, Subtotals

How to print all sheets in a workbook

November 8, 2008 By admin

To print all worksheets in a workbook then :

1. In the File menu click Print.

2. The Print window will open.

3. At the bottom of the Print window find a section labeled: Print what .

4. Click on the radio button labeled: Entire workbook.

5. Click OK.

Applies to Excel 2003

Filed Under: Printing Tagged With: Printing, Workbook, Worksheet, Worksheets

How to copy or move worksheets within the same workbook

November 7, 2008 By admin

To copy or move worksheets within the same workbook follow these steps:

1. Switch to the workbook which has the sheet to be copied or moved.

2. in the Edit menu click Move or copy sheet.

3. The Move or copy dialogue will open.

4. The To book drop down will default to the current workbook. Leave this as it is.

5. in the Before sheet list click where you want the sheet to be moved (position within the workbook).

6. Select the Create a copy check box if you want to retain the original worksheet , or deselect it if you just want to move the worksheet to a new location withinthe workbook.

Applies to Excel 2003


Filed Under: General Tagged With: Copy, Move, Workbook, Worksheet
« Older Posts