Advertising
Advertising

5 Powerful Excel Functions That Make Work Easier

5 Powerful Excel Functions That Make Work Easier

If you’ve just started learning how to use Microsoft Excel, you’ll probably have a lot of questions to ask about the functions of the program. Because let’s face it, despite being exceptionally useful, Excel can be a very complicated application. It’s like a hammer when your most frustrating reporting tasks at work resemble nails.

Aside from great Excel features such as flash fill, pivot tables, and conditional formatting, Excel also has a lot of powerful functions that will help save time when creating spreadsheets. Invest some time in learning to use Excel so you can prepare and manage complex reports, as well as perform what-if analysis on data like a pro!

To help you get started, here are 5 important Excel functions you should learn today.

1. The SUM Function

The sum function is the most used function when it comes to computing data on Excel. This function works to sum a group of numbers in a specific set of cells. This means you don’t need to type a long cumbrous formula just to calculate the sum of all the data you need. Because of its popularity, newer versions of Microsoft Excel have a button specifically for this function.

This function is performed by typing the formula on the function bar and highlighting the cells you want summed before clicking “Enter”. You also need to be careful in highlighting cells, as Excel will sum everything you include. If this happens, you can easily click the “Undo” button to reset the values back to its original state.

Advertising

SUM function

    The syntax formula for sum function is “=SUM” (number1, number2, etc.).

    In this image, the sum function for the cells C2 through C7 is obtained through the formula “=SUM(C2:C7)”, giving you the result of 33161.

    2. The TEXT Function

    Text function is a useful tool that helps convert a date (or number) into a text string in a particular format. It falls in the category of string formulas that converts numerical values to a string. It is handy when users need to view numeric data in a readable format. Take note that the “TEXT” formula only works to convert numeric values to text. Therefore, its results cannot be calculated.

    Advertising

    TEXT FUNCTION

      The syntax formula for text function is “=TEXT” (value, format_text).

      • “Value” refers to the particular number you wish to convert to text.
      • “Format_text” defines the format of the conversion.

      In this example, the user uses a text formula to find the abbreviated day for the date “=TEXT (B2, “ddd”)”.

      3. The VLOOKUP Function

      VLookup is powerful Excel function that is often overlooked. Users will find it useful when they need to find specific data on a large table. You can also use VLookup to search for names, phone number, or specific data on your sheet. Instead of manually looking for the names and wasting time scrolling through hundreds of data, the VLookup function makes this process faster and more efficient.

      vlookup

        Image: spreadsheeto.com

        Advertising

        The VLookup formula is “=VLOOKUP” (lookup_value, table_array, col_index_num, *range_lookup*).

        • “lookup_value” is the data you want to find.
        • “table_array” is the data column where you want to limit your search.
        • “col_index_num” is the column number within the table that you want to return a value from.
        • “range_lookup” is an optional argument that allows you to search for the exact match of your lookup value without sorting the table.

        4. The AVERAGE Function

        The average function is an extremely useful tool for getting the average value in a range of cells. Like the sum function, it is frequently used in computing and analyzing data on spreadsheet. Basically, the average function works to find the “arithmetic mean” for a group of cells. Aside from the average function, Excel also has the median and mode function.

        Average Function

          The syntax formula for the average function is “AVERAGE” (number1, number2, etc.).

          • “Number 1” refers to the first number in the range where you want the average.
          • “Number 2” is the additional reference of the average range. You can get an average of up to a maximum of 255 cells.

          Additional ­­Examples:

          Advertising

          “=AVERAGE (A2:A10)” – computes the average of numbers in cells A2 through A10.

          “=AVERAGE (B2: B10, 7)” – computes the average of the numbers in cells B2 through B10 and the number 7.

          5. The CONCATENATE Function

          This function is a good time saver when you need to combine data from 2 or more cells. Unlike the merge tool which physically merges two or more cells into a single cell, the concatenate function only combines the contents of the combined cells. In the latest version of Excel ( 2016), the concatenate function has been replaced with concat function and will be incorporated in more future versions of Excel.

          Average Function

            The syntax formula for the concatenate function is “CONCATENATE” (text1, [text2…text_n]),

            • “Text1, Text2…text_n” are the data you want to combine.

            More by this author

            Armela Escalona

            Freelance Writer

            9 Beautiful Journals for People Who Love To Take Notes 7 Ways To Fix Damaged Hair and Get Your Healthy Hair Back How To Design Your Bedroom the Feng Shui Way 5 Tools to Create a Professional Website for Free 5 Best Professional E-commerce Store Builders

            Trending in Productivity

            1 The Science of Setting Goals (And How It Affects Your Brain) 2 What to Do When Bored at Work (And Why You Feel Bored Actually) 3 6 Effective Ways to Enhance Your Problem Solving Skills 4 How to Concentrate and Focus Better to Boost Productivity 5 15 Productive Things to Do When Bored (So Time Is Not Wasted)

            Read Next

            Advertising
            Advertising
            Advertising

            Last Updated on July 17, 2019

            The Science of Setting Goals (And How It Affects Your Brain)

            The Science of Setting Goals (And How It Affects Your Brain)

            What happens in our heads when we set goals?

            Apparently a lot more than you’d think.

            Goal setting isn’t quite so simple as deciding on the things you’d like to accomplish and working towards them.

            According to the research of psychologists, neurologists, and other scientists, setting a goal invests ourselves into the target as if we’d already accomplished it. That is, by setting something as a goal, however small or large, however near or far in the future, a part of our brain believes that desired outcome is an essential part of who we are – setting up the conditions that drive us to work towards the goals to fulfill the brain’s self-image.

            Apparently, the brain cannot distinguish between things we want and things we have. Neurologically, then, our brains treat the failure to achieve our goal the same way as it treats the loss of a valued possession. And up until the moment, the goal is achieved, we have failed to achieve it, setting up a constant tension that the brain seeks to resolve.

            Advertising

            Ideally, this tension is resolved by driving us towards accomplishment. In many cases, though, the brain simply responds to the loss, causing us to feel fear, anxiety, even anguish, depending on the value of the as-yet-unattained goal.

            Love, Loss, Dopamine, and Our Dreams

            The brains functions are carried out by a stew of chemicals called neurotransmitters. You’ve probably heard of serotonin, which plays a key role in our emotional life – most of the effective anti-depressant medications on the market are serotonin reuptake inhibitors, meaning they regulate serotonin levels in the brain leading to more stable moods.

            Somewhat less well-known is another neurotransmitter, dopamine. Among other things, dopamine acts as a motivator, creating a sensation of pleasure when the brain is stimulated by achievement. Dopamine is also involved in maintaining attention – some forms of ADHD are linked to irregular responses to dopamine.[1]

            So dopamine plays a key role in keeping us focused on our goals and motivating us to attain them, rewarding our attention and achievement by elevating our mood. That is, we feel good when we work towards our goals.

            Dopamine is related to wanting – to desire. The attainment of the object of our desire releases dopamine into our brains and we feel good. Conversely, the frustration of our desires starves us of dopamine, causing anxiety and fear.

            Advertising

            One of the greatest desires is romantic love – the long-lasting, “till death do us part” kind. It’s no surprise, then, that romantic love is sustained, at least in part, through the constant flow of dopamine released in the presence – real or imagined – of our true love. Loss of romantic love cuts off that supply of dopamine, which is why it feels like you’re dying – your brain responds by triggering all sorts of anxiety-related responses.

            Herein lies obsession, as we go to ever-increasing lengths in search of that dopamine reward. Stalking specialists warn against any kind of contact with a stalker, positive or negative, because any response at all triggers that reward mechanism. If you let the phone ring 50 times and finally pick up on the 51st ring to tell your stalker off, your stalker gets his or her reward, and learns that all s/he has to do is wait for the phone to ring 51 times.

            Romantic love isn’t the only kind of desire that can create this kind of dopamine addiction, though – as Captain Ahab (from Moby Dick) knew well, any suitably important goal can become an obsession once the mind has established ownership.

            The Neurology of Ownership

            Ownership turns out to be about a lot more than just legal rights. When we own something, we invest a part of ourselves into it – it becomes an extension of ourselves.

            In a famous experiment at Cornell University, researchers gave students school logo coffee mugs, and then offered to trade them chocolate bars for the mugs. Very few were willing to make the trade, no matter how much they professed to like chocolate. Big deal, right? Maybe they just really liked those mugs![2]

            Advertising

            But when they reversed the experiment, handing out chocolate and then offering to trade mugs for the candy, they found that now, few students were all that interested in the mugs. Apparently the key thing about the mugs or the chocolate wasn’t whether students valued whatever they had in their possession, but simply that they had it in their possession.

            This phenomenon is called the “endowment effect”. In a nutshell, the endowment effect occurs when we take ownership of an object (or idea, or person); in becoming “ours” it becomes integrated with our sense of identity, making us reluctant to part with it (losing it is seen as a loss, which triggers that dopamine shut-off I discussed above).

            Interestingly, researchers have found that the endowment effect doesn’t require actual ownership or even possession to come into play. In fact, it’s enough to have a reasonable expectation of future possession for us to start thinking of something as a part of us – as jilted lovers, gambling losers, and 7-year olds denied a toy at the store have all experienced.

            The Upshot for Goal-Setters

            So what does all this mean for would-be achievers?

            On one hand, it’s a warning against setting unreasonable goals. The bigger the potential for positive growth a goal has, the more anxiety and stress your brain is going to create around it’s non-achievement.

            Advertising

            It also suggests that the common wisdom to limit your goals to a small number of reasonable, attainable objectives is good advice. The more goals you have, the more ends your brain thinks it “owns” and therefore the more grief and fear the absence of those ends is going to cause you.

            On a more positive note, the fact that the brain rewards our attentiveness by releasing dopamine means that our brain is working with us to direct us to achievement. Paying attention to your goals feels good, encouraging us to spend more time doing it. This may be why outcome visualization — a favorite technique of self-help gurus involving imagining yourself having completed your objectives — has such a poor track record in clinical studies. It effectively tricks our brain into rewarding us for achieving our goals even though we haven’t done it yet!

            But ultimately, our brain wants us to achieve our goals, so that it’s a sense of who we are that can be fulfilled. And that’s pretty good news!

            More About Goals Setting

            Featured photo credit: Alexa Williams via unsplash.com

            Reference

            Read Next