Trending February 2024 # Slicer Controlled Interactive Excel Charts # Suggested March 2024 # Top 5 Popular

You are reading the article Slicer Controlled Interactive Excel Charts updated in February 2024 on the website Bellydancehcm.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Slicer Controlled Interactive Excel Charts

Slicers are a great tool for easily filtering PivotTables and Pivot Charts, but what if you want to use a Slicer to control a regular chart like this:

Note: This technique applies to Excel 2010 onwards.

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the Excel Workbook . Note: This is a .xlsx file please ensure your browser doesn’t change the file extension on download.

Creating Slicer Controlled Interactive Excel Charts

The process to set up Slicer controlled interactive Excel charts is easier than you might think, so don’t be put off by the number of steps involved.

Step 1: Get some data and give each column a named range. Here is mine:

Step 2: In a separate column create a list of your named ranges. I’ve formatted mine in an Excel Table in cells G5:G9 called TableRegions:

Step 3: Insert a PivotTable and use the table from step 2 as your source data. Put the ‘Region’ field in the rows area of the PivotTable (note: your PivotTable will list all regions when you first create it):

Tip 1: Remove the Grand Totals from the PivotTable. You don’t need them.

Tip 2: give the first cell in your PivotTable (below the header) a named range. Mine is cell I6 and it’s called ‘trigger’. This just makes it easier to reference later on.

Step 4: Insert a Slicer for the Region field:

Step 5: Create a Dynamic Named Range that picks up the region from the PivotTable that is selected in the Slicer, and returns the appropriate range from the Chart Source Data (as shown in step 1). Mine is called ‘chart_rng’ and you can see it in the Name Manager below:

If you’re familiar with dynamic named ranges then this one might look a little different to what you’re used to.  Let’s take a closer look at the formula:

=IF(

trigger="north",north

,

IF(trigger="south",south

,

IF(trigger="east",east

,

west

)))

Yes, we’re using a nested IF Formula to return a range. The IF function can return a range as opposed to just a single value like you’re probably used to.

Tip: Take particular note of the use of double quotes in the IF formula above.

IF the value in the cell called trigger is north, then return the named range ‘north’, else IF the value in the cell called trigger is south, then return the named range ‘south’, else IF the value in the cell called trigger is east, return the named range ‘east’, else return the named range ‘west’ since there’s no other ranges it could be.

A simpler approach would be to use the INDIRECT function like so:

=INDIRECT(trigger)

But beware of the volatility of the INDIRECT function.

Tip: in the ‘Series Values’ field make sure you keep the sheet name/workbook name part of the reference and only replace the cell references with your named range.

Step 7: Insert a dynamic chart title that changes based on the region selected in the Slicer

Other cool Slicer Tricks

Use Slicer Selection in Formulas

Excel CHOOSE Function Toggle

Thanks

A big thank you to Bob Abrams for the IF function technique to select named ranges.

You're reading Slicer Controlled Interactive Excel Charts

Okx And Manchester City Launch Interactive Avatar Campaign With Top Players

 OKX, a world-leading Web3 technology company and Training Kit Partner of Manchester City has today kicked off a new interactive metaverse avatar campaign with the Club. The new experience at chúng tôi gives fans the opportunity to personalize their own digital avatars in Manchester City training kits and pre-match warm-up apparel with OKX branding.

To mark the kick-off, a hero video featuring top Manchester City players as giant avatars exploring Manchester as fans make their way to the stadium will be debuted in a multi-channel campaign during Manchester City’s home clash with fellow Premier League title contenders Arsenal. The video will announce the new experience and inspire fans to “Play for the City” and get their own personalized digital avatars in the Manchester City x OKX kit.

Haider Rafique, Global Chief Marketing Officer, OKX, said: “The metaverse experiences we’ve been developing with Manchester City represent a world of exciting new opportunities, and your avatar grants you access to these. This campaign is another example of how Manchester City and OKX are delivering on the promise of Web3 to enhance the fan experience and help people get closer to what OKX considers ‘a new way to the internet’.

Gavin Johnson, Media Director at City Football Group, said: “We’re pleased to launch this exciting campaign with OKX and Yahoo today. Bringing together expertise from all three collaborators, we’re able to offer Manchester City fans across the world the ability to connect and engage with the club in a unique and interesting new way and we hope fans enjoy the experience on offer.”

The experience was created by Manchester City and OKX in collaboration with Yahoo, which utilized its premium content creation team and technology to bring the experience to life and spread the word to fans. Working with the Ready Player Me platform to build the bespoke AR and avatar experience, Yahoo created the hero video and will distribute the content through digital Out of Home (DOOH) locations as well as video, display, and native formats across mobile, tablet and TV screens to inspire Manchester City fans to try it for themselves.

Josh Partridge, Head of EMEA at Yahoo, said: “We had a clear brief – to offer fans a fun, engaging, and immersive experience that gives them new ways to interact with the team and players through digital touchpoints in the stadium, the city, and even the virtual world. Using Yahoo’s experience in producing premium quality digital content and distributing it at scale directly to the club’s target audience means we can help forge deeper connections on digital platforms with their fans through new, interactive ways on match day and beyond.”

This is the latest Metaverse experience brought to fans by OKX and Manchester City following the launch of a training course with Rúben Dias and a tactical masterclass with İlkay Gündoğan in the OKX Collective metaverse. May will see fans invited into the metaverse for exclusive experiences with Jack Grealish and Alex Greenwood.

About OKX

OKX is the second biggest global crypto exchange by trading volume and a leading web3 technology company. Trusted by more than 50 million global users, OKX is known for being the fastest and most reliable crypto trading app for investors and professional traders everywhere.

As a top partner of English Premier League champions Manchester City FC, McLaren Formula 1, golfer Ian Poulter, Olympian Scotty James, and F1 driver Daniel Ricciardo, OKX aims to supercharge the fan experience with new financial and engagement opportunities. OKX is also the top partner of the Tribeca Festival as part of an initiative to bring more creators into web3.

Beyond OKX’s exchange, the OKX Wallet is the platform’s latest offering for people looking to explore the world of NFTs and the metaverse while trading GameFi and DeFi tokens.

Disclaimer

Six Charts Show Why No One Is Talking About Climate Change

If you’re like the average American, you are probably worried to some degree about climate change. But odds are you don’t spend a lot of time talking about it. At least that’s what the data show.

Social scientists disagree about why this is the case, but a new report from Yale and George Mason University offers a compelling explanation.

The report suggests there is a spiral of silence around climate change. Few Americans, even those who care about the carbon crisis, chat about climate change with friends or family. No one talks, so no one feels comfortable talking. Silence begets silence begets silence, widening the gap between popular discourse and public opinion, in an ever-descending spiral.

How social scientists imagine a spiral. Scheufle & Moy, 2000

The term spiral of silence was coined in 1974 by German researcher Elizabeth Noelle-Neumann, who was attempting to understand the rise of Hitler. She wanted to know how so many people remained silent as the country slipped into the grip of fascism. She found that when everyone is silent, no one speaks up.

Over the years, scholars have applied the theory to a range of issues. Consistently, they find that people don’t speak up for fear of isolation and reprisal. Share an unpopular opinion, and you risk losing friends and social status.

With climate change, there is some evidence of this. Again and again, surveys show that Americans broadly care about the issue, but few talk about it openly. This is apparent in the findings of the Yale/GMU report.

Most Americans care about global warming.

There are several ways to measure concern. The Yale/GMU report looked at who is interested in climate change and who finds the issue personally relevant. Turns out, it’s a lot of people. This is consistent with other other survey data showing that large majorities of Americans believe the climate is changing and think the government should work to cut greenhouse gases.

Yale/George Mason University

Few Americans say they hear about global warming in the news.

When asked how often they hear about climate change in the news media, fewer than half of Americans said once a month or more. On this measure, public perceptions are roughly in line with reality. News coverage of climate change is sorely lacking. Last year, broadcast news gave climate change less airtime than Deflategate.

Yale/George Mason University

Yale/George Mason University

Few Americans talk about global warming with their friends and family.

This is perhaps the most significant factor. The fear of isolation and reprisal is immediate and personal. People look to friends and family to judge which opinions are safe to share. Not many Americans hear their peers talking about global warming, and few are likely to bring it up in conversation — a fact that hasn’t changed much over the past several years.

Yale/George Mason University

Yale/George Mason University

Here is the smoking gun.

Among people who say they are interested in global warming and say it’s personally important, less than half say they hear about it in the media on a monthly basis. About a quarter say they hear about it from their friends once or more a month. And — here’s where the spiral of silence comes in — less than half talk about it with their peers.

Yale/George Mason University

Why is this happening?

Part of the problem is that news coverage hardly measures up to the scale of the problem. It’s a well-established finding in social science that Americans take their cues about which issues are important based on the volume of coverage.

But that doesn’t explain why so few people talk about climate with their family and friends. The rise of social media may be a factor. Political polarization may be as well.

“Our sense is that most Americans don’t raise the issue in conversation because they want to avoid heated or drawn out conversations with people who may hold more extreme views than they do — one way or the other,” said Ed Maibach, professor of communications at George Mason University and lead author of the report.

Dunlap, McCright, & Yarosh, 2024

Dunlap, McCright, & Yarosh, 2024

“It’s a bit like the old adage that it’s impolite to discuss sex, religion, or politics at the dinner table, because it is likely to make someone uncomfortable,” said Maibach. “Our spring 2024 survey found that 56 percent of Americans see global warming as a political issue… that’s more than half the people at any given dinner table.”

But, while climate change has become more polarized, few Americans hold extreme views. Most linger somewhere in the middle — concerned, cautious, disengaged or doubtful, as summed up in previous research from Yale and George Mason University.

Yale/George Mason University

“Most Americans are convinced that climate change is happening, but they don’t hold strong feelings about it. Moreover, they mistakenly think that lots of Americans hold strong feelings about it — either alarmed feelings or dismissive feelings,” said Maibach.

Not everyone agrees that what we are seeing is a spiral of silence. David Karpf, professor of communications at George Washington University, offered a competing view.

“I believe the main driver here is that climate change is a quiet crisis. The problem isn’t that it is politicized, or that it isn’t a priority,” said Karpf. “The problem is (1) that there isn’t a set of routinized events that bring it to into public conversation, and (2) we don’t have a clear notion of what we can each do about it.”

“That’s not really a spiral of silence,” he said. “If there were some set of events that acted as a drumbeat and pushed us to either pay attention to climate change or actively ignore it, then I think we’d see a lot more public conversation about climate.”

Compare climate to gun violence, a problem that regularly consumes the news in the wake of mass shootings. These are moments, explained Karpf, “that seize our collective consciousness.” With climate, such flash points routinely go unrecognized. Reporters fail to connect heat waves, droughts and severe storms to global warming, despite a wealth of evidence.

So, while most Americans believe climate change is a problem and worry about it, few are passionate about the issue. What’s needed is more coverage and more conversation about global warming. It’s going to take some work to serve up climate at the dinner table.

Jeremy Deaton writes for Nexus Media, a syndicated newswire covering climate, energy, policy, art and culture. You can follow him at @deaton_jeremy.

How To Use Boolean In Excel Vba With Excel Template?

VBA Boolean Operation

In Excel when we compare two cell contents or numbers by applying equal sign between them, we get output in TRUE or FALSE format. Which means values which we are comparing may be equal or may not be equal. In a similar manner, we have Boolean in VBA Excel. Boolean also gives the output in the form of TRUE or FALSE. Boolean is a form of data type which can only contain either TRUE or FALSE. When we give some input to Boolean and if the test becomes TRUE then we get an answer as TRUE or we get FALSE.

How to Use Boolean in VBA Excel?

Let’s see the examples of Boolean in Excel VBA.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VBA Boolean Excel Template here – VBA Boolean Excel Template

Example #1 – VBA Boolean

Let’s see a very simple example where we will see how to apply Boolean while comparing some.

Step 2: Now in the opened module, write the sub category of VBA Boolean. We can choose to write any name of subprocedure here.

Code:

Sub

VBABoolean1()

End Sub

Step 3: Now define a Dim with any name, let’ say an A and assign the variable A as Boolean as shown below.

Sub

VBABoolean1()

Dim

A

As Boolean

End Sub

Step 4: Let’s consider two numbers, 1 and 2. And now we will test with the help of Boolean whether 2 is greater than 1 or not. So in the next line, write a mathematical expression of 1<2 under defined variable A.

Code:

Sub

VBABoolean1()

Dim

A

As Boolean

A = 1 < 2

End Sub

Step 5: Now assign a message box to variable A to see what outcome will appear when we run the code.

Code:

Sub

VBABoolean1()

Dim

A

As Boolean

A = 1 < 2 MsgBox A

End Sub

Step 7: If we change the sign as 1 is greater than 2 as shown below. What would we get?

Code:

Sub

VBABoolean1()

Dim

A

As Boolean

MsgBox A

End Sub

Step 8: To test this, again run the code. We will see, Boolean has given FALSE as 1 cannot be greater than 2.

Example #2 – VBA Boolean

In this example, we will test if Boolean works for text or not. To apply this, we need a module.

Step 1: Open a new Module and give it a subcategory in the name of VBA Boolean or any name as per your choice.

Sub

VBABoolean2()

End Sub

Step 2: Define a variable A and assign a Boolean function to it.

Code:

Sub

VBABoolean2()

Dim

A

As Boolean

End Sub

Step 3: Now assign a text to defined variable A. Let’s say that text is VBA Boolean. And it should be under inverted commas.

Code:

Sub

VBABoolean2()

Dim

A

As Boolean

A = "VBA  Boolean"

End Sub

Step 4: At last, give that variable A in a message box to see the output as shown below.

Code:

Sub

VBABoolean2()

Dim

A

As Boolean

A = "VBA Boolean" MsgBox A

End Sub

Step 5: Once done, run the code. We will get an error message as “Run-time error 12 – Type Mismatch” which means that Boolean doesn’t support input as Text.

Example #3 – VBA Boolean

In this example, we will see, if Boolean works for a number without any comparison.

Step 1: Open a new module and give it a subcategory of VBA Boolean as shown below.

Code:

Sub

VBABoolean3()

End Sub

Step 2: Now define a Dim A variable as Boolean as shown below.

Code:

Sub

VBABoolean3()

Dim

A

As Boolean

End Sub

Step 3: As discussed above, we will give the variable A a number. Let’s consider that number is 10.

Code:

Sub

VBABoolean3()

Dim

A

As Boolean

A = 10

End Sub

Step 4: After that, select the function msgbox and assign it to variable A. This will help us print the value with the help of Boolean.

Code:

Sub

VBABoolean3()

Dim

A

As Boolean

A = 10 MsgBox A

End Sub

Step 5: Now run the code. We will get the message with the message as TRUE.

Step 6: Now let’s change that value to 0 from 10.

Code:

Sub

VBABoolean3()

Dim

A

As Boolean

A = 0 MsgBox A

End Sub

Example #4 – VBA Boolean

Step 1: Now, open a new module and write the subcategory of VBA Boolean as shown below.

Code:

Sub

VBABoolean4()

End Sub

Step 2: Now define 2 variable with any name as per your choice. Here, we have selected A and B as Integer. Which means both will store numeric values.

Code:

Sub

VBABoolean4()

Dim

A

As Integer

Dim

B

As Integer

End Sub

Step 3: Now assign any values to variable A and B. Here we have chosen number 1 and 2 for variable A and B as shown below.

Code:

Sub

VBABoolean4()

Dim

A

As Integer

Dim

B

As Integer

A = 1 B = 2

End Sub

Step 4: As stated above, we will use the If-Else loop. Now open the If-End If loop where we will write the criteria.

Code:

Sub

VBABoolean4()

Dim

A

As Integer

Dim

B

As Integer

A = 1 B = 2 If

End If

End Sub

Step 5: Now write the code, If A is less than equal to B then show us the message as TRUE, else show us the message as FALSE.

Code:

Sub

VBABoolean4()

Dim

A

As Integer

Dim

B

As Integer

A = 1 B = 2

If

A <= B

Then

MsgBox

True

Else

MsgBox

False

End If

End Sub

Step 6: Now compile the above code step-by-step and then run if no error found. We will see, the message box has the message as TRUE which means value stored in variable A (which is 1) is less than the value stored in variable B (which is 2).

Pros of VBA Boolean

It is quite useful when we are want to implement the process flow following TRUE and FALSE for each iteration.

Conditional comparison with the help of any kind of loop can easily be implemented.

Cons of VBA Boolean

Only numbers can be used in Boolean. It will show the error if used for text as seen in example-2.

Things to Remember

Using Boolean with any loop will give users a better output. And comparison can be done in various ways.

Boolean is a kind of cell comparison formula used in excel, but it only compares the numerical or mathematical values.

Always save the file in macro enable format to retain the written code to be used in the future.

Recommended Articles

This is a guide to VBA Boolean. Here we discuss how to use Boolean in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

How To Create Arraylist In Excel Vba With Excel Template?

Excel VBA ArrayList

Data structures are used to store a series of data in programming languages. It binds to the memory rather than address. An ArrayList is one of the data structures in excel. Comparing to normal arrays in excel ArrayList is dynamic. Therefore, no initial declaration of size is needed. ArrayList is not a part of VBA it is associated with an external library which can be used with VBA.

ArrayList can be defined as a list of a nearby memory location. Where the values are retrieved using the index numbers. The list starts from an index number ‘0’, the first element will be inserted into the ‘0’ index and rest is followed by 1, 2, 3, etc. ArrayList offers plenty of built-in operations, sorting, adding, removing, reversing, etc. are some among them.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Adding the Library

To use the ArrayList into the VBA it needs to include the library ‘mscorlib.dll’ which comes with .NET framework.

It will lead you to a window with a list of different libraries which supports in VBA and Excel. Scroll down to find the ‘dll’. Tick mark to confirm the selection then press ‘OK’ button.

Now the library is included in your VBA code and it will support different methods associated with an ArrayList.

How to Create VBA ArrayList in Excel?

You can download this VBA ArrayList Excel Template here – VBA ArrayList Excel Template

Excel VBA ArrayList – Example #1

How to Add Values to the ArrayList using VBA?

ArrayList act as a list where we can add values. This will automatically store in the different portions starting from 0,1, 2, etc. The values can add or insert to the ArrayList using the add method.

In this example, you will learn how to add a list of values into an ArrayList. Follow the below steps to add ArrayList using VBA Code in excel.

Step 1: To add a list of values to an ArrayList create a function arraylist1.

Code:

Private Sub

arraylist1()

End Sub

Step 2: Now we want to include the ArrayList into the function as an object where a list is declared as an ArrayList.

Code:

Private Sub

arraylist1()

Dim

alist

As ArrayList

End Sub

Step 3: Since this is an object to use it, you have to create an instance of the ArrayList. Set a new instance for this object.

Code:

Private Sub

arraylist1()

Dim

alist

As ArrayList

Set

alist =

New

ArrayList

End Sub

Step 4: Now using the ‘Add’ property of an ArrayList adds the values to the ArrayList. Where the list is added into the index values in an order 0,1,2,3 etc.

Code:

Private Sub

arraylist1()

Dim

alist

As ArrayList

Set

alist =

New

ArrayList alist.Add "192"

'index(0)

alist.Add "168"

'index(1)

alist.Add "1"

'index(2)

alist.Add "240"

'index(3)

End Sub

Step 5: To check whether the values got added into the list, let’s print the array values using a message box. To print the values each index is printed since the values are stored in these partitions.

Code:

Private Sub

arraylist1()

Dim

alist

As ArrayList

Set

alist =

New

ArrayList alist.Add "192"

'index(0)

alist.Add "168"

'index(1)

alist.Add "1"

'index(2)

alist.Add "240"

'index(3)

MsgBox ("\" & alist(0) & "." & alist(1) & "." & alist(2) & "." & alist(3))

End Sub

Step 6: Press F5 or run button to run the program and the values will be printed as below. Here an IP address is stored in the ArrayList and while printing the values extra notations are concatenated to form the IP address in a proper format.

Automation error in VBA

It is a common error happens while running an ArrayList. An automation error may encounter ‘Run-time Error ‘-2146232576 (80131700) Automation Error’

Excel VBA ArrayList – Example #2

Sorting ArrayList Using VBA Code 

ArrayList supports different functions like sorting, reversing, etc. this help to sort the values inserted into an ArrayList. Once you add a list into the ArrayList it is possible to reverse the inserted list.

Follow the below steps to sort the ArrayList using VBA Code:

Step 1: Create a function called arraysort1 to perform the sorting within the inserted values into an ArrayList.

Code:

Sub

arraysort1()

End Sub

Step 2: Declare an object ‘arraysort’ of the ArrayList. Use this object to add and sort the values within the ArrayList.

Code:

Sub

arraysort1()

Dim

arraysort

As ArrayList

End Sub

Step 3: Similar to the first example need to create a new instance of the declared object. Set this instance as a new ArrayList.

Code:

Sub

arraysort1()

Dim

arraysort

As ArrayList

Set

arraysort =

New

ArrayList

End Sub

Step 4: Now using the ‘Add’ method insert the elements to the ArrayList. Which is not possessing any order on values. Randomly inserted some values into the list.

Code:

Sub

arraysort1()

Dim

arraysort

As ArrayList

Set

arraysort =

New

ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45"

End Sub

Code:

Sub

arraysort1()

Dim

arraysort

As ArrayList

Set

arraysort =

New

ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45" MsgBox (arraysort(0) & vbCrLf & arraysort(1) _ & vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _ & vbCrLf & arraysort(4) & vbCrLf & arraysort(5))

End Sub

Step 6: Press F5 on the keyboard or run button on the code window to run the program to print the ArrayList. The ArrayList is printed in the same order as it is inserted since we use the index numbers in its correct order.

Step 7: Now to this list apply the sort property of the ArrayList. Use the sort method to sort the inserted list. The sort property will sort the list of values in ascending order by default.

Code:

Sub

arraysort1()

Dim

arraysort

As ArrayList

Set

arraysort =

New

ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45" arraysort.Sort MsgBox (arraysort(0) & vbCrLf & arraysort(1) _ & vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _ & vbCrLf & arraysort(4) & vbCrLf & arraysort(5))

End Sub

Step 8: Hit F5 or Run button under VBE to run this code, Where the values are sorted and printed in order from smallest value to largest.

Excel VBA ArrayList – Example #3

Reversing the ArrayList using VBA Code

When you want to reverse the order of inserted values in an ArrayList reverse method is available. This will reverse the order of the list from its current order. Now we have already sorted the ArrayList in the previous example, which is in ascending order.

Let’s try to reverse the sorted array to make it descending order. Use the reverse method of ArrayList to do this.

Code:

Sub

arraysort2()

Dim

arraysort

As ArrayList

Set

arraysort =

New

ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45" arraysort.Sort arraysort.Reverse MsgBox (arraysort(0) & vbCrLf & arraysort(1) _ & vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _ & vbCrLf & arraysort(4) & vbCrLf & arraysort(5))

End Sub

After applying the reverse method, the ArrayList will become in descending order and use the message box to print the reversed array. The sorted list is changed from large value to small value.

Things to Remember

ArrayList is dynamic in nature; it does not require re-initialization.

Different built-in methods are associated with ArrayList.

Compared to the array, ArrayList is easy to use in Excel VBA.

The supporting .NET libraries should be included in the VBA to work with ArrayList.

ArrayList is a continuing memory location which identified using index values.

Recommended Articles

This is a guide to VBA ArrayList. Here we discuss how to create ArrayList in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

Data Validation In Excel (Examples)

Data Validation in Excel

Data validation is a feature in MS Excel used to control what a user can enter in a cell of an Excel sheet. For restricts the entries in a sheet, such as a date range or whole numbers only. We can even create dropdowns, which save unnecessary space and shows the values in a single cell. Also, we can create a customized message which will appear user inserts any incorrect value or an incorrect format.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

For example, A user can specify a meeting between 9:00 AM and 6:00 PM.

Locate in MS Excel

Select Data Validation from the toolbar under the Data Tab:

How to Create Data Validation Rule in Excel?

Let us understand the working of Data validation in Excel with some examples.

You can download this Data Validation Excel Template here – Data Validation Excel Template

Example #1

Execute the Below mentioned steps for the creation of the data validation rule in Excel:

Step 1: Select B2 Cell.

Step 3: A data validation Pop-Up will open:

Step 3.2: Select the Whole number, then Some more required options will be enabled.

Step 3.4: All settings will apply to the selected cell.

Now Enter 1 in cell B2- It will allow users to enter any whole number from 0 to 10.

Now Enter 11 in cell B2- It will throw by default error.

Example #2 – Set Input Message and Error Alert

We can restrict a user to enter a limited text.

Step 1: Select B5 Cell from Example 2 Sheet.

Step 3: Select equal to from the data list drop-down.

Step 4: Enter your desired password length (as an example 11).

On the Selection of Cell, an Input message will be displayed.

Enter a text which has a text length of 11. It will be selected successfully.

Now enter an invalid password. It will throw the described error message.

Example #3 – Custom option for e-mail address validation

Step 2: Select Custom in Allow drop-down.

Step 3: Write a formula for selecting only value if it has ‘@‘.

On the Selection of Cell, the Input message will display.

Enter a valid e-mail id – It will be selected successfully.

Now enter any invalid e-mail Id- It will throw a described error Message.

Valuation criteria of Excel data validation Settings

Any Value – To provide any data.

Whole Number – Enter only real numbers.

Decimal – Enter only decimal numbers.

List – Pick data from the drop-down list.

Date – Accept the only date.

Time – Enter only time.

Text Length – Fixed the length of the text.

Custom – Enter a custom formula.

Conditions in the data list in the Valuation criteria settings

Between, not between, equal to, not equal to, greater than, less than, greater than, or equal to, less than, or equal to.

Some more conditions of Excel data validation Settings

If a user wants to ignore the blank, there is a checkbox called Ignore.

W a user selects between, then a user needs to select the Minimum and Maximum values for the cell(s).

If a user wants to apply these changes to all other cells with the same setting, then there is a checkbox that needs to mark, and it will apply to the remaining cells in the sheet.

When to Use Data Validation in Excel?

This is the best option when a user wishes to share a sheet with another user and needs the data entered to be correct and consistent.

Restrict entries to predefined items in a list.

Restrict numbers outside a specified range.

It Restricts dates outside a certain time frame.

Restrict times outside a certain time frame.

Limit the number of text characters.

Validation of the data, which is available on other sheets or workbooks.

A user can customize the error alert; it can be anything user-defined.

Things to Remember About Data Validation in Excel

If a user wants to create a data validation rule in the workbook for Excel web apps or Excel services, then he needs to make an Excel data validation rule on the desktop first.

If one user sender a workbook to another, then first, a user needs to make sure that the workbook is unlocking; otherwise, second, a user is never able to access the workbook cells to fill value.

Always remember that there is no error in a formula like #REF! or #DIV/0!

Anyone who can create a data validation rule in the filled cell MS Excel will not detect it.

How to Remove the Excel Data Validation Rule?

Users can only modify or delete the Excel data validation rule if the inherited sheet is unprotected. If it is password protected, contact the owner of the workbook. Only he can help to unprotect the workbook as there is no way to recover or lose the password option in MS Excel.

Recommended Articles

This has been a guide to Data Validation in Excel. Here we discuss how to create Data Validation in Excel, along with Excel examples and a downloadable Excel template. You may also look at these useful functions in Excel –

Update the detailed information about Slicer Controlled Interactive Excel Charts on the Bellydancehcm.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!