headermask image

Business Tools Blog

Format Numbers in Millions using Custom Number Format

You can divide numbers by 1,000,000 to display in millions, but this changes the value of the number.  A better way to display numbers in millions is to use a Custom Number Format.


  • Choose Format > Cells
  • On the Number tab, select Custom from the Category list
  • Paste one of the following formulas in the Type field
    • To display millions with one decimal, paste #.0,,;(#.0,,)
    • To display millions with a dollar sign and one decimal, paste $#.0,,;($#.0,,)
    • To diplay millions with two decimal places, paste #.00,,;(#.00,,)
    • To diplay millions with a dollar sign and with two decimal places, paste $#.00,,;($#.00,,)
    • To diplay millions with three decimal places, paste #.000,,;(#.000,,)
    • To diplay millions with a dollar sign and three decimal places, paste $#.000,,;($#.000,,)

Convert a Number with Currency in Excel into a Spelled/Written Word for use in Mail Merge

It makes me crazy when I have to complete a mail merge and the numbers that are so nicely formated in Excel turn into unformatted numbers in Word’s mail merge.   This can be solved by using the TEXT function, for example, if I wanted 1000 to show up like $1,000.00 in my mail merge document, I would use the below formula (where cell A1has the number that I want to be formatted as currency in my mail merge document.)


The next challenge is when I want to proceed the $1,000.00 with the words “One Thousand Dollars and No Cents”.  I can make this happen using the following formula (where cell A1has the number that you want to be formatted as text in your mail merge document)


But not so fast.  That formula only works after you have added this macro: http://support.microsoft.com/kb/210586

  1. Start Microsoft Excel.
  2. Press ALT+F11 to start the Visual Basic Editor.
  3. On the Insert menu, click Module.
  4. Type the following code into the module sheet.
Function ConvertCurrencyToEnglish (ByVal MyNumber)
   Dim Temp
   Dim Dollars, Cents
   Dim DecimalPlace, Count

   ReDim Place(9) As String
   Place(2) = " Thousand "
   Place(3) = " Million "
   Place(4) = " Billion "
   Place(5) = " Trillion "

   ' Convert MyNumber to a string, trimming extra spaces.
   MyNumber = Trim(Str(MyNumber))

   ' Find decimal place.
   DecimalPlace = InStr(MyNumber, ".")

   ' If we find decimal place...
   If DecimalPlace > 0 Then
      ' Convert cents
      Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
      Cents = ConvertTens(Temp)

      ' Strip off cents from remainder to convert.
      MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
   End If

   Count = 1
   Do While MyNumber <> ""
      ' Convert last 3 digits of MyNumber to English dollars.
      Temp = ConvertHundreds(Right(MyNumber, 3))
      If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
      If Len(MyNumber) > 3 Then
         ' Remove last 3 converted digits from MyNumber.
         MyNumber = Left(MyNumber, Len(MyNumber) - 3)
         MyNumber = ""
      End If
      Count = Count + 1

   ' Clean up dollars.
   Select Case Dollars
      Case ""
         Dollars = "No Dollars"
      Case "One"
         Dollars = "One Dollar"
      Case Else
         Dollars = Dollars & " Dollars"
   End Select

   ' Clean up cents.
   Select Case Cents
      Case ""
         Cents = " And No Cents"
      Case "One"
         Cents = " And One Cent"
      Case Else
         Cents = " And " & Cents & " Cents"
   End Select

   ConvertCurrencyToEnglish = Dollars & Cents
End Function

Private Function ConvertHundreds (ByVal MyNumber)
   Dim Result As String

   ' Exit if there is nothing to convert.
   If Val(MyNumber) = 0 Then Exit Function

   ' Append leading zeros to number.
   MyNumber = Right("000" & MyNumber, 3)

   ' Do we have a hundreds place digit to convert?
   If Left(MyNumber, 1) <> "0" Then
      Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
   End If

   ' Do we have a tens place digit to convert?
   If Mid(MyNumber, 2, 1) <> "0" Then
      Result = Result & ConvertTens(Mid(MyNumber, 2))
      ' If not, then convert the ones place digit.
      Result = Result & ConvertDigit(Mid(MyNumber, 3))
   End If

   ConvertHundreds = Trim(Result)
End Function

Private Function ConvertTens (ByVal MyTens)
   Dim Result As String

   ' Is value between 10 and 19?
   If Val(Left(MyTens, 1)) = 1 Then
      Select Case Val(MyTens)
         Case 10: Result = "Ten"
         Case 11: Result = "Eleven"
         Case 12: Result = "Twelve"
         Case 13: Result = "Thirteen"
         Case 14: Result = "Fourteen"
         Case 15: Result = "Fifteen"
         Case 16: Result = "Sixteen"
         Case 17: Result = "Seventeen"
         Case 18: Result = "Eighteen"
         Case 19: Result = "Nineteen"
         Case Else
      End Select
      ' .. otherwise it's between 20 and 99.
      Select Case Val(Left(MyTens, 1))
         Case 2: Result = "Twenty "
         Case 3: Result = "Thirty "
         Case 4: Result = "Forty "
         Case 5: Result = "Fifty "
         Case 6: Result = "Sixty "
         Case 7: Result = "Seventy "
         Case 8: Result = "Eighty "
         Case 9: Result = "Ninety "
         Case Else
      End Select

      ' Convert ones place digit.
      Result = Result & ConvertDigit(Right(MyTens, 1))
   End If

   ConvertTens = Result
End Function

Private Function ConvertDigit (ByVal MyDigit)
   Select Case Val(MyDigit)
      Case 1: ConvertDigit = "One"
      Case 2: ConvertDigit = "Two"
      Case 3: ConvertDigit = "Three"
      Case 4: ConvertDigit = "Four"
      Case 5: ConvertDigit = "Five"
      Case 6: ConvertDigit = "Six"
      Case 7: ConvertDigit = "Seven"
      Case 8: ConvertDigit = "Eight"
      Case 9: ConvertDigit = "Nine"
      Case Else: ConvertDigit = ""
   End Select
End Function

Yoda Translater

LOVE THIS!!  Convert any statement into Yoda speak


Acting like yoda are you.  Herh herh herh.

Beginning of the Month Function in Excel

Yesterday Ting Liu, Finance Lead at Zayo Bandwidth, asked if Excel had a function to calculate the first day of the month.  Great question …

A prior post showed how to use the EOMONTH function.  EOMONTH returns the last day of the month.

You can also use EOMONTH, to calculate the first day of the month.

  • =EOMONTH(A1,-1)+1
  • In the formula below =EOMONTH(A1,-1), returns the last day of the prior month = 10/31/08
  • Then “+1″ adds 1 day to 10/31/08 = 11/1/08


Note - if you receive a #NAME? error, make sure your addins are turned on.

Excel Tip - changing dates to the End of Month

You’ll often find that you need to show the end of the month in data analysis or when setting up a forecast template.  The EOMONTH command is the right tool for this job.

First, make sure your addins are turned on:

  • Goto Tools
  • Add-Ins…


  • Select Analysis ToolPak and Analysis ToolPak - VBA
  • Click OK


Next type the EOMONTH command:

  • +EOMONTH(B4,1)
  • The formula takes the date in cell B4, adds 1 month and reports the last day in that month.


Next format the date.

  • Here the Mar-01 date is used.  This would be an appropriate date for a forecasting template.


Then, copy the formula to the right:

  • Click the little black box in the lower right corner of the cell that you wish to copy
  • Then, drag that cell to the right.


Each cell now shows the last day of each successive month.

Note - +EOMONTH(B4,0) would have shown the last day of the current month.

Net Installs - graphed

Net Installs are tracked and forecasted on a weekly basis.  We use historical net installs as a rear view mirror and a forecast, to tell us where we are going.

We use a stacked bar to depict net installs.  The top of the bar = Net Installs.  Notice that when Net Installs >$0, the blue bar is split into 2.  The bottom blue bar = the disconnect bar, the other blue bar = the amount that Net Installs is >$0.


Here’s how you build the chart:

1.  Start by setting up the data sheet:

  • In column B, create boxes and color them.  These will be the legend for your chart
  • You can use the EOMONTH command to create the dates in row 17


2.  Create the graph data (in gray below).  The graph requires 4 bars.

  • Blank bar -  Formula =IF(D20<0,D20,”") If the Net Installs<0, then this bar = Net Installs. If Net Installs>0, then this bar has no value
  • Gross Install1 bar - Formula=IF(D23<0,”",D20) If Blank<0, then this bar has no value, if Blank>0, then this bar=Net Installs.
  • Gross Install2 bar - Formula=IF(D23<0,D18,D19) If Blank<0, then this bar = -Gross Installs, if BLANK>0, then this bar=Disconnects
  • Disconnects bar - Formula=+D19.  This bar=Disconnects


3. Create the chart

  • INSERT >> CHART >> Column >> Stacked Column


  • Select the gray area as the range for the chart


  • Create the Title


  • On the Legend tab, uncheck the Show Legend box
  • Click Finish


Your Chart looks like this:

4. Format Chart Area

  • Remove the chart outline
  • Double click on the line on the outside of the chart
  • Select Border=None and Area=None
  • Click OK


5.  Format Plot area

  • Remove the gray shading in the plot area
  • Double click on outline of the plot area
  • Select Border=None and Area=None
  • Click OK


6.  Format the Y axis, so that it displays currency

  • Double click on the Y axis
  • On the Number tab, click Currency
  • Decimal places=0
  • Symbol=$
  • Negative numbers=($1,234)


7.  Format the X Axis

  • Double click on the X axis
  • On the patterns tab, Tick mark labels=Low


8.  Color the bars

  • Double click on the Disconnect bar
  • Change the color to red
  • Click OK


  • Double Click on the Gross Install1 and Gross Install2 Bars and make them blue (you will have to do this separately
  • Click OK


  • Double click on the Blank bar
  • On the Patterns tab, select Border=None and Area=None


Your chart now looks like this:


Go to Businesstoolsblog Shared Files Google Group for a free template.

Converting numbers to words in excel without the dollars and cents

Converting numbers to words without the cents feature can be done with the below VBA code (there are no dollars and cents and no “exactly” in this macro)

  • Type [ALT] + F11 to open VBA editor
  • Select Insert > Module from the toolbar
  • Copy and Paste below text:
  • To use the formula type  ”=English(a2)” or “=English(100)”
Function English(ByVal N) As String

   Const Thousand = 1000@
   Const Million = Thousand * Thousand
   Const Billion = Thousand * Million
   Const Trillion = Thousand * Billion

   If (N = 0@) Then English = "zero": Exit Function

   Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
   Dim Frac As Currency: Frac = Abs(N - Fix(N))
   If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
   Dim AtLeastOne As Integer: AtLeastOne = N >= 1

   If (N >= Trillion) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " trillion"
      N = N - Int(N / Trillion) * Trillion
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= Billion) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion"
      N = N - Int(N / Billion) * Billion
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= Million) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N \ Million) & " million"
      N = N Mod Million
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= Thousand) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N \ Thousand) & " thousand"
      N = N Mod Thousand
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= 1@) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N)
   End If

   If (Frac = 0@) Then
      Buf = Buf & " "
   ElseIf (Int(Frac * 100@) = Frac * 100@) Then
      If AtLeastOne Then Buf = Buf & " and "
      Buf = Buf & Format$(Frac * 100@, "00") & "/100"
      If AtLeastOne Then Buf = Buf & " and "
      Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"
   End If

   English = Buf
End Function

Private Function EnglishDigitGroup(ByVal N As Integer) As String

   Const Hundred = " hundred"
   Const One = "one"
   Const Two = "two"
   Const Three = "three"
   Const Four = "four"
   Const Five = "five"
   Const Six = "six"
   Const Seven = "seven"
   Const Eight = "eight"
   Const Nine = "nine"
   Dim Buf As String: Buf = ""
   Dim Flag As Integer: Flag = False

   Select Case (N \ 100)
      Case 0: Buf = "": Flag = False
      Case 1: Buf = One & Hundred: Flag = True
      Case 2: Buf = Two & Hundred: Flag = True
      Case 3: Buf = Three & Hundred: Flag = True
      Case 4: Buf = Four & Hundred: Flag = True
      Case 5: Buf = Five & Hundred: Flag = True
      Case 6: Buf = Six & Hundred: Flag = True
      Case 7: Buf = Seven & Hundred: Flag = True
      Case 8: Buf = Eight & Hundred: Flag = True
      Case 9: Buf = Nine & Hundred: Flag = True
   End Select

   If (Flag <> False) Then N = N Mod 100
   If (N > 0) Then
      If (Flag <> False) Then Buf = Buf & " "
      EnglishDigitGroup = Buf
      Exit Function
   End If

   Select Case (N \ 10)
      Case 0, 1: Flag = False
      Case 2: Buf = Buf & "twenty": Flag = True
      Case 3: Buf = Buf & "thirty": Flag = True
      Case 4: Buf = Buf & "forty": Flag = True
      Case 5: Buf = Buf & "fifty": Flag = True
      Case 6: Buf = Buf & "sixty": Flag = True
      Case 7: Buf = Buf & "seventy": Flag = True
      Case 8: Buf = Buf & "eighty": Flag = True
      Case 9: Buf = Buf & "ninety": Flag = True
   End Select

   If (Flag <> False) Then N = N Mod 10
   If (N > 0) Then
      If (Flag <> False) Then Buf = Buf & "-"
      EnglishDigitGroup = Buf
      Exit Function
   End If

   Select Case (N)
      Case 0:
      Case 1: Buf = Buf & One
      Case 2: Buf = Buf & Two
      Case 3: Buf = Buf & Three
      Case 4: Buf = Buf & Four
      Case 5: Buf = Buf & Five
      Case 6: Buf = Buf & Six
      Case 7: Buf = Buf & Seven
      Case 8: Buf = Buf & Eight
      Case 9: Buf = Buf & Nine
      Case 10: Buf = Buf & "ten"
      Case 11: Buf = Buf & "eleven"
      Case 12: Buf = Buf & "twelve"
      Case 13: Buf = Buf & "thirteen"
      Case 14: Buf = Buf & "fourteen"
      Case 15: Buf = Buf & "fifteen"
      Case 16: Buf = Buf & "sixteen"
      Case 17: Buf = Buf & "seventeen"
      Case 18: Buf = Buf & "eighteen"
      Case 19: Buf = Buf & "nineteen"
   End Select

   EnglishDigitGroup = Buf

End Function

Use custom cell formats to display numbers in thousands (000’s)

The Cynic just created a report that shows numbers in thousands, without using the old standby “/1000″ or “*.001″.  See below, the value in cell C8 is 1522952.49.  With the custom cell format, the cell displays 1,523.


How to?

Use the “Format Cells” menu to create a “Custom” format as follows: #,###,;(#,###,)

* Don’t accidentally overlook the comma before the semi colon or the comma before the end parenthesis.


I don’t want my excel or PowerPoint chart to plot zeros

Question -

I created an Excel/PowerPoint chart and I don’t want the zeros to be graphed.  I clicked the option to ”not plot empty cells” but I am using a formula to create the data.  Because the cell contains a formula, Excel is not recognizing this as an empty cell and plots it as zero. How can I change the chart so it does not to plot the formula as “zero”?


Add an “if” statement with “NA()” to your formula.

Example - if cell A1 contains zero, use this formula


The cell will now show #N/A.  Excel and Powerpoint charts do not graph #N/A’s

Add manual data labels in PowerPoint graphs/charts

Drew Moldane came up with this trick to automate adding manual labels to PowerPoint graphs/charts. This is great for displaying related data in a straight line on any chart. It also displays words that can’t otherwise be graphed. He won the high five Buddha … since this is a huge time-saver!

Please see below for the step-by-step walkthrough on how to convert manually added labels to linked labels. Please let me know if any of the steps don’t make sense or if you’re following along and I’ve missed something and I’ll correct. As I’m sure you know with these things, what might make perfect sense to me may come across confusing to the reader, so let me know if that is the case …hope this is helpful!



1. Click anywhere within the chart
2. Click “Select Data” from the Design tab
3. Click “Add” in the Legend Entries (Series) box


4. Select the data that you want to appear as labels (this data may need to be manually added to the PPT chart excel sheet and then linked to your earnings supplement)


5. Edit the Horizontal (Category) Axis Labels


6. Select the numeric data for the Axis label range. Traditionally this data would be the dates


7. The “Select Data” box should look like the below screenshot, with CV added as a new Legend Entries and the CV values ($64, $61, etc) as the Horizontal Axis Labels


8. Return to the PPT and click anywhere within the chart

9. From the Layout tab “Current Selection” drop-down box, select the newly created Series and click “Format Selection”


10. In the new Format Data Series box, plot the series on the Secondary axis. Click OK and ignore the effects on the chart

11. From the Layout tab, click AxesSecondary Horizontal AxisShow Left to Right Axis and ignore the effects on the chart


12. From the Layout tab “Current Selection” drop-down box, select Secondary Horizontal (Category) Axis and click “Format Selection”


13. Format Axis Window:
a. Axis Options: select Major/Minor tick mark type: None, and leave the Axis labels: Next to Axis
b. Number: enter the custom Format Code below (this will create the M in the $xxM),uncheck Linked to source and click Add. There are various format codes for %, +/-, etc


c. Fill: No fill
d. Line Color: No line

14. Click Close. With the same Secondary Horizontal (Category) Axis selected, click the Home tab and format the Font Face/Size/Color/Bold to match the current format (most used color scheme settings below)


15. From the Layout tab “Current Selection” drop-down box, select Secondary Vertical (Value) Axis and click “Format Selection”


16. Format Axis Window:

a. Axis Options as shown below. Where the horizontal axis crosses should be the ONLY recurring manual step. This value will have to be updated depending on how the numbers shift. Pay attention to the Min/Max/Major unit on this page, as it will tell you about where the axis will cross and where it will show up on your chart


b. Fill: No fill
c. Line Color: No line
d. Click Close

17. Delete the current data labels / text boxes and voila! You now have labels that update automatically with the rest of your charts. No more nudging to get them to line up properly / remembering to update them! You still need to keep the “Contract Value = “ data label in place

18. Note: there are slight variations that have to be made for different chart types (%ages, etc), but the basic steps are the same