Excel: ​Are you an analyst? Use these 25 shortcuts & tricks to boost your productivity

Analyst's life is busy. We have to gather data, clean it up, analyze it, dig the stories buried in it, present them, convince our bosses about the truth, gather more evidence, run tests, simulations or scenarios, share more insights, grab a cup of coffee and start all over again with a different problem.
So today let me share with you 25 shortcuts, productivity hacks and tricks to help you be even more awesome.

Write faster formulas

Writing formulas is a big part of analyst life. Use below tricks to reduce the time you spend writing Excel formulas.
  1. Auto fill a series of Excel formulas with corner click - demoUse F2 key to edit any cell with formulas. This will put the cursor right the end of the formula.
  2. Exploit intellisense:  Whenever you are typing a formula, Excel shows a list of possible functions / names that start with the same few letters you have already typed. Once the list is small enough, you can use arrow keys (up / down) to pick the function or name you want and press TAB to let Excel type the thing for you. This will dramatically speed up your formula writing process.
  3. Corner click to auto-fill: Once you have a formula, chances are you want to fill down that formula for rest of the table / range. To do this, just select the formula cell, double-click at bottom-right corner of selection. Bingo, Excel will auto-fill the formula all the way down (as long as there are values in adjacent columns).
  4. CTRL+Enter to type same formula in a bunch of cells: If you want to have same formula applied to a bunch of different cells, just select them all and type the formula. This will place the formula in top-left cell of the selection. Now, instead of pressing enter, press Ctrl+Enter. Excel will place the formula (and adjust any relative references) in all the cells.
  5. Debug portions of the formula with F9 key: When working with long formulas, often we come across situations when the result doesn't make any sense. You can debug portions of such long formulas using F9 key. Just select the formula portion and press F9 to see the corresponding result.
Related: More formula shortcuts

Pivot table productivity tricks

Pivot tables are a big part of analyst's life. Use below tricks to work faster with Pivot tables.
  1. Use ALT+N V to insert a pivot table quickly. Or you can use the old school shortcut (from Excel 2003 days) – ALT + D P
  2. Double click any value to drill down: When looking at pivot tables, if you want to know which records correspond to a particular total, just double click on the number. This will show a new sheet with only data for that number.
  3. Rearrange your pivot table items by drag & drop: Want to see a particular product name on top? Want to see the department list in a certain order? No problem. Simple select the items and drag and drop them in any order you want. This will re-arrange the pivot report the way you want.
  4. Sort quickly with right click: Sort your pivot reports by simply right clicking on the value field and choosing sort option.

Related: Pivot table tricks to make you a star at work

User interaction hacks

A good analyst must create user friendly workbooks because a great deal of the job involves communicating with users. This is where ideas like data validation, form controls & slicers come handy. Here are few hacks to deal with such things faster.
  1. Multi-select slicer items by dragging: To select multiple items on a slicer, simply drag from first item to last. If the items you want to select are not together, hold down CTRL key and click on one slicer button at a time.
    Drag to multi-select slicer items - Excel tip - demo

  2. Set up form control linked cells faster: To set up the linked cell for a form control, simply select the control, click on formula bar, press = and click on the cell you want to link. Done!
  3. Cut and paste: When setting up a complex workbook model, usually all the calculations are done in a separate worksheet tab. To speed up the process of setting up user interaction elements (such as slicers or form controls), first set them up in the calculation sheet. Once everything is working as per plan, just cut and paste them to the output sheet.
  4. Alt + Down arrow to pick items from a validation / filter list: Use ALT + down arrow key to pick items from a data validation drop down or filter cells.
  5. Quickly clear filters with these shortcuts: On a table or list, use CTRL + Shift + L to clear the filters or toggle them. On a slicer use ALT + C to clear the filter (ie select all).

Charting done efficiently

A good chart may get you that hike. So it's no wonder we, analysts spend a lot of time working on charts.
Here are few tricks to work with charts efficiently.
  1. Use arrow keys to select individual chart elements: When working with charts, we have to select a chart element (bars, columns, titles, axes, legend etc.) before doing anything to it. To quickly select a chart element, simply activate the chart and use arrow keys.
    Use arrow keys to select chart elements - Excel tip

  2. Adjust chart's source data with drag and drop: If you want to change a chart's source data, simply use drag and drop. Select the chart series (for ex: in a line chart, select the line you want to change). This will highlight the source data range. Now using mouse pointer simply drag and drop the highlighted box to wherever you want. Done!
  3. Use the select objects tool: When working with multiple charts, often you may want to adjust settings for all in one go. Wouldn't it be great if you can draw a box containing all charts and everything gets selected, a la Power Point (or image editing software)? Well, you can do that in Excel too. Simply activate select objects tool from Home > Find & Select > Select Objects.
    In fact, I suggest adding this tool to quick access toolbar (right click on the select objects tool and choose Add to quick access toolbar) so that you can fire it up whenever you want.select-objects-tool

  4. Link chart title etc. to cell value: Default chart titles can be lame and boring. Create awesome titles (subtitles, captions etc. too) by using formulas. Then link them to chart title by using this simple trick. Select the title (or any other element), click on formula bar, press = and click on the cell containing your new title. Bingo, your chart now sports a context-sensitive, smart title. (Related: smart chart titles – how to?)
  5. Add data to charts with copy paste: Got a chart with sales trend for 3 products and want to add product 4 to it? Simple. Copy the data, select the chart, press CTRL+V. Tell Excel how you want this new data to be pasted and your chart is updated instantly.

Formatting / Presentation tricks

It's no good if you are productive. Your presentation skills are equally (if not more) important.
Let's see some powerful formatting / presentation tricks.
  1. Format anything with CTRL + 1: Simple, select the cell / chart / image / drawing shape you want to format. Press Ctrl 1. Format as you want.
  2. Use alignment tools, you must. Hmmm:
    If your report has multiple charts (or shapes), then align them all, you must. Having perfect alignment doesn't mean you waste several minutes nudging each chart in to right position. Simple select them all (using the select objects tool, of course) and fire up alignment tools from either Page Layout or Format ribbon. Align and space objects in a consistent way.
  3. Repeat last actions with F4 key: Let's say you are changing font color for various chart elements. You can do this step once on something like vertical axis, then select other items and simply press F4. This will repeat your last action (ie font change) on the new selection.
  4. Format once, paint many times: Use format painter tool from Home ribbon to quickly apply format settings (including conditional formats) from one range to many. Works awesomely and saves you several precious minutes of formatting time.
  5. Add frequently used items to quick access toolbar: Formatting tends to be a time consuming activity. To reduce the amount of clicks, mouse travel & un-necessary ribbon navigation, simply add all the frequently used formatting options to quick access toolbar.
  6. Turn-off grid lines: Get rid of them grid lines to instantly give your workbooks a professional & clean look. You can do this by going to View ribbon. While at it, consider turning-off formula bar & headings too if you find them intrusive

Microsoft Excel: Filter as you type
Subcontractor Evaluation Checklist
Creating Spark-Line in MS Excel
Using VLOOKUP Tool for Microsoft Excel
Microsoft Excel: Top 10 Formulas for Aspiring Analysts
EXCEL: Using * in Excel Formulas
How to Improve Your Microsoft Excel Skills
Important Excel Keyboard Short Cuts
Excel Tip: Replace formulas with values Quickly
Creating Simple Check List in Excel
Learn Top 10 Excel Features

Become a Master of Google Search in Mere Minutes

​You Deserve to Be Happy - ​By Brian Tracy

Achieving your own happiness is the best measure of how well you are living your life and enjoying your relationships. You can learn how to be happier and more fulfilled in everything you do.

Everyone is Different

Happiness in life is like a smorgasbord. If 100 people went to a smorgasbord
​(Buffet offering wide range of food) ​
and each put food on their plate in the quantity and mix that each felt would be most pleasing to him, every plate would be different. Even a husband and wife would go up to the
smorgasbord and come back with plates that looked completely different. Happiness is the same way. Each person requires a particular combination of those ingredients to feel the very best about himself or herself.
Listen to Your Heart

And your mix is changing continually. If you went to the same smorgasbord every day for a year, you probably would come back with a different plateful of food each time. Each day-sometimes each hour-only you can tell what it takes to make you happy. Therefore, the only way to judge whether a job, a relationship, an investment, or any decision, is right for you is to get in touch with your feelings and listen to your heart.

Be True to Yourself

You're true to yourself only when you follow your inner light, when you listen to what Ralph Waldo Emerson called the "still, small voice within." You're being the very best person you can be only when you have the courage and the fortitude to allow your definition of happiness, whatever it may be, to be the guiding light of every part of your life.

There Are No Limits

A very important point on the subject of happiness is whether or not you feel that you "deserve" to be happy.
​ ​
Accept the notion that you deserve all the happiness you can honestly attain through the application of your talents and abilities. The more you like and respect yourself, the more deserving you will feel of the good things in life. And the more deserving you feel, the more likely you will attain and hold on to the happiness you are working toward.

Make Happiness Your Key Measure
You should make happiness the organizing principle of your life. Compare every possible action and decision you make against your standard of happiness to see whether that action would make you happier or unhappier. Soon, you will discover that almost all of the problems in your life come from choices that you have made - or are currently making - that do not contribute to your happiness.

Pay the Price

Of course, there are countless times when you will have to do little things that don't make you happy along the way toward those larger things that make you very happy indeed. We call this paying the price of success in advance. You must pay your dues. Sometimes these interim steps don't make you happy directly, but the happiness you achieve from attaining your goal will be so great that it totally overwhelms the temporary inconveniences and dissatisfactions you have to endure in order to get there.

Action Exercises

Here are three steps you can take immediately to put these ideas into action.

First, accept that you deserve all the joy and happiness you can possibly achieve through your own efforts.
Second, make your own happiness the chief organizing principle of your life and judge everything against that standard.
Third, be willing to work hard and pay the price for the satisfaction and rewards you
desire. Always go the extra mile and your success will be assured

7 Bad Habits Holding You Back!
7 Cardinal Rules For Life
7 Qualities of Great Spirits
7 Steps For Melting Anger On The Spot
7 Tips for Peace of Mind
7 Ways To Maintain A Positive Attitude - A Good Article
9 Rules for Staying Positive
9 Tips To Make Effective Decisions

Fantastic Answers on Poison, Fear, Envy, Anger & Hate

What is Poison?
He replied with a beautiful answer 

- Anything which is more than our necessity is Poison. It may be Power, Wealth, Hunger, Ego, Greed, Laziness, Love, Ambition, Hate or anything

What is fear.....?
Non acceptance of uncertainty.
If we accept that uncertainty, it becomes adventure...!

What is envy ?
Non acceptance of good in others
If we accept that good, it becomes inspiration...!

What is Anger?
Non acceptance of things which are beyond our control.
If we accept, it becomes tolerance...!

What is hatred?
Non acceptance of person as he is. If we accept person unconditionally, it becomes love...!

11 Project Management Tips for Setting and Managing Expectations

Project Management Tips
Keeping projects on track -- especially in the face of constant change requests and additions -- is a project manager's greatest challenge. And when project managers don't properly set expectations from the start -- with senior management, the client or their team -- and don't have a strategy in place for dealing with scope creep or last-minute requests or changes, deadlines can suffer.

So how can project managers ensure that everyone shares the same goals and expectations? CIO.com asked dozens of IT executives, project managers and project management experts to find out. Check out their top 11 suggestions on how to successfully set, manage and adjust expectations to ensure deadlines are met and tempers don't flare.

1- Get involved early (during the planning process). "Expectations, especially for IT projects, tend to get set by senior management without consideration to the details it takes to deliver," says Diane C. Buckley-Altwies, CEO, Core Performance Concepts, a provider of project management courseware and training.
Therefore, it is critical to "take the appropriate amount of time to work with senior management during the planning process to define key measureable objectives that everyone can agree [upon]," Buckley-Altwies says. That way, "when issues arise, the project manager [can] always come back to those objectives and ask management if the issue affects [his] ability to deliver on the objectives."
"If possible, be part of the sales process so that you know what is expected and discussed from the very beginning," says Abie McCauley, digital project manager, Nebo Agency, a web design and interactive marketing firm. "The more you know about the goals of the project, the better you can guide your team to success."

2- Involve all stakeholders, especially IT. "Whenever I have a project that is clearly IT heavy, I bring all the [IT] guys in the room, from the CIO to a junior programmer, to make sure everyone has input on the timelines and expectations," says Richard Bexon, COO, Namu Travel Group. "This way the guys laying the code or installing a new server 100 percent understand what the project is about and have buy-in."  

3- Have a clear project scope with sign-off -- and set priorities. "Making sure that you have a very clear scope when starting a project is essential, as people will always try to creep things into the scope," says Bexon.
"Scope containment is the Achilles heel to project management," says Bill Gaffney, corporate development, Applied Robotics. "Even minor changes can accumulate and cause scope creep," he says.
To avoid or at least manage scope creep, "the scope of the project and the strategy(s) to accomplish that scope/strategy must be clearly defined," he says. Similarly, "the potential risks and mitigation actions must be identified and addressed. [And] changes from the baseline plan must be rigorously evaluated for impact to schedule and cost, no matter how insignificant they may appear at the time."
In addition, "ensure that all projects and enhancements are assigned an LOE, ROI and priority level," says Samira Mahjoub Tapia, cofounder and head of product at Chippmunk, a savings search engine. And make sure all members of the team understand the priorities.
"Having the team review this list weekly forces us to keep our focus and ensures that precious development time isn't spent on projects that don't move [us] forward towards our goals," Mahjoub Tapia says. "It also allows the dev team to see upcoming projects and feel like empowered members of the team."

4- Be realistic. "From the very early days of a project, it is critical to set realistic completion dates [to] ensure accurate forecasting," says Hernan Clarke, CEO, 4Sight Technologies, a company that specializes in developing and supporting software for project management.
"Meeting deadlines and keeping projects on time and on budget requires commitment from all parties involved. This means a representative from the client, management and project team should always be involved in the process of setting expectations and target completion dates to ensure agreement and buy-in from every perspective," Clarke says.

5- Make sure everyone (including senior management) understands his or her role and responsibilities. "Set realistic expectations with everyone from the very beginning about what their roles and jobs are for the project," says McCauley. "Make sure each team member has a clear vision of how they fit in the overall success of the project, so that they understand how all the many aspects tie together."
"PMs must be clear about the time and activity they will require of team members and executives," says Robert Kelly, managing partner, Kelly Projects Solutions. "This may be even more important with respect to executives as their calendars often fill up weeks in advance. Everyone must know exactly what meetings will be held, when and the purpose of those meetings."
Above all, make sure everyone has a good "understanding [of] goals, timelines, KPI's, etc.," says Eugene Slobodetsky, project manager, Lyons Consulting Group. "Managing expectations is important, and in order to do that, there needs to be agreement across all parties on what the expectations are." Ask yourself, "What are the goals of the engagement, the expected (and realistic) timelines and measures for success? Many times, the mistake is in not communicating these clearly, and having both sides 'singing to a different tune,'" he says.

6- Make sure team members communicate with each other. "Assumptions can kill your team, work and deadlines," says Jim Shulkin, vice president of marketing at Daptiv, which develops project portfolio management (PPM) software.
"Developing a communication plan that errs on the side of over-communication [is] critical to the success of projects and work objectives," Shulkin says. A successful communications plan "usually outlines scope of work, owners of each task, deadline against each task and status updates," and who to notify when issues arise.

7- Try to identify potential pain points. "There are a lot of variables going into every design project and even with infinite preparation, every aspect still wouldn't be known," explains Dan Bodenstein COO, Boulder Engineering Studio. "Being clear about this upfront sets an expectation for the unknown. However, trying to identify points along the development cycle where surprises are likely at the outset helps relations with the client, positions them to respond quickly and can keep projects closer to schedule."

8- Set up calendar reminders for milestones. To keep projects and team members on track, "set up calendar notifications, alerting the team when steps should be completed and due dates," says Alexis Chrzanowski, account executive, Sales & Marketing, Innovative Facility Solutions. "Setting up these notifications keeps everyone's mind in the right place and right stage of [the] project.

9- Have an escalation strategy. "Nothing ever goes exactly to plan," says Joseph Czarnecki, vice president, Product and Sales Support, IPS Learning. "To keep expectations [in] check, work with management to develop clear guidance on what needs escalating and what does not need escalating because not everything will," he says. "Then discuss that guidance with your team regarding what and when issues need to be escalated and how to escalate, giv[ing] your team the accountability (and responsibility) to solve what they can at their level."

10- Hold regular status meetings -- and set up reminders to ensure everyone attends. "We have regular client bi-weekly reviews either via a conference call or a face-to-face meeting," says Paula Tompkins, founder and CEO of ChannelNet, which delivers digital customer acquisition, retention and conquest services.

For these meetings to be productive, however, "it's important to be open, honest and inclusive," she says. "Direct communication between all concerned parties is the best and most efficient way to deliver a quality project, based on our 29 years of experience," Tompkins says.
"We make sure that we have daily scrum meetings [Agile] with all stakeholders so that everyone knows where everyone is," says Bexon. "This allows us to communicate and overcome any roadblocks or issues that exist and to work as a team."

11- Don't be afraid to communicate bad news -- and adjust expectations. "If anything happens that will affect the deadline of your project, let all stakeholders know immediately," says Mike Vitale, CTO at TalkPoint, which specializes in browser-based audio and video webcasting. "Nothing good ever comes from trying to hide a problem. Always be clear and concise when communicating issues, and get to the point quickly."
"All projects will have roadblocks," says Clarke. The key is "to disclose the problems promptly and as honestly as possible," he says. "More often than not, the client and management will appreciate being brought into the loop earlier rather than later."

And when presenting bad news, be sure to also present options for dealing with roadblocks to minimize delays and frustration.
Jennifer Lonoff Schiff is a technology writer and a regular contributor to CIO.com. She also runs a marketing communications firm.
Follow CIO.com on Twitter @CIOonline, Facebook, Google + and LinkedIn.

Searching for Job? Ask these 7 Questions to Yourself

Whether you're just beginning the job hunt or you've been searching for months, it's important to regularly monitor your activities and reevaluate your strategy. Keep your job search on track by asking yourself the following questions throughout the job-search process.
1. Am I qualified for this position?
Go-through the job description precisely prior you submit an application. Do you really live up to the central prerequisites of the post? Prefer only those jobs where you have a firm hold over these must-haves.

2. Does my resume tell the right story?
Possessing accurate skills is equal to half battle; owning an application that determines your academics will make the other half. Deem as if your resume is the integral part of your personal marketing brand. It should highlight what you're qualified for and why excited about your target role to the potential HR.

3. Do I know someone at the company I can talk to?
Hunt your network before you decide to apply for a position. In case your network is decayed, then it's the right time to get out there and generate novel connection!

4. Have I Googled my name this month?
As per the analysis, 93% of employers very often have a look at applicant's social portfolio. In addition, 42% have reassessed a job-seeker pedestal on the matter of their online profiles, arriving at both positive and negative re-evaluation.

5. What have I learned recently?
It's essential to hint for pertinent professional development activities, if you're gazing to mount a corporate ladder or you are recently jobless or looking for work. Try and attend workshops, gain knowledge about the new technologies and open yourself to new connection opportunities.

6. Have I stepped outside my comfort zone to find new job leads?
Most often, candidates just do the mere task of searching a job in a convenient method. While, it is essential to recruit multiple strategies to find a dynamic number of pertinent job leads.

7. Am I ready for the interview?
Never pounce into the interview room unless and until you possess sufficient knowledge about the organization and you're prepared well in advance for the interview questions. Recruiters always look for whether you know a bit about their firm or taking the interview seriously or not.

Source: jobinterviewtipz

Interview Tips for Project Managers
Thinking Out of Box – 4 Simple Rules
4 Tips for Creative Thinking
12 Steps to Build Perfect Teams
Manage Team Performance by Managing Behaviour
How to Work Smart Not Hard
Time Management for daily activities
Secrets to give presentations
7 Rules of Management

Know-how about Depression

You may know someone who is depressed and not know they're depressed. People expect someone who is depressed to cry a lot, stay in bed all day, mope, or sound like Eeyore from Winnie the Pooh. But depression isn't always this obvious.
Some people can totally fake it. They can smile and laugh; they can act like everyone else, even while they are in excruciating emotional pain. Occasionally people who can do this end up killing themselves, and no one can believe it.
The pain is also complicated by the fact that depression attacks a person's thoughts and feelings, rather than liver or lungs. Depression can cause a person to think she hates herself or is unhappy in her relationships.
It can cause someone to believe everyone would be better off without him, or even that others would be better off dead. It can cause people to feel sad, angry, guilty, numb, or rageful, even when none of this is how they feel when they aren't depressed

100+ Inspirational Stories
11 Great Ways to Reduce Stress
11 Tips to Improve Self Confidence
12 Ways to Stay On Top of Stress
22 Things to Keep
28 Lessons of Life
3 Options to Fight Stress
36 Amazing Tips for Great Life

Story: Boy, Girl and Doll

A Six year old boy was in the market with his 4 year old sister...Suddenly the boy found that his sister was lagging behind.

He stopped and looked back.His sister was standing in front of a toy shop and was watching something with great interest.

The boy went back to her and asked,"Do you want something?" The sister pointed at the doll. The boy held her hand and like a responsible elder brother, gave that doll to her.The sister was very very happy...
The shopkeeper was watching everything and getting amused to see the matured behaviour of the boy... Now the boy came to the counter and asked the shopkeeper,

"What is the cost of this doll, Sir!"
The shopkeeper was a cool man and had experienced the odds of life. So he asked the boy with a lot of love and affection,"

Well, What can you pay?"

The boy took out all the shells that he had collected from sea shore, from his pocket and gave them to the shopkeeper. The shopkeeper took the shells and started counting as if he were counting the currency. Then he looked at the boy.The boy asked him worriedly,"Is it less?

" The shopkeeper said," No,No...

These are more than the cost. So I will return the remaining." Saying so,he kept only 4 shells with him and returned the remaining. The boy, very happily kept those shells back in his pocket and went away with his sister...

A servant in that shop got very surprised watching all these. He asked his master," Sir!You gave away such a costly doll just for 4 shells???

"The shopkeeper said with a smile," Well, For us these are mere shells.

But for that boy, these shells are very precious. And at this age he does not understand what money is, but when he will grow up, he definitely will. And when he would remember that he purchased a doll with the Shells instead of Money, he will remember Me and think that world is full of Good people.
He will develop positive thinking..Thats it..."

So always be positive and behave positive++++!

Safety... You will regret if you forget...!

Story: Singapore Taxi
Story: The Two Falcons
Story: ​ How God Answers our Prayers
Story - Ant In the Balcony
Story - Ajay and Anita
Story - The Singapore Taxi
Story - The Lady and 4 Turtles
Story - Shark and Glass Wall
Story - Love and Marriage
Story - The Wooden Bowl
Story - The Two Falcons
Story - How God Answers Your Prayers
Story: The Farmer and Onions
Mr. Nazir Lost His Wife