For years, Microsoft Excel has represented the gold standard when it comes to spreadsheet software. Although firm market share figures specific to Excel use are hard to come by, Microsoft Office, the productivity suite in which Excel comes packaged, still claims approximately 80% to 96% of the worldwide user share.
Still, there are countless ways to supercharge your Microsoft Excel experience—some built directly into the program’s interface, and some available as free external plug-ins. Below you’ll find the top 10 tips, tricks, apps, and add-ins to get even more productivity out of Microsoft Excel:
1) Dig deep into data with PivotTable reports. This interactive Excel option allows users to see the big picture contained within a data set by automatically extracting, organizing, and summarizing data. Select either a whole table or individual cell and click on Insert > PivotTable—then visit Microsoft’s PivotTable Support page for further tutorials.
2) Apply similar formatting to multiple sheets at the same time. This critical task is a breeze with Excel’s grouped worksheets feature. Click the tabs of the sheets you want to group together while pressing CTRL and they’ll all turn white; while the sheets are grouped, any formatting entered on one sheet also shows up in the others. After formatting grouped sheets, make sure to click on tabs to ungroup them before entering or removing data.
3) Improve user interface with Excel Utilities. This free AppsPro add-in improves user interface development via 30 shortcuts and selection tools that spreadsheet pros use on a daily basis. From one-click access buttons to conditional formatting, data validation, unhiding or rehiding worksheets, and more, Excel Utilities puts these shortcuts within easy reach on the ribbon tab in Excel 2007 or higher.
4. Improve user interface with ASAP Utilities.
Another free add-in, ASAP Utilities, bills itself as “the most popular add-in for Excel users” and a “must-have for anyone who spends time in Excel.” With over 300 features and shortcuts—exporting worksheets as separate files, sorting sheet tabs by name or color, auto-naming multiple worksheets, selecting cells based on formatting and content—ASAP Utilities works with versions all the way back to Excel 2000.
5. Prevent bad data from mucking up worksheets. You know the saying: “garbage in, garbage out.” That’s particularly true in Microsoft Excel, where one bad piece of data can ruin an entire worksheet. Avoid that with Data Validation, which specifies the format that can be entered into cells. In a table, highlight the cells that should only contain one kind of data; from the Table Tools tab, click Data Validation, make your selection, and then enable Error Alerts to display the right format.
6. Easily jump between multiple Excel spreadsheets using Office Tabs. All good Internet browsers are built around tabbed viewing. Office Tabs—available for free on Office 2010 and earlier and for $25 after a 30-day free trial on Office 2013—allows you to organize and easily toggle between multiple open spreadsheet files in one window.
7. Open multiple worksheets with one double click. Working on multiple worksheets one day and know you’ll do it again the next? Before you shut down Excel, click on View > Save Workspace, and then enter a name and location for your saved view. An icon will appear in the location of your choice—clicking on that icon takes you back to the same worksheet set.
8. Use Search Commands for intuitive internal search. Mac users consider the baked-in magnifying glass search pane a basic necessity. Now Microsoft Office users can take advantage of the free Search Commands download from Office Labs, which also offers a Search the Web bar powered by Google for Office 2013.
9. Identify duplicate values. Want to weed out duplicates? Select the range of cells you want to assess, and then click on Conditional Formatting > Highlight Cells Rules > Duplicate Values. To isolate the duplicates, right-click on one of the cells, then click on Filter > Filter By Selected Cell’s Color.
10. Insert rows and columns with ease. A good Excel spreadsheet is always ready to grow. Quickly adding rows and columns can be done in a variety of ways: by clicking Insert > Rows or Columns; right-clicking on rows or columns and choosing Insert; or pressing the CTRL and + or CTRL, SHIFT, and + keys.
Whether you’re a seasoned Excel pro scanning the horizon for fresh usability tricks, or a new user scrambling to make sense of it all, these tips should beef up your program experience.
Considering an upgrade to Office 2013 or looking for other ways to make your technological life more productive and efficient? Call or email CMIT Solutions today—we’re here to make technology work for your business, not against it.