How do you change the worksheet tab color in Microsoft Excel?

 Excel 2003 Users


If you are wondering how you change the worksheet tabs color in an Excel spreadsheet then the title of this post might be a little deceiving. Unfortunately, this feature is not currently available in Microsoft Excel. It would certainly be helpful to many users to be able to color-code your worksheet tabs. For some unknown reason, Microsoft hasn't implemented this feature yet although I hear it has been available in 1-2-3 and Quattro Pro for quite a while. Still, that's not enough to make me quit using Excel and switching to another program. 

Excel 2007 Users

You can easily change the tab color in Microsoft Excel 2007 by going to the home tab, clicking on the Cells section, going to Format and then hit "Change tab color." Easy. 

How do you capitalize letters in a name with Excel VBA macro?

Is it possible to have a VBA macro capitalize the first letters in someone's name? For example, if a name is John Stoneburner and a user only enters "john stoneburner' in a text box could it would automatically capitalize it to be John Stoneburner. With Excel, anything is possible! There are two options you could try:

Dim myString as String
myString = Application.WorksheetFunction.Proper(TextBox1.Value)

Or use this option:

Range("A1") = UCase(Left(Range("A1"), 1)) & Mid(Range("A1"), 2)

Honeymoon vacation planner spreadsheet template suggestions?

	Budget
Hotels	---
Airfare	---
Car Rentals	---
Activities	---
Meals	---
Petrol	---
Misc.	---
Total	$0.00

 

I am working on changing the Excel spreadsheet I used to help plan my honeymoon vacation into a downloadable user template. Here are the major categories I have listed to help users plan their budgets and activities. Are there any categories that I am missing that you would like to see added? I'm sure I will be able to add more unexpected items after I go on my actual honeymoon.

Booked	Item	Cost	Budget	Balance
YES	Hotels	$1,511.83	---	#VALUE!
YES	Airfare	$600.80	---	#VALUE!
SELECT	Activities	$874.00	---	#VALUE!
SELECT	Car Rental	$279.13	---	#VALUE!
SELECT	Meals		---	#VALUE!
SELECT	Petrol		---	#VALUE!
SELECT	Misc.			$0.00
	Total	$3,265.76	$0.00	#VALUE!

Looking for an Excel Football Schedule Spreadsheet Template?

Fall is upon us and you know what that means- it's football season! Whether you're following your son in middle school, your high school, a college team, or a fantasy NFL team, an Excel spreadsheet is a good way to keep track of your favorite team's stats. Here is a spreadsheet I put together as a quick example. Column A is for the week number of the season. Column B is to list your favorite team. Column C is for their schedule, who the opponent is every week. Next, D is your team's score and E is your opponent's score. Up to this point all the columns have required manual data entry. The next columns are all formulas.

 Column F is the result column, a Win, a Loss, or a Draw. I used a simple IF formula: 

=IF(D2>E2, "W", IF(D2

Next, I want to know what the current winning or losing streak is. I insert a column with the following formula starting in cell G2:

=1+(IF(F3=F2,G2,0))

Now in the streak column I can use a simple concatenate: 

=F2&G2 

Now, for a complete football stats page I want to see what my team's overall record is. To do that I use this formula:

=COUNTIF(F:F, "W")&"-"&COUNTIF(F:F, “L")&"-"&COUNTIF(F:F, "D")

 

Winning percentage is another easy formula and useful stat.

=COUNTIF(F2:F11, "W")/COUNTA(F2:F11)

 

The remaining statistics are all simple average, max, and min formulas. Take control of your fantasy football league with the football schedule Excel spreadsheet! What other stats or features would you like to see on my football template spreadsheet?


Download Football Schedule Spreadsheet.xls now!

How do you get Excel to recognize milliseconds?

I recently needed to import some elapsed time data from a scientific instrument into Microsoft Excel for in order to compute some complex calculations. The scientific instrument provided elapsed time in the following format:

01:02:03:123 where 01 = hours, 02 = minutes, 03 = seconds and 123 = milliseconds.

At first glance, Excel does not seem to recognize milliseconds. However, you just have to know what Excel is looking for. In this case, Excel expects to see milliseconds as:

01:02:03.123

Notice the difference? It is tiny yet important (a "." instead of a ":").

 

Another method to recognize milliseconds in Excel may be to try this formula:

=REPLACE(A1,9,1,".")+0 where your time values are in column A. Then, format the converted values as hh:mm:ss.000

How do you create bullet points in Microsoft Excel 2007?

How can you do bullet points in Excel similar to those in Microsoft Word? There are at least six methods that I know of:

1. =CHAR(149)

2. Alt+0149

3. Alt+249

4. The lower case letter n in Wingdings font

5. You can also import them from Word or use the character map (char.exe)

6. VBA code line: ActiveCell.Value = "•"

There may be others that I haven't thought of yet. Any suggestions?