You are here: Home / Archives for October 2008

Macro based yearly calendar template

October 29, 2008 By admin

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. Each selection will trigger a macro that will generate the calendar. Here are a few notes to consider:

To Enable Macros:

Select : Tools —> Macro —> Security —> Security Level —> Medium

Names of days and months

I have put the names of days and months as tables in the “Settings” worksheet. This will allow you to translate them in your own language if necessary. You have to unprotect the sheet first before you can do that.

Protection

1. All sheets are protected to avoid accidental change of data and formulas. If you want to make some changes like formatting for example, then you can unprotect the sheet. Make sure however that you don’t mess up the formulas.
2. Columns X and Y are hidden but not protected. These columns receive data from the two combo boxes for the year and the start date.

Printing

You can print the calendar just like any other Excel worksheet. The print area is set in a way that it will only print the year and the 12 boxes for the months, i.e. without the combo boxes.

VBA code

If you want to see the code behind all this, then switch to visual basic editor. The main routines are in module2, but some of the code is within different events of the workbook and the combo boxes.

Your feedback is appreciated.

Applies to: Excel 2003

Filed Under: Date and Time, General, Visual Basic Tagged With: Calendar

How to get the week number of a given date

October 22, 2008 By admin

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 = Sunday, 2 = Monday). The default is 1.

Example:

If cell A1 = 10/19/2008 , which is a Sunday, then:

=WEEKNUM(A1,1) will return a week number of 43 (week begins in Sunday).

=WEEKNUM(A1,2) will return a week number of 42 (week begins in Monday).

Note:

WEEKNUM function is part of the Analysis ToolPak add-in. If it is not available in your list of functions then you have to install the Analysis ToolPak. Check the following link for more help:

install and load the Analysis ToolPak add-in

Important note (from Microsoft Excel Help)

The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard.

Applies to Excel 2003

Filed Under: Date and Time, Formulas, Functions Tagged With: Week Number, Weeknum

How to install and load the Analysis Toolpak

October 21, 2008 By admin

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 then click OK.

Tip:

If Analysis Toolpak is not listed, click Browse to locate it.

3. If you see a message that tells you the Analysis Toolpak is not currently installed on your computer, click Yes to install it.

4. Click Tools on the menu bar. When you load the Analysis Toolpak, the Data Analysis command is added to the Tools menu.

Note:

To include Visual Basis for Application (VBA) functions for the Analysis Toolpak, you can load the Analysis Toolpak – VBA Add-in the same way you load the Analysis Toolpak. In the Add-ins available box, select the check box next to Analysis Toolpak – VBA.

Source: Microsoft Office Online.

Original article: http://office.microsoft.com/en-us/excel/HP011277241033.aspx

Applies to Excel 2003

Filed Under: Customization, Functions, General Tagged With: Analysis Toolpack, Analysis Toolpak, Tool pack, Toolpack, Toolpak

Count how often a value occurs – multiple criteria

October 20, 2008 By admin

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, so we will take a different approach and use the SUM function.

In the example below we want to count how often “John” appeared in the list in quarter 2 (Q2). i.e. our critera is “John” in column B and “Q2″ in column C. We will use the following formula for this purpose:

=SUM((B1:B17=”John”)*(C1:C17=”Q2″))

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((B1:B17=”John”)*(C1:C17=”Q2″))}

This formula will give us a result of 3 as you can see.

Filed Under: Data, Formulas, Functions Tagged With: Count, Occurs, Sum

How to change Excel default file location

October 18, 2008 By admin

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 here the full path of the required default file location. If you want to type a path to a network server location for the default file location, enter the path by using the UNC (Uniform Naming Convention) syntax: \\servername\foldername.

4. The next time you start Excel and whenever you click the Open button, Excel will show you all the files/subfolders in the specified default location.

You must restart Excel to change the default working folder location to a network server.

Filed Under: Customization, General Tagged With: Default, File Location, General

How to change Excel default font

October 14, 2008 By admin

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 (drop down) for the font name and font size. Select the font name and font size you want.

4. The next time you start Excel and whenever you create a new workbook, your selected font name and font size will be taken as a default.

Applies to Excel 2003

Filed Under: Customization, Formatting Tagged With: Default Font, Font

Count how often a value occurs within a range

October 10, 2008 By admin

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 list, then we will use the following formula:

=COUNTIF(B1:B15;”John”)

This will give us a result of 7 as you can see.

Read more on How to use the COUNTIF function.

Applies to Excel 2003

Filed Under: Data, Formulas, Functions Tagged With: Count, COUNTIF, Occurs

How to summarize data using a pivot table

October 7, 2008 By admin

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 you see a list of products, salesmen and total sales for a product / salesman.

To create a pivot to summarize the total sales by product, you perform the following steps:

1. Prepare your data table and make sure you have columns headings.

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

3. In the data menu click Pivot Table and then follow the wizard.

4. When you get the pivot table field list:

  1. Drop the “Product” field in the area where its says “Drop row fields here”.
  2. Drop the “Total Sales” field in the area where its says “Drop data items here”. You will get the pivot table as shown here:

5. You can then plot a chart using the pivot table instead of your detailed data.

Applies to Excel 2003

Filed Under: Charts, Data Tagged With: Data, Pivot Table

How to filter and count unique values among duplicates

October 2, 2008 By admin

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. First make sure that your column has a header row.

2. Select your data range (including the header row).

3. On the Data menu point to Filter, then click Advanced Filter. The Advanced Filter window will open.

4. Click Copy to another location.

5. The List range text box should have your data range. If not then click on it, go to your worksheet and select the range.

6. Click on the Copy to text box. Go to your sheet and click on a blank column (or just a single cell) where you want your unique vales to be copied.

7. Select the Unique records only check box and click OK.

8. To count the unique values just copied, use the COUNTA function or the ROWS function. E.g if the unique values are in the range E1:E6 then in a separate cell enter one of the following formulas:

1. “=COUNTA(E2:E6)”

2. “=ROWS(E2:E6)”

Applies to Excel 2003

Filed Under: Data, Lists Tagged With: Autofilter, Count, Duplicates