Data Validation – Dynamic (Excel 2010) – From Table

Data Validation – Dynamic (Excel 2010) – From Table

Data validation is used so users enter the expected data only, available in a drop down. But with static data validation, if we want to add more values in our list, we will first have to update the source list and then update the source range in Data Validation.  

But there is a way to handle this issue. For example, in column A we have a few product’s listed which we want in another cell to appear in drop down. And as per our need when we add/modify another product in the list (Column A), it start appearing in the drop down.

1- For this, first create a table of the list, for this select the data A1:A9 and press Ctrl T.

2- The default table name is Table1, if you want to change then have your cursor somewhere in the table and go to design tab, under table name the name is appearing and can be changed.

3- Select all record of the table excluding the header (A2:A9) then go to Formulas tab > Define Name and give a name to highlighted range i.e. product

4- In the Refers to tab refer the table like in our case =Table1

5- Select Cell C1 and go to Data > Data Validation. In the Data Validation dialog box, settings tab, choose list under Allow: and in the source box write equal sign and the named range. Like in our example it would be =Product. Press Ok.

6- The drop down list is available in cell C1

7- Now important thing is that we can add a new value to our list, simple go to cell A10 and type another product and press ok or move out of cell and check in C1 cell, the new product is in the list.

Please refer to below snapshots for better idea.

Excel Fill Series

Excel Fill Series

For example, we want to create a list of dates from 01/01/2015 to 12/31/2015. We can type the date 01/01/2015 in cell A1 and then drag it down until we reach 12/31/2015. It will take some time as it’s difficult to stop the mouse at exactly the right cell.

So an option is to use the fill series option. The series dialog box has many options and some of them get automatically selected as the selected cell in our case has a date. If the value in the first cell is a number then we’ll see the ‘Date unit’ options are greyed out:

As we want to fill dates down column A, so we need columns selected.

--Type as Excel detected is Date.

--It has also assumed that we want to increase series by days, as opposed to weekdays, months or years.

--And the step value or increment value is one day at a time.

--So I need to enter the stop value which is last date of my series which in our case is 12/31/2015.

Click ok and you will have a list of dates from January 1 2015 to December 31, 2015 in a few clicks.

Note: (I learned it from http://www.myonlinetraininghub.com/)

Pictograph in Excel

Pictograph in Excel

We can paste a picture from clipboard onto a chart.

For this click on your picture and copy it, suppose it’s in MS Word so copy from there then click on graph series and paste it there (ctrl v).

See the below snap, first graph is standard and the other is with Pic.

We can do some more formatting as well. For that right click on series then Format Data series. Then go to ‘Fill’ Option. There are 3 options. Stretch is default, Stack and Stack & Scale to.

With Stack option the chart will look like below.

With Stack & scale option, we can enter input.

There are other options in the Fill tab like Gradient Fill, Picture or texture fill, which can be tried to make bars look more eye catching.

Using “Playbill” font & Excel formula for incell charts

Using “Playbill” font & Excel formula for incell charts

With REPT formula and symbol (“|”), we can make in-cell charts. And to look them just like a chart, use the playbill font. We can change the theme color as well to make it more prominent/attractive.

For example, our data is in range D3:D7. Use the below formula. Here we are repeating the symbol and the number of times is the cell value divided by the maximum number of the list/range and multiplied by hundred. It’s easy & simple, just try it.

=REPT("|",D3/MAX($D$3:$D$7)*100)

Excel formula: COUNTIFS() with date range in cell reference

Excel formula: COUNTIFS() with date range in cell reference

Suppose we have soma data with dates and we want to count the rows, falling between two dates.  I had to do the same thing today and faced an issue. Though most of the people would be already aware of it but for a person like me, it was a new thing so thought to share with everyone.

If we do the date comparison this way “>=G1”, here G1 is the cell where start date is written then this check will not work. Though, it will work, if we specify a date itself instead of cell reference. For cell reference, the comparison operator will be in double quotes and with & we will add the cell reference. Please see the complete syntax. Here the B:B is the column range/criteria range having dates and G1 & H1 are start & end dates.

=COUNTIFS(B:B,">="&G1,B:B,"<="&H1)