MS OFFICE FUN

There is no built-in formula in excel, you have to add a vb script and permanently save it with your MS. Excel's installation as Add-In.

Function English(ByVal N As Currency) 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 & " exactly"
   ElseIf (Int(Frac * 100@) = Frac * 100@) Then
       If AtLeastOne Then Buf = Buf & " and "
       Buf = Buf & Format$(Frac * 100@, "00") & "/100"
   Else
       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 & " "
   Else
       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 & "-"
   Else
       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 .

=English (100)
Result: "one hundred exactly"

=English (125)
Result: "one hundred twenty-five exactly"

=English (125.3)
Result: "one hundred twenty-five and 30/100"

sample

Advanced Filters 

The tutorial explains the basics of Excel Advanced Filter and how to use step-by-step guide walks you through how to apply filter.

Create a Drop-Down List by Using Data Validation Microsoft Excel 2016

You can use Data Validation to create a dropdown list of options in a cell. However, the list font can't be changed, nor can the number of visible rows, which has a maximum of eight. Also, Data Validation doesn't have an AutoComplete feature, which finds matching items in the list as you start to type.

ComboBox Excel ActiveX Control. You can link the Combo Box to a Worksheet cell. Make Excel entry easier with autocomplete and bigger fonts.

Pivot tables are one of Excel's most powerful features.

A pivot table is a tool that allows you to explore large sets of data interactively. Once you create a pivot table, you can quickly transform huge numbers of rows and columns into a meaningful, nicely formatted report.

Part-1

Part-2

Learn about using data Tables in Microsoft Excel
you can create a data table to quickly try out different values for formulas. Excel is great for storing lists of data and you may need to manage that data by sorting it into order. How to create a data table and drop-down menus and Filtering Data Tables

Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false.

Understanding This chapter teaches you how to deal with some common formula errors in Excel






#DIV/0!:  Excel displays the #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.

#NAME?:  Names are wrongly/incorrectly spelled in a formula or cell.

#NUM!:  Occurs when Excel encounters an invalid number.

#NULL!:  Basically this occurs due to missing arguments in the formulas or named ranges.

#N/A:  Indicates that a value is not available to a formula.

#VALUE!:  Excel displays the #VALUE! error when a formula has the wrong type of argument.

######:  When your cell contains this error code, the column isn't wide enough to display the value.

#REF!:  Excel displays the #REF! error when a formula refers to a cell that is not valid.

Excel’s Flash Fill is a time-saving feature which fills in data for you when a pattern is evident


Start Flash Fill manually. If Flash Fill is turned on but doesn't start automatically when you type data that matches a pattern, you can try starting it manually by clicking Data > Flash Fill, or by pressing Ctrl+E. Check out this training course if you'd like to know more about using Flash Fill.

During this Excel 2016 tutorial, learn how to use text function and with other functions in Excel 2016.

PROPER, UPPER, & LOWER
Use Excel's PROPER, UPPER, or LOWER functions to correct or convert text data to capital and small letters as needed

CONCATENATE
CONCATENATE function allows you to combine text from different cells into one cell.

TRIM 
TRIM strips extra spaces from text, leaving only single spaces between words and no space characters at the start or end of the text.

Len
The Microsoft Excel LEN function returns the length of the specified string.

Left
The text from which to extract characters. Use the LEFT function when you want to extract characters starting at the left side of text.

Right
The text from which to extract characters on the right. Use the RIGHT function when you want to extract characters starting at the right side of text.

FIND / SEARCH
FIND function is case sensitive while the SEARCH function is case-insensitive

The CHOOSE function in Excel allows you to 'Choose' a "value' depending on a specific input.

1.Randomly CHOOSE names (where value is "text" (in quotes))
2.The CHOOSE function can help show which fiscal quarter a month is in, or show the total sales for a selected store number

Try a formula construction like (with lookup value in cell B1):
=CHOOSE(B1,A1,A2,A3,A4,A5,A6,A7)

Watch this video to see how to use the Excel CHOOSE function, and select items in a list.

COUNT 
Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range

COUNTA
Use the COUNTA function to count cells that contain numbers, text, logical values, error values, and empty text "". COUNTA does not count empty cells. COUNTA will also count items.

COUNTBLANK
Use the COUNTBLANK function to count blank cells in a range.

COUNTIF
Excel COUNTIF function counts the number of cells in a range, that meets a given criteria. If you need to count Number of Filled Cell in a range, you can use,

COUNTIFS
Count cells that match multiple criteria
If we want to know how many boys test scores were greater than 50%, we could use the following

Welcome to the YouTube home of the "Learn MS Office" varieties of Tips, Tricks and More Tutorials that center on the effective and efficient use of Microsoft Office.

Hopefully these videos will help you to become better with MSOfficeFun so you can save time at work,

If you like what you see be sure to Subscribe and SHARE this with others!

The spreadsheet model

• Worksheets and their contents
• Differentiating workbooks and worksheets

Navigating the interface

• The Ribbon
• Customizing the interface
• Managing large spreadsheets by zooming
• Identifying worksheet tabs by color
• Changing worksheet views

Basic data editing

• Modifying cell contents and formats
• Inserting data with AutoFill
• Knowing when to use Paste Special

SUM

The AutoSum dropdown. Here the range of cells is selected manually before selecting the option.

SUMIF

The Excel SUMIF function returns summation of selected range of cells if it satisfies the given criteria. You can optionally select from which range you want to sum the value. Go through the below example to clearly understand it.

SUMIFS

The Excel SUMIFS function is used when we want to apply more than one range which satisfies set of criteria and returns the corresponding values.

when exporting data from database into Excel sheet, dates export as a text entries When this occurs, it might be necessary to convert the text dates to Excel dates so that you can perform calculations & sorting in Excel.


In this tip, you will learn how to quickly convert "text dates" to "Microsoft Excel dates" using a long-standing feature in Excel.

Hopefully these videos will help you to become better with MS Office so you can save time at work,

This tutorial provides several practical examples of when and how to use the VLookup and HLookup functions in Microsoft Excel

In this video, we show you See how to use Vlookup Formula in MS Excel 2010.
whenever you need them! For anyone who wants to master Microsoft Excel

Simple way about how to Automatically insert the serial numbers in Excel when new data is entered in relevant fields by using the IF formula. Watch the Video to know more about adding serial numbers automatically.

Microsoft Excel Courses Excel Intermediate Excel Advanced Excel Pivot Tables and Dashboards Essential Excel Formulas and Functions Advanced Excel Formulas and Functions Creating & Designing Excel Workbooks Automating Excel with Macros

As well as being the world’s premier spreadsheet software, Excel offers powerful tools for application development, data analysis, business reporting and self-service business intelligence.