Blog Archive

An Introduction to Data Validation in Excel


Data Validation in Excel lets you control the data that can be entered in a cell. You can restrict the user to enter only a specified range of numbers or text or date.
You can also use data validation functionality to create drop down lists in Excel (which is definitely one of the coolest and most powerful features in Excel)
Accessing Data Validation in Excel
Data Validation in Excel can be accessed through the Data tab in the Ribbon.
Data Validation In Excel
In most cases, there are three situations where you would want to use Data Validation in Excel:
  • When you want to restrict data entry to certain numbers/text/dates. Data that does not meet the validation criteria is not allowed.
  • When you want to inform user whenever out-of-range data is entered. However, all kinds of data entry is allowed.
  • When you guide the user on what data to enter. All kinds of data entries are allowed.
Let's us go through these situations one by one:
When you want to restrict data entry to certain numbers/text/dates
Data validation allows you to specify a condition for data entry in a cell/cells in Excel. Once specified, it does not allow the user to enter anything that is out of that specified range. This feature can be accessed by opening the data validation dialogue box and selecting the Settings option
Data Validation In Excel Data Entry Restricted
In the drop-down list, you can choose the condition you want to apply for a range of cells
  • Any Value – Allows any value to be entered in a cell.
  • Whole Number – Allows only whole numbers to be entered, with additional conditions such as greater/less than, between/not between, equal to/not equal to.
  • Decimal – Allows numbers with decimals to be entered, with additional conditions such as greater/less than, between/not between, equal to/not equal to.
  • List – Creates a drop down by taking a list of items (through range selection or named range).
  • Date – Allows dates (or its number value) to be entered, with additional conditions such as greater/less than, between/not between, equal to/not equal to.
  • Time – Allows time (or its number  value) to be entered, with additional conditions such as greater/less than, between/not between, equal to/not equal to.
  • Text Length – Allows text with the condition on its length.
  • Custom – Allows values that meets the specified criteria. For example, if I use the formula =A1>10, then only numbers greater than 10 are allowed in cell A1.
Whenever you enter any data in a cell that violates the specified condition, it shows an error.
Data Validation in Excel - Error

When you want to inform user whenever out-of-range data is entered (all kinds of data entry is allowed)
While the earlier section was about restricting the user to a specified range while entering data, this section is about warning the user if any out-of-range data is entered. However, the data entry is still allowed. This can be enabled by changing the error message settings.
You can customize the error message by going to the Error Alert tab in the Data Validation dialogue box. There are three options:
  • Stop Error – Displays the stop error and does not let user enter the data which is out of the specified range.
  • Warning Error – Displays the warning error but lets user enter the data which is out of the specified range.
  • Information Error – Displays the information error but lets user enter the data which is out of the specified range.
Data Validation in Excel - Error Messages Types

When You Guide the User on What Data to Enter (all kinds of data entry is allowed)
Suppose you have a list of employees and you want to get their joining date. There could be multiple formats to enter the date (such as 01/01/2014, or 01 Jan, 2014, or 1st Jan 2014). However, for the sake of consistency, it is better to get all the dates in one format. However, it is alright of people enter it in any other format, as capturing the data is of prime importance.
Data Validation In Excel Display Message
Here is how this can be done:
  1. Select any cell and then go to Data tab –> Data Validation
  2. In Data Validation dialogue box, select Input Message tab
  3. Ensure that 'Show input message when cell is selected' check box is selected
  4. In the Input message tab, enter Title (max 32 characters, optional) and Input Message (max 256 characters)
    Data Validation In Excel Display Message Dialogue Box
  5. Click ok. Now whenever you either click on the cell or select it using keyboard, it would display the message.
Caution: If you move the message box from its position, then all the message box will be shown at that position only. So it is safe not to move the message box.
Source: trumpexcel

15 Everyday Excel Shortcuts

Here is a list of excel shortcuts that I use everyday. Therefore I call them everyday excel shortcuts. I stongly recommend evereybody to learn and start to use these shortcuts. They will boost your speed significantly.

Here is my everyday Excel shortcuts:

Cut, Copy, Paste with lightning speed.
Ctrl + X : Cuts selected cell
Ctrl +C : Copies selected cell
Ctrl + V: Pastes the information you previously cut or copied into selected cell

Paste as values
Menu Key + V : If you prefer to paste values only, this is your shortcut. If your keyboard doesn't have a menu key (i.e. laptop keyboard), you can try "Fn + Ctrl + V" in this order.

Select a group of cells
Ctrl + A : When one of a group of cells is selected, this key combo selects the whole group.

Instantly move to end of a row or column of values
Ctrl + Arrow Keys : This shortcut will teleport your selection to last unempty cell in a column or row. 

Select Cells with keyboard
Shift + Arrow Keys : With holding down Shift key, you can select cells by using arrow keys.

Instantly select whole column or row of cells
Ctrl + Shift + Arrow Keys : This one combines previous 2 shortcuts and selects whole rows or columns of data instantly.

Autosum a column or row of values
Ctrl + = : This works as a substitute for the autosum button located on home ribbon near filters. Adds a SUM function into the cell and selects the row or column of cells next to itself. Just hit Enter then.

Apply last used formatting
F4 : When you are going to dye a bunch of seperate cells or chart elements, or add border, etc. Select the first one and apply that formatting. Then select next and hit F4. Be aware that it will only apply last formatting done.

Refresh all pivot tables and charts
Alt + A + R + A : This will refresh all pivot tables and charts at once. Super handy.

Apply filter to columns
Ctrl + Shift + L : No more selecting title row and clicking here and there for adding filter. Select any cell on title row and use this shortcut. I will add filter to all cells on that row.

Access cell properties
Crtl + 1 : This shortcuts does the same thing as right-clicking on a cell and select Format Cells.

Toggle edit mode in a cell
F2 : While on a cell with a formula, you need to double click or go to formula bar to edit contents of that cell. If you directly start inputting, it will replace cell content. This shortcut lets you toggle input mode without touching your mouse.

Mass Input same value or formula into selected cells
Ctrl + Enter : When you need to input same value into a group of cells, like same month number next to a list, select the cells and input your values. Instead of pressing Enter button alone, press Ctrl + Enter. Same value will be inputted into all selected cells at once.

Please feel free to let us know about your everyday excel shortcuts by leaving a comment below. We can all benefit from your experince and add one into our everyday excel shortcuts too.

Source: BeatExcel Picture Source: Technolly

Learn From Your Heart

Image result for life from heart

Learn life from Heart..
It’s been
Played, Stabbed, Cheated
Burned and Broken
But Somehow still works..
Always Remember
life give you the taste of everything
But Never Quit..

Humor: Episodes of my life if I were a Doctor!

Stress is Everywhere!

Stress Everywhere

View this email in your browser

Yes, that's right! Stress is everywhere.
But let me tell you one thing politely: "Happiness is also everywhere, happiness is all around us"

You can't feel it? Cant experience it? The reason is that your thoughts are more focused on the problems than happiness. All it requires a little practice to tune your mind with happiness and pleasure. Once done, believe me, your stress will be reduced to minimum.

Stress Management is an art, not difficult to understand as long as you are committed to learn it. Once you acquire this skill, you can handle almost all kinds of situations of life.
To begin with, here are some articles chosen for you. Happy Reading!

The PHILOSOPHY of Happy Life
The Secret Of Happiness is Hidden
How To Avoid Anxiety
What Stress Actually Does to You and What You Can Do About It
30 Simple Ways to Reduce Stress at Work
10 Principles for Peace of Mind
50 Encouraging Things to Say to Yourself
25+ Small Habits To Lead A More Peaceful Life


How Successful People Stay Calm

The ability to manage your emotions and remain calm under pressure has a direct link to your performance. TalentSmart has conducted research with more than a million people, and we've found that 90% of top performers are skilled at managing their emotions in times of stress in order to remain calm and in control. 

If you follow our newsletter, you've read some startling research summaries that explore the havoc stress can wreak on one's physical and mental health (such as the Yale study, which found that prolonged stress causes degeneration in the area of the brain responsible for self-control). The tricky thing about stress (and the anxiety that comes with it) is that it's an absolutely necessary emotion. Our brains are wired such that it's difficult to take action until we feel at least some level of this emotional state. In fact, performance peaks under the heightened activation that comes with moderate levels of stress. As long as the stress isn't prolonged, it's harmless.
New research from the University of California, Berkeley, reveals an upside to experiencing moderate levels of stress. But it also reinforces how important it is to keep stress under control. The study, led by post-doctoral fellow Elizabeth Kirby, found that the onset of stress entices the brain into growing new cells responsible for improved memory. However, this effect is only seen when stress is intermittent. As soon as the stress continues beyond a few moments into a prolonged state, it suppresses the brain's ability to develop new cells.
"I think intermittent stressful events are probably what keeps the brain more alert, and you perform better when you are alert," Kirby says. For animals, intermittent stress is the bulk of what they experience, in the form of physical threats in their immediate environment. Long ago, this was also the case for humans. As the human brain evolved and increased in complexity, we've developed the ability to worry and perseverate on events, which creates frequent experiences of prolonged stress.
Besides increasing your risk of heart disease, depression, and obesity, stress decreases your cognitive performance. Fortunately, though, unless a lion is chasing you, the bulk of your stress is subjective and under your control. Top performers have well-honed coping strategies that they employ under stressful circumstances. This lowers their stress levels regardless of what's happening in their environment, ensuring that the stress they experience is intermittent and not prolonged.

While I've run across numerous effective strategies that successful people employ when faced with stress, what follows are ten of the best. Some of these strategies may seem obvious, but the real challenge lies in recognizing when you need to use them and having the wherewithal to actually do so in spite of your stress.

They Appreciate What They Have
Taking time to contemplate what you're grateful for isn't merely the "right" thing to do. It also improves your mood, because it reduces the stress hormone cortisol by 23%. Research conducted at the University of California, Davis found that people who worked daily to cultivate an attitude of gratitude experienced improved mood, energy, and physical well-being. It's likely that lower levels of cortisol played a major role in this.

They Avoid Asking "What If?"
"What if?" statements throw fuel on the fire of stress and worry. Things can go in a million different directions, and the more time you spend worrying about the possibilities, the less time you'll spend focusing on taking action that will calm you down and keep your stress under control. Calm people know that asking "what if? will only take them to a place they don't want—or need—to go.

They Stay Positive
Positive thoughts help make stress intermittent by focusing your brain's attention onto something that is completely stress-free. You have to give your wandering brain a little help by consciously selecting something positive to think about. Any positive thought will do to refocus your attention. When things are going well, and your mood is good, this is relatively easy. When things are going poorly, and your mind is flooded with negative thoughts, this can be a challenge. In these moments, think about your day and identify one positive thing that happened, no matter how small. If you can't think of something from the current day, reflect on the previous day or even the previous week. Or perhaps you're looking forward to an exciting event that you can focus your attention on. The point here is that you must have something positive that you're ready to shift your attention to when your thoughts turn negative.

They Disconnect
Given the importance of keeping stress intermittent, it's easy to see how taking regular time off the grid can help keep your stress under control. When you make yourself available to your work 24/7, you expose yourself to a constant barrage of stressors. Forcing yourself offline and even—gulp!—turning off your phone gives your body a break from a constant source of stress. Studies have shown that something as simple as an email break can lower stress levels.
Technology enables constant communication and the expectation that you should be available 24/7. It is extremely difficult to enjoy a stress-free moment outside of work when an email that will change your train of thought and get you thinking (read: stressing) about work can drop onto your phone at any moment. If detaching yourself from work-related communication on weekday evenings is too big a challenge, then how about the weekend? Choose blocks of time where you cut the cord and go offline. You'll be amazed at how refreshing these breaks are and how they reduce stress by putting a mental recharge into your weekly schedule. If you're worried about the negative repercussions of taking this step, first try doing it at times when you're unlikely to be contacted—maybe Sunday morning. As you grow more comfortable with it, and as your coworkers begin to accept the time you spend offline, gradually expand the amount of time you spend away from technology.

They Limit Their Caffeine Intake
Drinking caffeine triggers the release of adrenaline. Adrenaline is the source of the "fight-or-flight" response, a survival mechanism that forces you to stand up and fight or run for the hills when faced with a threat. The fight-or-flight mechanism sidesteps rational thinking in favor of a faster response. This is great when a bear is chasing you, but not so great when you're responding to a curt email. When caffeine puts your brain and body into this hyperaroused state of stress, your emotions overrun your behavior. The stress that caffeine creates is far from intermittent, as its long half-life ensures that it takes its sweet time working its way out of your body.

They Sleep
I've beaten this one to death over the years and can't say enough about the importance of sleep to increasing your emotional intelligence and managing your stress levels. When you sleep, your brain literally recharges, shuffling through the day's memories and storing or discarding them (which causes dreams), so that you wake up alert and clear-headed. Your self-control, attention, and memory are all reduced when you don't get enough—or the right kind—of sleep. Sleep deprivation raises stress hormone levels on its own, even without a stressor present. Stressful projects often make you feel as if you have no time to sleep, but taking the time to get a decent night's sleep is often the one thing keeping you from getting things under control.

They Squash Negative Self-Talk
A big step in managing stress involves stopping negative self-talk in its tracks. The more you ruminate on negative thoughts, the more power you give them. Most of our negative thoughts are just that—thoughts, not facts. When you find yourself believing the negative and pessimistic things your inner voice says, it's time to stop and write them down. Literally stop what you're doing and write down what you're thinking. Once you've taken a moment to slow down the negative momentum of your thoughts, you will be more rational and clear-headed in evaluating their veracity.
You can bet that your statements aren't true any time you use words like "never," "worst," "ever," etc. If your statements still look like facts once they're on paper, take them to a friend or colleague you trust and see if he or she agrees with you. Then the truth will surely come out. When it feels like something always or never happens, this is just your brain's natural threat tendency inflating the perceived frequency or severity of an event. Identifying and labeling your thoughts as thoughts by separating them from the facts will help you escape the cycle of negativity and move toward a positive new outlook.

They Reframe Their Perspective
Stress and worry are fueled by our own skewed perception of events. It's easy to think that unrealistic deadlines, unforgiving bosses, and out-of-control traffic are the reasons we're so stressed all the time. You can't control your circumstances, but you can control how you respond to them. So before you spend too much time dwelling on something, take a minute to put the situation in perspective. If you aren't sure when you need to do this, try looking for clues that your anxiety may not be proportional to the stressor. If you're thinking in broad, sweeping statements such as "Everything is going wrong" or "Nothing will work out," then you need to reframe the situation. A great way to correct this unproductive thought pattern is to list the specific things that actually are going wrong or not working out. Most likely you will come up with just some things—not everything—and the scope of these stressors will look much more limited than it initially appeared.

They Breathe
The easiest way to make stress intermittent lies in something that you have to do everyday anyway: breathing. The practice of being in the moment with your breathing will begin to train your brain to focus solely on the task at hand and get the stress monkey off your back. When you're feeling stressed, take a couple of minutes to focus on your breathing. Close the door, put away all other distractions, and just sit in a chair and breathe. The goal is to spend the entire time focused only on your breathing, which will prevent your mind from wandering. Think about how it feels to breathe in and out. This sounds simple, but it's hard to do for more than a minute or two. It's all right if you get sidetracked by another thought; this is sure to happen at the beginning, and you just need to bring your focus back to your breathing. If staying focused on your breathing proves to be a real struggle, try counting each breath in and out until you get to 20, and then start again from 1. Don't worry if you lose count; you can always just start over.
This task may seem too easy or even a little silly, but you'll be surprised by how calm you feel afterward and how much easier it is to let go of distracting thoughts that otherwise seem to have lodged permanently inside your brain. 

They Use Their Support System
It's tempting, yet entirely ineffective, to attempt tackling everything by yourself. To be calm and productive, you need to recognize your weaknesses and ask for help when you need it. This means tapping into your support system when a situation is challenging enough for you to feel overwhelmed. Everyone has someone at work and/or outside work who is on their team, rooting for them, and ready to help them get the best from a difficult situation. Identify these individuals in your life and make an effort to seek their insight and assistance when you need it. Something as simple as talking about your worries will provide an outlet for your anxiety and stress and supply you with a new perspective on the situation. Most of the time, other people can see a solution that you can't because they are not as emotionally invested in the situation. Asking for help will mitigate your stress and strengthen your relationships with those you rely upon.
Authro: Dr. Travis Bradberry is the award-winning co-author of the #1 bestselling book, Emotional Intelligence 2.0, and the cofounder of TalentSmart,

Let It Go!

When somebody told me that he has failed in his exams, my question is, "Is it a law that you will pass everytime?"

When someone told me that my spouse broke up with me, my question is, "Is it a rule that you will have successful relationships everywhere?"

When somebody asked me why am I in depression, my question is, "Is it compulsory to have confidence all the time?"

When someone cried to me about his huge business loss due to his wrong decision, my question is, "Is it possible that you take all right decisions?"

The fact is our expectation that life has to be perfect/permanent is the biggest reason of our unhappiness.

One has to understand the law of impermanence of nature.

After each sunny day, there has to be a dark night, after each birth there have to be certain deaths, for the full moon to come again it has to pass through no moon. In this imperfection of nature, there is perfection.

So stop taking your failures and bad part of your life soooo personally or intensely. Prepare yourself for one more fight after each fall because even failures cannot be permanent...!

Enjoy life....

Your breath comes to go.
Your thoughts come to go.
Your words come to go.
Your actions come to go.
Your feelings come to go.
Your illnesses come to go.
Your phases come to go.
Your seasons come to go.
You have come to go.

Then why do you hold on to your guilt, anger, unforgiveness, hatred
so so so tightly, when it too has come to go...


8 Ways to Interrupt a Talkative Person

Once they open their mouths, some folks don’t know how to shut them. They seem unable to differentiate monologue from dialogue, dissertation from conversation, minutiae from significant details. 
When you’re in such a “conversation,” you may initially think of yourself as a good listener. However, it’s not long before you realize that you’ve become the captive audience for one who will drone on and on for as long as you allow it to happen.

Giving indirect hints that “enough is enough” usually doesn’t work. Hence, in such situations, you not only have a right to interrupt, you also have an obligation to do so to maintain your sanity.   
So how do you do it without coming across as rude?

Here are eight tips on how to interrupt an incessant talker with as much tact as possible:

Segue into another topic.
“That’s some story. But now I’d like to talk about something more upbeat.”

Be direct.
“I need to interrupt you. I want to tell you what happened to me yesterday.”

Use the person’s name (always an attention-getter), then redirect.
“Jen, I get what you’re saying; it happened to me too.”

Speak about your time situation.
“Jared, I only have another minute to chat.”

Help the person move forward.
“Bob, what’s the bottom line here?”

Give honest feedback(with a light touch, if you can).
“Maria, I haven’t been able to get a word in edgewise. Time to let me talk.”

Make an ending statement.
“I gotta go. I’m already late for an appointment. Talk to you another time.”

When all else fails,
there’s always the bathroom excuse.

If you think it’s rude, crude or insulting to implement these strategies, think again. They’re simply assertive ways to obtain your freedom when you’re being held hostage.

Story: Chicken, Chicken, Chicken

By Junaid Tahir

Colleague Faisal is a person who prefers to stay alone. He is kind of introvert person who does not mingle well with people. On Sunday noon time, he opens his lunch box and eats fried chicken which makes him feel good. On Monday noon, he opens the lunch box and eats fried chicken again. This time he does not like it as much as he did yesterday. On Tuesday, at lunch time, he opens the lunch box and finds fried chicken which he eats but this time I can feel irritation in his mood. Well, next comes the Wednesday, and guess what, he has fried chicken in his lunch box. He is annoyed to see fried chicken on fourth consecutive day but eats it without saying any words of complaint. On Thursday, just before the lunch break I converse:

"Most likely, you have fried chicken today"
"Why don't you ask your wife to give you something else for food which can keep your body healthy"
"I am unmarried, I pack my lunch myself"

"What !!! XoYz%*&%$O %$##  !@#%% "

Flash:This story might look strange but this is how we give mental food to our brains on daily basis. If we co-relate this story with a bad event of the past which we remember again and again, we are giving a special food called "Stress" to our mind on daily basis. Considering the fact that eating fried chicken on daily basis can lead our cholesterol levels towards danger zone causing chronic heart disease, can't we just think with common sense that giving stress to our brain and heart we are seriously impacting our body developing Sugar, High Blood Pressure, Heart blockage, Asthma, Anxiety and God know how many other diseases in the long run !!!

So the choice is yours. Either we keep feeding your brain the germs of negativity and stress  OR develop the power of not looking back and start living in today. Remember the fact that life never stops. People who live in their past, stop living on earth – or simply die soon. So we must exert our energies to bring something positive and fruitful today which can bring happiness in our life or in our loved ones' lives. About the problems we should develop the MGTD attitude: which means Meet them (confess the situation with no fear), Greet them (think positively), Treat them (focus on solution) and finally Defeat them (do practical things for eradication).

Ability, Motivation and Attitude

Image result for ability motivation and attitude\

Is what you are capable of Doing.
Determines what you do.
Determines how well you do it…