Posts Tagged ‘excel 2007’

Formulas To Determine Quarters For Calendar And Fiscal Years (XL2000-2010)

Sunday, March 6th, 2011

If you ever need to determine in which quarter of the year a date falls, it’s pretty easy, right? Jan-March is Quarter 1, April-June is Quarter 2, etc…

But what if you need to determine this value for many date cells in your worksheet? Or maybe you have formula in your worksheet whose calculation is based on the quarter. Here’s a formula that makes determining which quarter of the year a date falls a snap.

=ROUNDUP(MONTH(A2)/3,0)

In this example, A2 is the cell containing a date.

Formula to determine quarter of year

In many areas of business it is often necessary to determine in which quarter of a Fiscal Year a date falls. Here is a formula that you can use…

=MOD(CEILING(22-FY_Start_month+ MONTH(Cell_Ref),3)/3,4)+1

In this formula, the variables are FY_Start_Month, which is the month in which your Fiscal Year begins and Cell_Ref is the cell containing the date you want to evaluate.

In this example, the fiscal year begins in October, and the date you want to evaluate is in cell A2. So the formula is…

=MOD(CEILING(22-10+MONTH(A2),3)/3,4)+1

Formula to determine fiscal quarter of year

Microsoft Excel VLOOKUP Can Save You Many Hours Of Work (Excel 2007, Excel 2003, Excel 2002, Excel 2000)

Sunday, May 10th, 2009

There’s a very powerful function in Microsoft Excel that many Excel users don’t know about.

Whether you are using Excel 2007, Excel 2003, Excel 2002 or Excel 2000, it could be saving you hours and hours of work.

It can reduce many hours of manual work down to just a few seconds once you’ve got it set up. I recently showed it to a new co-worker of mine and it reduced two days work down to a few seconds work.

Imagine what that would be worth to you.

How much time would that save you in the run of a year?

What would that be worth to your company?

VLOOKUP is a function that is used in a worksheet to return a value from a table (either in the same sheet, another sheet or another workbook) that is related to the value you give it.

Say you have a parts list in a table on one sheet containing thousands of parts numbers and their related information. The table shows the part number in the first column, the part name in the second column, the part price in the third column. On a separate sheet you have an invoice with columns for the Quantity, the Part #, the part Description, the Price and the Total.

Excel VLOOKUP example

Reduce hours of work down to seconds when you use Excel's VLOOKUP function


The Description and Price columns in the Invoice sheet contain VLOOKUP functions. When you enter the part number in the Part # column on the Invoice, the VLOOKUP function in the Description column looks down the first column of the parts table until it finds a match for the part number you entered, it then looks across the row for the related Description and returns it to the Invoice sheet. A similar function finds the price for the part number.

Here’s the syntax of a VLOOKUP Function:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

In this sample the formula is =VLOOKUP($D19,parts_list!$A$1:$C$22,2,0)

The lookup_value is the value to search in the first column of the table.

The table_array can be two or more columns of data. You can use a reference to a range or a range name. The values in the first column of the table are the values searched by lookup_value. These values can be text, numbers, or logical values.

The col_index_num is the column number in table from which the matching value must be returned. A col_index_num of 1 returns the value from the first column in table; a col_index_num of 2 returns the value from the second column in table, and so on.

Range_lookup specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE (or 1), an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. The values in the first column of table must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. If FALSE (or 0), VLOOKUP will find only an exact match. In this case, the values in the first column of table do not need to be sorted. If there are two or more values in the first column of table that match the

lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

To avoid #N/A errors when a VLOOKUP function does not find a match, modify the formula with an IF statement

=IF(ISNA(formula),””,formula)

=IF(ISNA(VLOOKUP($D19,parts_list!$A$1:$C$22,2,0)),”",VLOOKUP($D19,parts_list!$A$1:$C$22,2,0))

I hope this helps you understand the power of the VLOOKUP function.

Microsoft Excel 2007: Change Default File Format For Saving Workbooks

Wednesday, January 21st, 2009

In Microsoft Office Excel 2007, by default workbooks are saved as Excel Workbooks (.xlsx). You can easily change the default file format to Excel 97-2003 (.xls). This is a great solution for Excel 2007 users who frequently share workbooks with others who are still using an earlier version of Microsoft Excel. You can still save your workbooks in any supported format but you will save time and frustration if your default ‘Save As’ format is the one you use most frequently.

  1. Click the Microsoft Office button;
  2. Click Excel Options button;
  3. Select the Save category and for the first option. ‘Save files in this format‘, click the Excel 97-2003 (.xls) format.

Please note that…

  • All new workbooks will be saved in the default file format unless you choose a different format in the Save As dialog box when you save a workbook.
  • Previously saved workbooks will be saved in the format that they had previously been saved in.
  • Some Excel 2007-specific features and formatting will not be saved with the earlier file format

Learning better ways for working with Microsoft Excel 2007 (and earlier versions) has been made easier with Spreadsheet Tips From An Excel Addict, a FREE weekly newsletter available at The Excel Addict.

Welcome to The Excel Addict Blog

Wednesday, January 14th, 2009
Francis Hayes (The Excel Addict)

Francis Hayes (The Excel Addict)

Hi, my name is Francis Hayes. I have been using Microsoft Excel for more than 18 years and have become ‘addicted’ to the almost unlimited potential it gives to anyone who does a little digging for its hidden gems.

Most Excel users just use it as a plain spreadsheet. I’m much more curious and adventurous. For the past 18 years, I have been uncovering Excel’s hidden secrets that open up a new world of possibilities that 95%+ of Excel users don’t know about.

Over the years, I became frustrated because of the attitude of most Excel users who continue using Excel as a ‘glorified calculator’ even after been shown some of it’s time-saving, money-saving, frustration-decreasing, accuracy-assuring features.

In 2003, to release some of my frustration, I created my own website, TheExcelAddict.com, and started publishing a FREE weekly newsletter, Spreadsheet Tips From An Excel Addict to share my knowledge with an audience of Microsoft Excel users who ARE interested in expanding their Excel knowledge.

After more than 7 years and 18,000+ subscribers, I decided to create this blog to further spread my message to those who may be looking for better and faster ways to do things in Excel and want to differentiate themselves from the other 95% of Excel users.

It may be hard to believe, but just the fact that you’re reading this blog tells me that you are in the top 5% of Excel users.

My passion is helping others discover the amazing power hidden in Excel. If you’re as passionate about Excel as I am, I hope you will come back to my blog regularly to learn some of the things I have discovered over 18 years of using Microsoft Excel and want to share with the world.

P.S. If you love Excel as much as I do, get my weekly newsletter and see how learning a few of these tips every week will, over time, make your productivity soar beyond what you’ve ever thought possible.

Sign up NOW for free at http://www.TheExcelAddict.com