Connect with us

Adv-Excel

Advance conditional formatting excel 2016

Published

on

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.

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.

Adv-Excel

How to use quick analysis tool in excel 2016

Published

on

https://excelxp.com/advanced-excel/excel-2016-advance-custom-number-formatting/

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

Adv-Excel

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

Published

on

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:-

=TODAY()60

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

=TODAY()+60

 

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

Adv-Excel

Excel 2016 Advance custom number formatting

Published

on

Advance number formatting

So, Basically 

What is Advance Number Formatting?

Number formatting in Excel is used to change the way a value appears in a cell range of cells. generally speaking, number formatting does not actually alter the value, it only changes the way we see it. This isn’t true in
all cases, numbers formatted as text are no longer considered as number types and thus it can be suggested that
number formatting can change the value of the cell. A more accurate method of determining a cells true value
is to focus on the value as it appears in the formula bar.

Font color as per value:
To specify number formats that will be applied only if a number meets a condition that you specify, enclose the
a condition in square brackets. The condition consists of a comparison operator and a value. For example,
the following format displays numbers that are less than or equal to 5000 in a Red font and numbers that are greater
than 5000 in a Blue font.
Just select the cell with the number you want to format and hit ctrl+l and go to Number Tab in the dialog box.
Select “custom” category and enter format as [Red][<=5000];[Blue][>5000].

Add Suffix and prefix.

There may be an instance where one has a requirement to prefix and suffix a number with a single character. The benefits of this are that one may yet  use the number in subsequent formulae since this still yields a valid number and not text,

Different Color for Positive, Negative, Zero Values and Text:

Kindly follow the below mention list for some other different type of number formatting.

sample custom number formats

sample custom number formats

 

Continue Reading

Popular tags

Trending