Excel Digest

  Excel help for the rest of us

Archive for July, 2008

31 Jul

How to format a date with month in upper case

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

26 Jul

How to perform autofill without dragging the fill handle

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

21 Jul

How to hide rows based on a cell value

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

18 Jul

Macro based monthly calendar template

I have added to the downloads page a 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 [...]

16 Jul

How to use the INDEX function

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

13 Jul

How to update existing chart, Part 3: Chart Options

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:

Click on the chart.
On the [...]

09 Jul

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

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.

06 Jul

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

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.

03 Jul

How to use the Match function

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

01 Jul

How to make your macros and functions available to all workbooks

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

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

GPS Reviews and news from GPS Gazettewordpress logo