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

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

Remove ‘ALT+ENTER’ed Line Breaks Without VBA (XL2000-XL2010)

January 25th, 2011

Remove line breaks in Excel

You already know that you can enter a forced line break within a cell by pressing ALT+ENTER, right? If you you want to get rid of the line break you can simply click immediately before the line break and press Delete or click immediately after the line break and press Backspace. 

But what if you have a worksheet that has a lot of these line breaks and you need to get rid of them? You may think that this would require a macro. You’d be wrong! 

Here’s a non-VBA method to quickly get rid of all those line breaks in your worksheet. If you want to remove them from only part of the worksheet, select that range first. Otherwise, select any single cell. 

1) Press CTRL+H (i.e. shortcut for Find and Replace); 

2) Click in the ‘Find what‘ field, hold down the ALT key and type 0010;. It may not seem like anything happened but you actually entered an invisible line break character; 

3) Click in the ‘Replace with‘ field, press the spacebar once and click Find All. You will get a list of all cells containing a line break; 

4) At this point you can choose to Replace All

It may not be apparent but you may have caused some cells to have two blank spaces. You can easily find and get rid of them by repeating the previous 4 steps, using two blank spaces in the ‘Find What‘ field and one blank space in the ‘Replace with‘ field. 

FYI, you can also use a formula to remove these line breaks. 

=SUBSTITUTE(A1,CHAR(10),” “) 

 
 
 

 

How can I change a Name scope from local to Global in Excel 2007?

March 10th, 2010

How can I change a Name scope from local to Global in Excel 2007?

Question: I created a worksheet and duplicated it, along with the Names. I have edited the names but now those are scoped for that sheet only. The Name Manager won’t allow me to change the scope.

Answer: This is so easy that it’s not so obvious:

  1. Select the Named range on your worksheet whose scope you want to change;
  2. Open the Name Manager (Formulas tab) and select the name;
  3. Click Delete and OK;
  4. Click New… and type in the original name back in the Name field;
  5. Make sure Scope is set to Workbook and click Close.

See? That was way too easy!

Francis Hayes (The Excel Addict)

P.S. Click HERE to get more FREE Time-Saving Microsoft Excel tips at TheExcelAddict.com

How To Convert An Excel 2007 Table Back To A Normal Range

May 10th, 2009

remove_table_formatting2

Time-saving tips for Microsoft Excel 2007, 2003, 2002, 2000 from TheExcelAddict.com

After you create a table in Microsoft Excel 2007, you may want to convert it back to a normal range.

Note that when you convert a table to a normal range, the row headers will no longer include the sort and filter arrows and the structured references  (i.e. table name and column header references that are used in the table formulas) will be converted back to regular references.

Convert Excel 2007 Table To A Normal Range:

1) Right-click any cell within the table;
2) On the pop-up menu point to Table, then Convert to Range. You will be asked to confirm your action with a message “Do you want to convert the table to a normal range?

Note that after you convert the table back to a normal range, the table formatting remains. So, as you can see, this can be used as a neat way to format a range of cells with a table format that you like. On the other hand, if you do not want to retain the formatting left behind by the table, you will need to remove it manually.

To convert all of your cells back to default formats, you can easily do this by selecting the entire table (CTRL+A) and then on the Home tab, click the Clear button in the Editing group and choose Clear Formats. However, this removes all formatting including number formats and cell alignment.

To keep the existing number formatting and alignment options, first select the entire table (CTRL+A) and then remove the unwanted formats using the commands (e.g. Fill, Font, Borders, etc…) on the Home tab in the Font group or on the Mini toolbar when you right-click a cell in the table.

If you want to experiment with some of the table formats, immediately after you create the table, you can click Undo on the Quick Access Toolbar (or press CTRL+Z) to convert the table back to a range and the table formatting will be removed.

Find more time-saving tips for Microsoft Excel 2007, 2003, 2002, 2000 at TheExcelAddict.com

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

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.

Resetting The Last Used Cell (Excel 2003/Excel 2007)

April 6th, 2009

Here is something that every Excel user, at one time or another, has a problem with.

I often hear, ‘Why are all these blank pages printing at the end of my worksheet?’ or ‘I originally had 2000 rows in my worksheet but now that I have deleted 1000 of them, I still end up at row 2000 when I press Ctrl + End to get to the bottom of my sheet. Is there a way to fix this?’

Reset the last used cell in Excel 2003

Fortunately there is a very simple solution.

First, delete all rows below (Ctrl + Shift + down arrow) and all columns to the right of (Ctrl + Shift + right arrow) your actual data to ensure there is no formatting left behind.

Pressing Ctrl + End at this point will still bring you to your previous last used cell. To solve the problem, simply save your workbook, and your last used cell will be exactly where it should be. Pressing Ctrl + End now will take you to the ‘real’ end of your data.

Note: In Excel 2007 and Excel 2003, the last used cell is reset when you save your workbook. Earlier versions of Excel may require you to also close and re-open your workbook.
This is just ONE simple solution to ONE annoying problem many Excel users face everyday. If you would like to avoid other time-wasting problems like this one, go to TheExcelAddict.com where you can find many more solutions to boost your Excel skills.

Recover A Deleted Worksheet Sheet Tab in Excel

February 11th, 2009
You probably have discovered that when you (accidentally) delete a sheet there is no Undo option to bring it back. One solution is to close your workbook without saving the changes and then reopen it in its original state. But what if you have made substantial changes to your workbook that you don’t want to lose?
Here is a solution…

  1. Save your workbook with a different filename (File, Save As…)
  2. Re-open your original workbook.
  3. Copy the sheet that you need to recover from the original workbook to the new renamed workbook.
  4. Close the original workbook.
  5. Save the (renamed) workbook using the original name. You will be prompted to overwrite the original file. Click Yes.
  6. Once you have determined that your workbook is functioning properly, you can delete the redundant (renamed) workbook

Once you see this, it seems like very logical solution but I frequently see people recreate all of their work because that have been told that you cannot recover a deleted sheet tab.

Calculate Number of Days Between Two Dates

January 30th, 2009

As you know, Excel is great at arithmetic.

Well, even when it comes to dates, Excel allows you to use simple arithmetic to calculate the number of days between dates buy subtracting one date from another. This is possible due to the way that Excel stores dates.

Each date is equal to 1 and part of a day (i.e. hours and minutes) are stored as a decimal. To Excel, the date July 10, 2009 is actually a number (40,004). If you format a cell containing a date to a number you will see it’s decimal equivalent. The number represents the number of days since January 1, 1900.

With this information, it takes just a simple subtraction formula in Excel to determine the number of days between two dates.

You subtract the oldest date from the newest date. If the dates are contained in cells your formula might be =B1-A1.

If you want to count the days between a certain date and today, you can use the TODAY function in your formula. For example,. =TODAY()-A1

You can follow this link to determine the number of months and years between two dates.

You can also extract the year, month or day from a date which can be useful in formulas.

Easily Convert Formulas To Values

January 24th, 2009

I often see Excel users who need to replace formulas with values doing it manually by typing the value in each cell. If you’re one of those people, I’ve got a much easier way for you — and less chance of making a costly or embarrassing mistake.

A quick and easy way to convert formulas to values, rather than using Paste Special… Values, is by dragging the edge of a cell or range using the right mouse button, dropping it where you want to paste the values (even back to the original cells), and select Copy Here As Values Only.

Position your mouse pointer over the edge of a selected range of cell. The pointer will change to a four-headed arrow.With your left-mouse button, click and drag away from then back to its original position.

It’s so simple!

Replace formulas with values in Microsoft Excel

Replace formulas with values in Microsoft Excel

Microsoft Excel 2007: Change Default File Format For Saving Workbooks

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.

Microsoft Excel: Prevent Automatic Creation Of Hyperlinks

January 19th, 2009

When you type a website address or an e-mail address into your worksheet, Excel will create a hyperlink. Often, this is not you want. To remove these unwanted hyperlinks you can simply press Crtl+Z (i.e. Undo) immediately after the hyperlink is created. This works fine for a hyperlink or two, but what if you just don’t want Excel creating these hyperlinks automatically.

Here are some suggestions:

1) In Excel 2002 and Excel 2003 you can turn off the option for creating these hyperlinks. From the Tools menu, select Autocorrect Options. Click the ‘AutoFormat As You Type‘ tab and remove the check mark from the ‘Internet and network paths with hyperlinks‘.

2) In Excel 2000, the best you can do is type an apostrophe (‘) before the address (i.e. ‘www.brigus.net)

If you are familiar with creating macros here’s a simple and quick macro that will get rid of all hyperlinks in the current worksheet.

Sub DeleteAllHyperlinks()
Cells.Hyperlinks.Delete
End Sub

This is just one of hundreds of solutions to common problems you will face when working in Excel. But most problems have a solution. You just need to know where to find it. A good choice is at http://www.TheExcelAddict.com

How To Quickly Delete Blank Rows From A Long List (Excel 2003, Excel 2007)

January 16th, 2009

Do you ever work with long lists of data in Excel that contain a large number of blank rows that you need to delete?

You could select each row, one by one, and delete them individually. However, if you have a lot of rows to delete, this could be very time consuming. You may have hundreds, even thousands of empty rows that you want to remove. Deleting them one by one would be downright impractical, not to mention, mind-numbing.

If you have a long list of data that contains blank rows that you want to remove, here there a few much better options.

Option #1:

You could sort the rows alphabetically, which would put all the blank rows together, and then delete that group of blank rows. However, there will be times when you may want to maintain the order of your list, so this may not be an option.

Option #2:

You could filter the list for blank cells, select these rows (i.e. visible cells) and delete them.

Option #3:

You could use a little-known shortcut to quickly select the blanks in your list and delete them.

Today, I will cover option number 3 and in a future post I will cover option number 2. The first option  above is self-explanatory and often is not your best choice, so I won’t discuss it here.

Suppose you receive a list of data that contains blank rows that you want to remove without affecting the order of the list. This technique will allow you to delete all rows that have blank cells in a particular column. Here’s the fastest way to do it.

  1. First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data;
  2. Now, select the cells in one column from the top of your list to the bottom;
  3. Make sure that all the blank cells in this selected range are the rows you want to delete;
  4. Press the F5 key on your keyboard (or select Edit, Goto);
  5. Click the Special button;
  6. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected;
  7. Now choose Edit, Delete, select the Entire Row option and click OK. Almost instantly, all blank rows have been deleted

delete blank rows in excel
If you work with large lists of data in Excel, this tip will save you a lot of time. If you don’t work with large lists often, remember this tip anyway, because sooner or later you’re going to need this.

Spreadsheet Tips From An Excel Addict” is a FREE weekly newsletter and “101 Secrets of a Microsoft Excel Addict” is a 107-page ebook by Francis Hayes (The Excel Addict).

Find out how to boost your Microsoft Excel skills week by week at www.TheExcelAddict.com or check out my blog at www.TheExcelAddict.com/blog

Welcome to The Excel Addict Blog

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