http://www.exceldigest.com/myblog Excel help for the rest of us Wed, 19 Nov 2008 11:25:31 +0000 http://backend.userland.com/rss092 en 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 ... http://www.exceldigest.com/myblog/2008/11/18/how-to-sum-values-based-on-multiple-criteria/ 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 ... http://www.exceldigest.com/myblog/2008/11/16/calculate-the-number-of-months-between-two-dates/ 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 ... http://www.exceldigest.com/myblog/2008/11/14/how-to-calculate-the-difference-between-two-dates/ 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 ... http://www.exceldigest.com/myblog/2008/11/13/how-to-sum-values-based-on-a-criteria-or-condition/ 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 ... http://www.exceldigest.com/myblog/2008/11/11/a-macro-to-print-only-visible-sheets-in-a-workbook/ 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 ... http://www.exceldigest.com/myblog/2008/11/10/how-to-summarize-data-using-subtotals/ 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 2003ShareThis http://www.exceldigest.com/myblog/2008/11/08/how-to-print-all-sheets-in-a-workbook/ 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 ... http://www.exceldigest.com/myblog/2008/11/07/how-to-copy-or-move-worksheets-within-the-same-workbook/ If you know Visual Basic programming then you can create your own VBA macro and run from within Excel. Follow the steps below: 1. On the Tools menu select Macros. 2. On the Macro submenu select Visual Basic Editor. 3. ... http://www.exceldigest.com/myblog/2008/11/05/how-to-write-a-vba-macro/ To print more than one worksheet in a workbook then : 1. Select the worksheets you want to print. If the sheets to be printed are adjacent then click on the first one, press SHIFT then click the last one. If they are not then click on the first one, press CTRL then ... http://www.exceldigest.com/myblog/2008/11/04/how-to-print-more-than-one-sheet-in-a-workbook/ To copy or move worksheets between workbooks 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. In the To book drop down select the destination workbook. You ... http://www.exceldigest.com/myblog/2008/11/03/how-to-copy-or-move-worksheets-between-workbooks/ I have added to the downloads page a yearly calendar template. This is a dynamic template that will allow you to generate a calendar for any year between 2001 and 2016. There are two combo boxes for the user to select the year and the start date of the week. ... http://www.exceldigest.com/myblog/2008/10/29/macro-based-yearly-calendar-template/ To get the week number of a given date, i.e. to find out where the week falls numerically within a year you use the WEEKNUM function. Syntax WEEKNUM(serial_num,return_type) serial_num : is a valid date. E.g. August 15, 2008 or 12/10/2008. Return_type : is a number that determines on which day the week begins. (1 ... http://www.exceldigest.com/myblog/2008/10/22/how-to-get-the-week-number-of-a-given-date/ The Analysis Toolpak is an Excel add-in program that is available when you install Microsoft Office or Excel. To use it in Excel, however, you need to load it first. 1. On the Tools menu, click Add-Ins. 2. In the Add-Ins available box, select the check box next to Analysis Toolpak, and ... http://www.exceldigest.com/myblog/2008/10/21/how-to-install-and-load-the-analysis-toolpak/ In a previous post I covered counting how often a value occurs within a range. In that post we looked into one criteria, and we used the COUNTIF function. But what if we want to count cells that meet multiple criteria. We can not use the COUNTIF function  in this case, ... http://www.exceldigest.com/myblog/2008/10/20/count-how-often-a-value-occurs-multiple-criteria/ To change Excel default file location, follow these steps: 1. In the Tools menu click Options. 2. When you get the Options window, click the General tab. 3. Towards the bottom of the window there is a label called "Default file location". To the write of this label is a text box. Enter ... http://www.exceldigest.com/myblog/2008/10/18/how-to-change-excel-default-file-location/ To change Excel default font name or size, follow these steps: 1. In the Tools menu click Options. 2. When you get the Options window, click the General tab. 3. Towards the bottom of the window there is a label called "Standard font", and to the write of it are two combo boxes ... http://www.exceldigest.com/myblog/2008/10/14/how-to-change-excel-default-font/ To count how often a value occurs within a range we use the COUNTIF function. Because this function count cells based on a given criteria, that fact will help us limit the count to our specified value. If in the example below we want to count how often "John" appeared in the ... http://www.exceldigest.com/myblog/2008/10/10/count-how-often-a-value-occurs-within-a-range/ What is a pivot table? Pivot table is a feature of Excel that takes a long list of data, summarizes it into a shorter list that is more easy to analyze and assimilate. The best way to explain how to create a pivot table is by using an example. In the image below ... http://www.exceldigest.com/myblog/2008/10/07/how-to-summarize-data-using-a-pivot-table/ If you have a column of data that contains duplicates, then you can use the advanced filter option to identify and copy the unique values to a different location. You can then easily count those values using COUNTA function or the ROWS function. Perform the following steps to achieve this goal: 1. ... http://www.exceldigest.com/myblog/2008/10/02/how-to-filter-and-count-unique-values-among-duplicates/