Microsoft Excel is a powerful tool for compiling and analyzing large quantities of data, but many users are intimidated by its complexity. You don’t need to be a master programmer to master Excel functions—just endure the small amount of time it takes to learn some advanced features and reap the rewards!
There’s so much more to Microsoft Excel than simple spreadsheets and reports, asserts Mike Thomas in his webinar, “How to Master Excel’s ‘Power’ Functions.” Thomas provides a comprehensive set of three 60-to-90-minute training sessions in which you’ll learn to use the full scope of Power Pivot, Power Query, Lookup functions, and more.
Keep Function & Formula Tips Handy
Functions and formulas might sound confusing but by definition are fairly straightforward.
Definition: “A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel,” explains Excel Easy.
The ease of functions is that they already exist, but you need to understand what you’re looking for when you want to sort your data. You can find lists of functions and their explanations online. For example, the Corporate Finance Institute and Computergaga both provide lists of functions (and how to use them). You’ll also find handy explanations for 16 Excel functions in this PCWorld article.
As you venture beyond functions into formulas, it’s easy to get frustrated. Here are a few tips to remain successful when working with Excel formulas, from another PCWorld article.
- If you use Copy and Paste > Special > Values for dates, the result will be text and cannot be converted to a real date. Dates require the DATEVALUE formula to function as actual dates.
- Formulas are always displayed in uppercase; if you type them in lowercase, Excel converts them to uppercase.
- There are no spaces in formulas. If your formula fails, check for spaces and remove them.
4 Excel Techniques To Boost Your Skillset
Here are four tips to help you create pivot tables, sort and rank data, and update the date and time in a worksheet automatically:
- Create a Pivot Table to sort data. Pivot tables are summary tables that are useful for sorting and analyzing your organization’s data. For example, you might wish to reveal revenue for a particular branch location or sales for a specific item. To sort data with a pivot table, follow these steps provided by Sunil Ray’s Analytics Vidhya article:
- Click somewhere in the list of data.
- Choose the Insert tab, and click PivotTable. Excel will automatically select the area containing data, including the headings. If it does not select the area correctly, drag over the area to select it manually. Placing the PivotTable on a new sheet is best, so click New Worksheet for the location and then click
- Now, you can see the PivotTable Field List panel, which contains the fields from your list; all you need to do is to arrange them in the boxes at the foot of the panel. Once you have done that, the diagram on the left becomes your PivotTable.
- Use SUMIF for a more concise IF statement. When you need to perform a calculation where a cell must meet specific criteria, consider using SUMIF instead of the more familiar IF function.
Example: Let’s say you’re calculating sales bonuses and you want to determine a sales associate’s commission. If the associate makes at least $20,000 in sales, they receive a 20% commission. You’ll multiply D3 (which displays an associate’s sales figure) by .2, but only if that cell’s current value is greater that 20000. If the associate sells less than $20,000, they receive no commission.
You could use the following IF function:
=IF(D3>20000,.2*D3,0)
But consider instead the more straightforward formula:
=.2*SUMIF(D3,“>20000”)
Because the alternative is zero, you don’t need to include it in the SUMIF formula.
- Rank your data without sorting it. When you need to rank spreadsheet data, such as quarterly sales—but you don’t want to sort all of the worksheet’s data based on the ranking—use the RANK.EQ function.
Example: You can rank the quarterly sales numbers for your company’s six locations while still keeping the worksheet sorted alphabetically based on location name.
The RANK.EQ function uses the syntax RANK.EQ(number, ref, order). The number is the cell containing the number you want to rank, the ref is the list of numbers to rank your number among, and order is an optional argument that specifies ascending or descending order. If you skip this argument or use zero, you’ll rank in descending order. Entering a value other than zero ranks the data in ascending order.
For our example, let’s say the locations’ sales are listed in cells B4:B9. We’ll place the rankings in cells C4:C9. Click in C4 and type:
=RANK.EQ(B4,$B$4:$B$9,0)
This formula finds the rank of the sales figure in cell B4 among the range $B$4:$B$9, and the 0 tells Excel to calculate the rankings in descending order. The absolute reference ensures that when you copy this formula to the remaining five cells, it’ll continue to rank the numbers based on this range. To copy the formula for your remaining five locations, select C4, click on the fill handle in the lower-right corner of the cell, and drag to C9.
- Know when to use TODAY, NOW, or a keyboard shortcut. Dates appear in Excel worksheets for various reasons. Perhaps you regularly print reports, and you want the printouts to reflect how recently each report was updated. On the other hand, you may need a spreadsheet to document the date and time that you received an important phone call. Of course, you can type the current date into Excel, but this method takes time and isn’t foolproof, especially if you want to update the date each time you open the spreadsheet. You could easily forget to update it.
When you want your date and time to automatically update, use TODAY and NOW. To do so, click in a cell, type the following formula, and then press [Enter].:
=TODAY
Excel displays the current date. Every time you reopen the file, the date updates. To insert the date and time, click in a cell, type the following formula, and then press [Enter]:
=NOW()
More: Simply click in a cell and press [Ctrl]; when you want to insert the current date. Similarly, press [Ctrl][Shift]; to insert the current time. This information will not automatically update.
Learn To Put Power Functions To Work
If you want to break through to some of Excel’s more sophisticated data analysis tools and reach your goal of eliminating data entry errors, you can. Mike Thomas shows you how to take pivot tables to the next level, import and clean your data quickly, and master lookup functions to improve the accuracy and integrity of your workbooks in this webinar. You’ll walk away with a greater understanding of what Excel has to offer—and the confidence to begin using these Power functions in your daily work.
