You are here: Home / Archives for July 2008

How to format a date with month in upper case

July 31, 2008 By admin

If you want to format a date with month in upper case, then hear is how you do it:

1. The general format for getting the month in three character is this:

=TEXT(cell_reference,”mmm”)

2. To get upper case you enclose the TEXT function within the UPPER function as follows:

=UPPER(TEXT(cell_reference,”mmm”))

So if we have a date in cell A1 for example then our functions will be as follows:

=UPPER(TEXT(A1,”mmm”))

If you want to return the full date then you have to concatenate the day and the year with the month as follows:

=DAY(A1)&UPPER(TEXT(A1,”mmm”))&YEAR(A1)

The final result will be somthing like: 15AUG2008

Filed Under: Date and Time, Formatting, Formulas, Functions Tagged With: Date, Format Date, Month, Uppercase

How to perform autofill without dragging the fill handle

July 26, 2008 By admin

If you have a large worksheet with formulas and you want to perform an autofill, then it may be a tedious job to drag the fill handle across hundreds of rows or columns. Here is a simple procedure to make your life easier:

1. Select the first cell in your range (the one which has a formula already).
2. While holding down the shift key go to the last cell in the range and select it. Now you should have all your range selected.
3.In the Edit menu click Fill then Series.

Fill Series 1

4. When you get the Series window click Autofill.

Fill Series 2

All cells in the range should now be updated with formulas.

Filed Under: Data, Formulas Tagged With: Autofill, Data, Formulas

How to hide rows based on a cell value

July 21, 2008 By admin

The autofilter feature in Excel allows you to show a subset of your data based on a list of unique values derived by the auotfilter. You can also use this feature to hide rows of data based on certain conditions you specify. here is how you can do it.

1. Click Data –> Filter –> Autofilter.


2. In the column which have the criteria for hiding rows,click the arrow of the list box.

3. From the drop-down list Select custom.
4. The Custom Autofilter window will open.

4. Suppose in this example we want to hide all rows with value less than 10, so In the list where it says “Show rows where” , select “is greater than or equal”.
5. In the list to the right select the value which qualify the row to be shown. In our example we will select 10.

This will hide all the rows which have a value less than 10.

Applies to: Excel 2003

Filed Under: Customization, Data Tagged With: Autofilter, Data, Hide, Rows

Macro based monthly calendar template

July 18, 2008 By admin

I have added to the downloads page a dynamic monthly calendar template. There are three combo boxes for the user to select the month, 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

Proverbs

1. This calendar uses function RANDBETWEEN from the analysis toolpack to generate a random number for the proverb lookup.
2. Every time recalculation occurs you will get a different proverb (hit PF 9 to recalculate)
3. If you don’t have the analysis toolpack then copy the following formula in cell “A12″ and remove the quotes. You will get a proverb depending on the selected month.
“=VLOOKUP(I2,Settings!$C$2:Settings!$D$13,2,FALSE)”
4. The proverbs are stored in the “Settings” worksheet. If you don’t like my selection then you can replace them with your own.

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 H and I are hidden but not protected. These columns receive data from the three combo boxes for the month, year and start date.

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: General, Visual Basic Tagged With: Calendar, Calendar Template, Monthly Calendar

How to use the INDEX function

July 16, 2008 By admin

The INDEX function returns an item from a specific position (row, column) within a given array or a data range. Row and column here refers to the relative position of the item within that array and not within the worksheet. E.g. this function will return the value in row 3 of the worksheet, because that row represents the second row of the range.

=INDEX(A2:D7,2,3)

Use the INDEX function when you know the position of an item in a range and you want the actual value of the item.

Syntax

INDEX(array,row_num,column_num)

array: is a contiguous range of cells , or can be an array constant.

row_num: the row number, within the array, from which the item is to be returned. If the array contains one row then this argument is optional.

col_num: the column number, within the array, from which the item is to be returned. If the array contains one column then this argument is optional.

Examples:

In the array shown in the image below:

=INDEX($A$2:$B$7;3;1) will return “student3″, which is in the intersection of row 3 and column 1 of the array.

=INDEX($A$2:$B$7;5;2) will return “201″, which is in the intersection of row 5 and column 2 of the array.

probably there aren’t many practical uses of the INDEX function by itself, but the INDEX and MATCH functions together are a very good combination. You use the MATCH function first to get the relative position of an item, either in a row or in a column, and then feed that value into the INDEX function.

Applies to: Excel 2003

Filed Under: Functions Tagged With: Functions, Index

How to update existing chart, Part 3: Chart Options

July 13, 2008 By admin

As I mentioned in part1 and part 2 of this series you can update an existing chart to change some of its properties without having to recreate the chart from scratch. In this post we will see how to update the chart options:

  1. Click on the chart.
  2. On the top menu Click Chart then click Chart Options.

  3. The Chart Options window will open.

chartoptions2

There are six tabs in Chart Options window.

1. Titles

You can change the chart main title, category (X) axis title or Value (Y) axis title.

2. Axes

Here you specify what you want to see as labels in the category (X) axis or the Value (Y) axis.

3. Gridlines

Specify whether you want gridlines in the category (X) axis or the Value (Y) axis.

4. Legend

Specify where you want the legend to appear relative to the chart.

5. Data Labels

Data labels allow you to put labels on your data (the columns in our example). You can chose to put series names, category names or values.

6. Data Table

Data table gives you the option to show your actual data below the chart.

Please note that some of these options are not available for certain types of charts.

Filed Under: Charts Tagged With: Chart Options, Charts

How to update an existing chart, Part 2: Data Series

July 9, 2008 By admin

As I mentioned in part 1 of this series you can update an existing chart to change some of its properties without having to recreate the chart from scratch. In this post we will see how to update the data series:

1. Click on the chart.

2. On the top menu Click Chart, then click Source Data.

\

3. The Source Data window will open.

\

4. Click on the Series tab.

5. On the left you will find a list of your series. When you click on an individual series, its name and value will show on the right. You can override those with actual values or with cell references.

6. To change the name of a series for example, click on the Name textbox. Either right the name directly, or to reference a cell go to your worksheet and select the desired cell.

7. Here you can also change the Category(X) axis labels. Click on the textbox then type the names of the labels separated by commas, or you can refer to worksheet cells as I mentioned earlier.

Applies to: Excel 2003

Filed Under: Charts Tagged With: Charts, Data Series

How to update an existing chart, Part 1: Data Range

July 6, 2008 By admin

If you have an existing chart and you want to change the source data of that chart, by expanding it for example, then instead of recreating the chart you can just update it as follows:

1. Click on the chart.

2. On the top menu Click Chart, then click Source Data.

\

3. The Source Data window will open.

\

4. Click on the Data Range tab.

5. In the Data Range textbox (below the chart) highlight the current data range.

6. Go to your worksheet and select your data range.

7. The new data range will be reflected on the textbox. And the chart will be updated accordingly.

Applies to: Excel 2003

Filed Under: Charts Tagged With: Charts, Data Range

How to use the Match function

July 3, 2008 By admin

The MATCH function searches for an item (lookup value) in a given array or a data range (lookup array), and if I understand it right the array can only be within one column or within one row but I could not find that mentioned explicitly in any reference. When the item is found, MATCH returns the relative position of the item within that array and not within the column. I.e. if your array is A6:A10 and a match is found in A7, then MATCH function will return 2 and not 7.

Use the MATCH function instead of one of the other LOOKUP functions when you need the position of an item in a range instead of the item itself.

Syntax


MATCH(lookup_value,lookup_array,match_type)

Lookup_value: is the value you want to match in lookup_array. It can be a value like =MATCH(7,A1:A15,0)or a cell reference like =MATCH(B2,A1:A5,0).
Lookup_array: is a contiguous range of cells containing possible lookup values. Lookup_array must be an array like this: =MATCH(“b”,{“a”,”b”,”c”},0) or an array reference like MATCH(7,A1:A15,0).
Match_type: is the number -1, 0, or 1 as seen here: =MATCH(B2,A1:A5,0). Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

What each match type means:

  1. Type 1 : MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.
  2. Type 0 : MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
  3. Type: -1 : MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.
  4. If match_type is omitted, it is assumed to be 1.

In the example below:

  1. “=MATCH(147;$B$2:$B$7;1)” : no exact match for “147″ so the relative position of “145″ is returned.
  2. “=MATCH(201;$B$2:$B$7;0)” : An exact match for “201″ is found with a relative position of “5″
  3. “=MATCH(201;$B$2:$B$7;-1)” : returned an error because match type (-1) requires data to be sorted in descending order.

\

For the third version of MATCH to work properly you need data to be sorted in descending order like this:

In this example 3 is returned because 85 is the smallest value that is greater than our lookup_value (80), and its position is no. 3 from the top.

Notes:

  1. MATCH does not distinguish between uppercase and lowercase letters when matching text values.
  2. If match_type is 0 and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Applies to: Excel 2003

Filed Under: Data, Functions Tagged With: Excel Functions, Lookup Functions, Match

How to make your macros and functions available to all workbooks

July 1, 2008 By admin

When you create a macro or a user defined function in a workbook, by default that macro will only be visible for that specific workbook.
If you have a code that you use repeatedly and you want it to be available for all workbooks then you have to create a global workbook called Personal.xls and save it in Excel startup folder.

How to create the file:

1. Open a new workbook.
2. Save As Personal.xls in folder:
C:\Program Files\Microsoft Office\OFFICE11\XLSTART
3. Copy your macros from their original workbooks to personal.xls.
4. Click Window –> Hide to hide the file.

The path may differ slightly depending on which version of office you are using.

How to use the file:

1. Whenever you start Excel personal.xls will be opened automatically, and all your macros will be available for use in any workbook.

2. Keep personal.xls hidden unless you want to add macros to it.

3. When you want to add new macros Click Window –> Unhide and select “personal.xls”.

4. To add a new macro click Tools –> Macro –> Record new macro. You will get the following dialogue box.

savemacro

5. Under “Store macro in:” select “Personla.xls”.

6. When you are done hide the file again.

If you are interested in more details please see the following Microsoft office link:

Deploy your Excel macros from a central file

Applies to: Excel 2003

Filed Under: Functions, General, Visual Basic Tagged With: Macros, Personal.xls, User Defined Functions, Visual Basic