Microsoft Excel is a dynamic analysis tool that has become a major part of our business environment. It is estimated that around 89% of companies utilize Excel in the operations of their businesses, but very few are using it to its fullest potential. Excel is such a powerful tool, with the capabilities of doing so much, and it seems that Microsoft is committed to continuously upgrading and enhancing its abilities. Here are some lesser-known functions of Excel that may help you to become more adept.
1.) Quick Analysis Tool
The quick analysis tool can be found by selecting the data you want to analyze. A little pop-up will appear in the bottom right-hand corner of the data. This pop-up gives you quick access to formatting tools, charts, tables, sparklines, and basic formulas. This saves time on typing formulas and moving through different menus to find the desired tool. If the pop-up does not appear, the shortcut for the quick analysis tool is (ctrl + Q).
2.) Filters
Filters enable you to extract data without changing the underlying data set. These filters can be inserted into your spreadsheet by using the editing menu in the home page of Excel or by using the shortcut (ctrl + shift + L).
3.) Drop-Down List
Drop-down lists are a great way to ensure consistency in a worksheet. Create a list of values in a new sheet. To access the drop-down list function, use the data tools in the data tab. Change the “allow bow” to “List” and select your source data. Drop-down lists can also be accessed through the short cut (Alt + down arrow).
4.) Auto-Fit Column Width and Auto-Fit Row Height
Auto-fit column width can be accessed through hovering between columns and then left clicking twice. This only allows you to do one column at a time. For multiple columns, select your columns then use the short cut (Alt + H + O + I).
Auto-fit row width can be accessed through hovering between rows and then left clicking twice. This only allows you to do one column at a time. For multiple columns, select your columns then use the short cut (Alt + H + O + A). Both of these are time savers with any spreadsheet.
5.) Transpose
Transpose is a great tool to see data in a different format or to switch the column and row labels. To utilize this function, select your data. Then select a cell that you would like the transpose to start in. Then use the special paste menu to add transposition. The short cut for this action is (ctrl + C, select a space, and ctrl + Alt + V + E).
6.) Remove Duplicates
Remove duplicates is a great way to identify duplicate entries with conditional statements. This function can be found in the conditional formatting menu in the home tab. The short cut to the function is (Alt + H + L + H + D + Enter). This function will highlight duplicates in your data, which is more efficient then scanning through the normal data.
Transpose is a great tool to see data in a different format or to switch the column and row labels. To utilize this function, select your data. Then select a cell that you would like the transpose to start in. Then use the special paste menu to add transposition. The short cut for this action is (ctrl + C, select a space, and ctrl + Alt + V + E).
7.) Goal Seek
Goal seek is a great way to look at projects and study data to see if you are meeting your expectations. This function can be found in the what-if analysis menu in the data tab. The short cut to the function is (Alt + T + G). This function will allow you to set a goal and see if you are meeting that goal without changing your formulas.
8.) V-Lookup
V-lookup is a great way to extract specific data from a data table. This function can be found using the equals sign and the insert function key in excel. V-lookup allows you to search a table of data and return your requested information.
9.) Defined Name Ranges
Defined name ranges are a great way to make Excel spreadsheets more legible. This function can be accessed by selecting any cell with a formula then hitting (ctrl + F3). This will allow you to change the name of the formula in the cell. Instead of seeing a string of number and letters for the formula, you can change the formula to show what it is.
10.) Paste Special Values
Paste Special Values is a great way to transfer data from spreadsheets. This function can be accessed by copying the data you want then hitting (ctrl + Alt + V). This will allow you to change what type of data you want to paste. This is helpful when extracting data from formula spreadsheets.
If you would like to learn more about this topic, please contact:
Shari Diamond, CIA
Partner
Shari has been with Cerini & Associates, LLP since 2008 where she works primarily with the firm’s school district clients providing internal audit and claims audit services. She has over twenty years’ experience performing internal audits, risk assessments, and compliance reviews, as well as recommending processes to strengthen the internal controls environment while increasing efficiencies. Her prior experience at PWC and Northrop Grumman included performing Information Technology audits.
