Excel Digest

  Excel help for the rest of us

Archive for October, 2008

29 Oct

Macro based yearly calendar template

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 [...]

22 Oct

How to get the week number of a given date

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). [...]

21 Oct

How to install and load the Analysis Toolpak

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 [...]

20 Oct

Count how often a value occurs - multiple criteria

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 [...]

18 Oct

How to change Excel default file location

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 [...]

14 Oct

How to change Excel default font

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 [...]

10 Oct

Count how often a value occurs within a range

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 [...]

07 Oct

How to summarize data using a pivot table

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 [...]

02 Oct

How to filter and count unique values among duplicates

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 [...]

© 2008 Excel Digest | Entries (RSS) and Comments (RSS)

GPS Reviews and news from GPS Gazettewordpress logo