Connect with us


How to use flash fill in excel 2016 and most used features



Flash Fill

How to use Flash Fill In 2016.

One of the most useful and timesaving tools Microsoft has to introduce in office 2013is flash fill in Excel. flash fill takes tedious tasks, often performance manually, and executes them automatically. you don’t have to insert a single formula -Excel learns and recognizes a pattern of what you are trying to do and fills in the data for you in a flash!

Now we will discuss various uses of flash fill.

Merging Columns:- You have two columns of names [first & last name ] and you want to merge them into ok one single column with the full name. to use flash fill just filling in the column with the full name the way you want it to appear.

If you continue to fill in more name flash fill will automatically kick in and fill the full column with names for you. if you want to activate Flash fill yourself, fill in the first cell hit enter then press the keyboard shortcut CTRL+E.

Splitting columns in Excel 2016

You can be used flash fill if you want to split contents from one cell into multiple cells. perhaps you have a column with a full name but you want columns with first name and last name are you have a column with prices where you want to split numbers and currencies into two different columns.

Clearing Data:- Flash fill can also be used if you want to clean or modify data perhaps you have a column with city names were some of them begin with space? just type in a city name the way it is supposed to be written and flash fill will remove all Space.

Perhaps you have a full test string and you only want to pick out a certain word for instance city or PIN Code from the full address? just write the name of the city or PIN Code you want to select and flash fill with picking out the rest for you.

Reverse Order:- Suppose you have maintained your database first name then the last name now you want this in reverse order means you want the last name first then the first name. just type the last name then first name and flash fill will pick out the rest for you.

How Formatting Text and Numbers Work?

Quite often you have data in your spreadsheet that you want to format in some other way. perhaps you have a column with a name but they are all in a proper case. Again, just start typing the names the way you want them to appear and flash fill will format the names the way you want them.

Date & Time:- you may change the formatting of date and time used flash fill. you may split day month & year from date, hour minutes & seconds from time or merge day, month & year to date.

How to Turn Flash Fill On?

Flash fill is on by default and automatically fills your date when it senses a pattern, However, if it’s not working as expected hear’s how you can check if flash fill is turned on.

Click On File Tab ⇒ Option

Click advance and make sure the Automatically Flash Fill Box Is Checked.

Flash Fill doesn’t take into account changes in date. say for example that you have used flash fill to combine the first name and last name into a single full name column. if you make any changes to the names in the original columns, they will not be reflected in the combined column.


Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.


How to use quick analysis tool in excel 2016



How to use a Quick Analysis tool?

The Quick Analysis tool as the name suggested allows you to select data on your sheet and then quickly analyses it using the shortcut options available to us from the tool.

Once you select your data, if the option is applicable, you will see the Quick Analysis tool appearing in the bottom right of your selection.whrn you click on this tool the following screen will appear. you can see from this screen that there are 5 possible options.

The formatting option, allows you to apply conditional formatting options without having to go to the HOME
tab and choosing Conditional formatting. Very quickly you can apply the options of data bars, color scales and
so Note that the last icon on the formatting view us clear format, So if you apply a format like Colour scale
and then decide you do not like it you can clear it very easily. Also, if you just hover over these icons you will
l See a preview before you click, as when you click you have applied the format and would need to clear format
to delete it.
Press Ctrl+Q to display the Quick Analysis Lens tools.

The charts option allows you to create a chart from the selected data. Again if you hover over the chart types you will see the chart before deciding on the one to choose.

The Totals option, allows you to create a totals line at the bottom of your selected data. For example, if you choose
Sum when your data is highlighted it would give you a new line of data under your existing data with the
sum of each of your columns. Click Average and you will get a new line with the average total at the end of
the data and so on. The arrow pointing to the right shows that there are more options on this window, click on
the arrow and you will see more total options.

The tables option, allow you to convert the selected data into a table format. Once in a table, you can use all the tools in the tools in the table tools tab. it also allows you to convert the data into a pivot table.

The sparkline option allows you to create a line, column or Win-loss sparkline in the cells to the right of your selected data. Once the sparkline is created further option can be accessed through the sparkline tab.

Continue Reading


What is data validation in excel how to use data validation?



What is data validation in excel, how to use data validation?

What is Data Validation?

Data Validation is a great way to keep your users information about possible values in a cell and guide them to select something appropriate. Here we will discuss advanced data validation techniques that can help you are modeling a complex worksheet.

Dynamic List


Dynamic data validation lists is an advanced use of data validation list in a way that it updates it the underlying data grows or shrinks. For Example:- If you have a product list and have them listed in a data validation list. Now if you add or delete products in that list, data validation list will automatically update for new item added or deleted to the list.

Suppose you have created a sales invoice having the following fields:

You have created product list in another sheet, now you want to create a product list in the description field of the invoice and if you add or delete a product in your product list it will automatically update. We may create a dynamic list of the various process here we will follow the simple one. Follow the below process to create dynamic Data Validation list:
At first, you need to create the list of Products
Then select the list and hit shortcut Ctrl+T to convert the range into Table. You can have this done by going to
Insert Tab ⇒ Tables group click Tables button. Activate My Table Has Header checkbox.

Select the table and click on Design tab and go to properties group. Change the name of a table to the products list.
Make sure there are no spaces in the name. Press Enter to confirm the name. Now your table is named ‘products list’.

Hit Ctrl+F3 to bring Names manager dialogue box up. Click the New button. A new dialogue box to define a new name will appear. In the name field write product Click once in the ‘refers to’ field to make it active and revert back
to the table to make the selection. Select the “Products” column of the table completely. You will notice that reference is something like this: – products list [products]. This is a structured reference. This referencing system is available only with Excel Tables. ‘products list’ is the name of the table and [Products] represent the ‘Products’ of the table.

Structured reference is the real fuel in real fuel in making Excel table truly dynamic. As you can see while defining name we didn’t mention any static range like A2: A8 (which it should be in our case) instead it is [columnname]. With this address whatever goes in that column now will get counted as is independent of rows because they are not specified.

Go to Invoice sheet and select the range where you want the product list [Description field in our example and
click on Data Tab ⇒ Data Validation ⇒ Setting ⇒ List. Put = sign in the source box and hit F3 to select the name range.

Your work is done here now you may edit the list and check that it will work as a dynamic range. Depended List: In Excel, you can set up drop-down lists that are dependent on the selection made in another cell. In this example, you select a Zone in column C and only the States in that Zone is in the drop-down list in column D.

Here you need to create three tables one for Zone list and other two for East zone state details and North zone
state details.
Then create name range as Zone, East, and West for the following tables.
Now go to Zone field and create a dropdown list on Zone Table and again go to the state field and create a dropdown list as shown below.


Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Custom Data Validation:- Here we will discuss some formulae in custom Data Validation.

Prohibit Weekend Dates: Prevent entry of dates that fall on Saturday or Sunday. The WEEKDAY function returns the weekday number for the date entered. and values of 1 (Sunday) and 7 ( Saturday) are not allowed.

  • Select cell F2
  • Choose Date ⇒ Data Validation
  • Choose Allow: Custom
  • For the formula enter.

=AND (WEEKDAY(F2)<>1, WEEKDAY (B2)<>7)






Restricted to a Dynamic Date range:- Instead of setting a specific date range, you can create a dynamic range. for example, a formula is used in the date box, to allow only dates within a specific number of days from the current date.

  • Select the cell where you want the data validation applied
  • On the Ribbon, Click the Data tab, then click Data Validation
  • From the Allow drop-down, select Data
  • In the Data drop-down, select one of the operations, such as Greater than, or Between
  • Depending on the operator you selected, start date and/or End date boxes will appear
  • Type a Formula in the start date and/or end date boxes, using the TODAY function.

To set the earliest start date allowed as 60 days prior to the current date:-


To set the latest start date allowed as 60 days after the current date:



Limit the Total:- Prevent the entry of a value that will cause a range to exceed a set total. in this example, the total budget cannot exceed ₹25000. the budget amount is cell C3: C7 and the formula checks the total in those cells

  • Select cells C3: C7
  • Choose Data ⇒ Data Validation 
  • Choose Allow: Custom
  • For the formula, use SUM to total in the range $C$3:$C$7. The result must be less than or equal to ₹25000.

=SUM ($C$3:$C$7)  <= 25000

Follow the below table for some other uses of custom Data Validation:

Requirement  Formula
Accepting Text only =ISTEXT(A1)
Accepting number only =ISnumber(A1)
Accepting a large value than the previous cell =A2>A1
Accepting non- Duplicating entries =COUNTIF($A$1:$C$20,A1)=1
Accepting Text that begins with A =LEFT(A1)=” a”
Accepting Text that begins with A and is exactly five characters Long =COUNTIF(A1,”A????”)=1
The cell that contains a product code name (B5) always begins with the standard prefix of ID- and is at least 10 characters long. =AND(LEFT(B5,3) =”ID-“,LEN(B5)>9)
Require Unique 5- Digit Number =AND(ISNUMBER(A2),LEN(A2)=5,COUNTIF($A$2:$A$10,A2)<=1)


Continue Reading


Advance conditional formatting excel 2016



Advance conditional formatting excel 2016

Advance conditional formatting.

Conditional formatting is a powerful feature in excel. by using conditional formatting you can tell excel to highlight portions of your data that meet any given condition. for example: highlighting the top 5 customers, below average performing employees etc. while anyone can set up simple conditional formatting rules, an advanced excel user can do a lot more. they can combine formulas with conditional formatting to highlight data that means almost any condition. conditional formatting allows you to automatically apply formatting_ such as colors icons, and data bars_ to one or more cells based on the cell value. You can apply multiple conditional formatting rules to a cell range or worksheet, allowing you to visualize different trends and pattern in your data.

Highlighting Weekends

When you design an automated calendar you don’t need to color the weekends yourself. With the conditional formatting tool, you can automatically change the colors of weekends by basing the format on the WEEKDAY function.

In this case, you must lock the reference of the columns so that the conditional format will work correctly in the other cells in this table.

Highlight the salesman name of highest sales.

Highlight Product Name If Expired

Highlight the name of employees getting salary 12000 to 25000

Advance uses of icons set:-Suppose you have a database with fields project name and project completion status. now want to insert icon set 3 Flags with the following conditions.

  • Red Flag complete less than 50%
  • Yellow Flag if complete between to 50% to 80%
  • Green Flag if complete more than 80%

Here you need to manage rule and edit the percentage value as per requirements. Activate show icon only checkbox if you want to see only icons.

Continue Reading

Popular tags