Excel 2000, 2002, 2003, 2007 Tips

The Excel Addict - Help with Excel 2007

Publication Date: March 31, 2010
 


This newsletter is being sent to 15,000+ Excel 'addicts' around the world
using Aweber, the best solution for managing your email campaigns.  

Greetings from The Excel Addict

Hi fellow Excel Addict,

The Excel Addict Donation to Haiti Earthquake ReliefAt times when things are going a little slower than I would like, I begin to wonder what impact this newsletter is having. But this past week has truly been amazing. The overwhelming positive attitude of my readers who really appreciate the information I am sharing has filled my Inbox with testimonial after testimonial.

One of my mentors, Jim Rohn, used to say that you would be doing a disservice to mankind if you choose NOT to share knowledge you have that would be of value to others. Following on his advice, I made my special ebook offer this week. If you had already purchased my ebook, the extra emails this week may have been a tiny bit annoying but, as I realize that everyone doesn't open every email everyday, without those extra reminders, hundreds of new Excel users may not have discovered 101 new ways to use Excel.

In this newsletter, I share my tips, I share some quotes and I occasionally share some personal experiences. If I discover something that I think some of my readers would find valuable, I will share it. When I finish my next ebook, I will let you know. After all, you're the reason I'm doing all this. Well, that's partially true. The other reason is that I'm addicted to Excel.

As always, I appreciate your feedback.

To your SUCCESS !
Francis Hayes (The Excel Addict)
 

If your want to send me an email...

• Please type 'The Excel Addict' in the Subject field of your emails.
• If you reply to this email, please delete the newsletter content before Sending.
• Remove background color in Outlook 2007 - Options, Page Color, No Color.



View The Excel Addict Francis J Hayes's profile on LinkedIn   Find out what The Excel Addict is doing NOW

This week's tips..

1) Formulas To Determine Quarters For Calendar And Fiscal Years (XL2000-2007)
2) Separate Sorted Groups Of Data With Borders (XL2003/XL2007)
3) Easily Duplicate A File In Windows Explorer (Windows XP)


If you're having trouble displaying graphics in this email, I have posted an online HTML version of this week's newsletter here.

Last week's tips were...

1) Simultaneously View Formulas And Results In The Same Workbook (XL2000-XL2007)
2) Copy A Picture Of Worksheet Range To An Email (XL2003/XL2007)
3) Uncover The Hidden 'No to All' Option When Copying Files (Windows XP)


You can still find last week's newsletter here.

You can access even more tips on my website by going to my members' page.  

QUOTE OF THE WEEK:

"Patience is waiting. Not passively waiting. That is laziness. But to keep going when the going is hard and slow - that is patience." -- unknown source



Make sure you save a copy of your spreadsheet before trying these tips, just in
case you make an error or the procedure doesn't produce the results you want.


Microsoft Excel Tip #1

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

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 will do that.

=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




Pull answers from your lists with these 45 LOOKUP formulas

Microsoft Excel Tip #2

Separate Sorted Groups Of Data With Borders (XL2003/XL2007)

When you have a large table of sorted data, it is often difficult to see the where one group ends and another begins. Using a border to separate each group makes it much easier for the reader, however, with a large table and many groups, manually adding borders between each group would be very time consuming. Also, when you make changes to your table, the borders may get out of sort.

Here is a neat trick using Conditional Formatting that will automatically draw borders between your sorted groups. The nice thing about doing this with Conditional Formatting is that, even after you resort or add more data, the borders will automatically readjust.

This example is based on a table of data in cells A1:C50 sorted by column A.

First, click any cell in the table and press CTRL+SHIFT+8 to select the entire table. Note: You must press the 8 key on the row of number keys, not on the numeric keypad.

Conditional Formatting steps in Excel 2007 are slightly different than earlier versions, so I have given instructions separately.

For Excel 2007:

Add borders with Conditional Formatting1) On the Home tab, in the Styles group, click on Conditional Formatting and then New Rule...;

2) In the 'Select a Rule Type' section, click 'Use a formula to determine which cells to format' The 'New Formatting Rule' dialog appears;

3) Click in the 'Format values where this formula is true' field and enter this formula:

=$A1<>$A2

In this example, the table is sorted by column A. If your table is sorted by a different column, use that column letter in the formula. 

Row 1 (e.g. $A1) in this formula refers to the row number of the 'active cell'. You can see the address of the 'active cell' displayed in the Name box to the left of the Formula Bar. 

Row 2 (e.g. $A2) refers to the row immediately below the active cell. 

The dollar signs apply absolute referencing for the column. No dollar signs means relative referencing for the rows because we are checking the condition for only column A;

4) Click the Format... button;. This will open the Format Cells dialog;

5) Click the Border tab, select a color, click the Bottom border icon and click OK to close the dialogs.

You should now see a border separating each group.

For Excel 2000-2003:

Apply borders using Conditional Formatting

1) From the Format menu and select Conditional Formatting;

2) In the left dropdown select Formula Is;

3) In the Formula box type =$A1<>$A2

In this example, the table is sorted by column A. If your table is sorted by a different column, use that column letter in the formula.

Row 1 (e.g. $A1) in this formula refers to the row number of the 'active cell'. You can see the address of the 'active cell' displayed in the Name box to the left of the Formula Bar.

Row 2 (e.g. $A2) refers to the row immediately below the active cell.

The dollar signs mean absolute referencing for the column. No dollar signs mean relative referencing for the rows because we are checking the condition for only column A;

4) Click the Format... button;. This will open the Format Cells dialog;

5) Click the Border tab, select a color, click the Bottom border icon and click OK to close the dialogs.

You should now see a border separating each group.


101 Excel Tips revealed in this book
Microsoft Excel 2000 - 2003

Try out the secrets in my ebook for two months. If they don't save you 10 times the price of the book...

If you're not 100% satisfied...

...simply request a full refund from ClickBank.com

There's absolutely no risk to you.


Non-Excel Tip

Easily Duplicate A File In Windows Explorer (Windows XP)


Here's a useful trick that I discovered only a couple of years ago (it's embarrassing to admit), even though I had been using Windows every day since it first came out.

In the past, when I needed to make a duplicate copy of a file within the same folder, I would open the file and then save it using a different filename.

Then someone showed me how I could simply hold down the CTRL key, drag the file using the left mouse button, drop it in the same folder and a copy of the file will be created.
 

Easily make a copy of a file in the same folder

You can also use Copy and Paste from the menus. You can use the Copy and Paste buttons on the toolbar. You can use keyboard shortcuts, CTRL+C and CTRL+V or you can drag the file using the right mouse button, drop it in the same folder click Copy Here from the pop up menu.



 

My goal: To reach One Million Excel Users

Subscription Information

Spreadsheet Tips From An Excel Addict is available only to subscribers of my newsletter.

If this newsletter was forwarded to you and you would like to get your own copy, please visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber(DOT)com

If you would like to share this newsletter with others...

1) Forward this newsletter by email, but first delete the unsubscribe link at the very bottom so you don't get accidentally unsubscribed

2) Ask your friend/colleague to visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber(DOT)com

3)  Post a link to TheExcelAddict.com in a company newsletter or website
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630