MS OFFICE FUN

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!

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.