Each day, I spend a great deal of my time using Microsoft Excel. Even though I had used Excel for years, I still fumbled around a bit here and there. My efficiency and proficiency skyrocketed when I finally took the time to sit in on our Excel classes. I walked away with so many new tools and tips and tricks, and I thought I would share some of my favorite tips with you:
1. Putting text on several lines
This is one of my most favorite tips. If you're typing text or multiple lines of data into a cell and you want the text to appear on several lines of that same cell, instead of entering the text in another cell just simply press ALT+ENTER and you'll start a new line while you're typing or editing data (all within the same cell).
2. Adding a Calculator to the Quick Access Toolbar
Do you ever have an excel sheet open and you want to do a quick calculation or convert currency, or length, or a date calculator? In Excel 2016, you can add a calculator hot key to your Quick Access Toolbar, so it is handy and ready to use, next to the save icon and undo or redo. (If you aren’t sure where that is, check out our Excel User Interface Post. It quickly shows you all of the different areas of Excel and the proper names of each area).
On the Quick Access Toolbar, click the drop-down arrow and select More Commands...
Under Choose Commands from..., select Commands Not In the Ribbon.
Scroll down and click on Calculator.
Then push the Add>> button.
Finally, click OK.
While you are in there, browse through and see if there are other commands you might want to add (for example: Insert Hyperlink or Freeze Frame, or dozens of other useful commands that will save you time in Excel).
3. Quickly Move through worksheets using shortcut keys
Just as you can use ALT + TAB to skip through the opened program windows on your Windows desktop, you can do a similar thing to skip through the worksheets in Excel.
To move one worksheet to the right press: CTRL+PAGE DOWN
To move one worksheet to the left press: CTRL+PAGE UP
4. Display Formulas Instead of Results
I love this tip because I use so many formulas in my Excel spreadsheets, and I was constantly adjusting or comparing formulas. Before discovering this tip, I used to have to go to File menu, go to Options, then Advanced; then scroll down to Display Options for this Worksheet and check the box next to "Show formulas in cells instead of their calculated results." Then I would need to uncheck the box to display results again.
With this tip, I save time, and time is money. A single keystroke lets me toggle between Excel's normal display, which shows the results of the formulas in the spreadsheet, and a display mode that shows the actual formulas. The keystroke is Ctrl-`; (hint: The grave accent key is located on the top left of the keyboard next to the 1 key); press it once, and Excel displays formulas instead of results. Press it again, and the results appear again.
5. Shade Alternate Rows
When you have a long list of products/customers/etc.., it is easy to get overwhelmed or lose track of what details you are looking at on different rows. One way to make your data legible is to apply cell shading to every other row in a range. Excel's Conditional Formatting feature (available in Excel or later) makes this a simple task.
Select the range that you want to format (either a specific range of cells, or the whole worksheet).
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click New Rule.
In the New Formatting Rule dialog box, under Select a Rule Type, click Use a formula to determine which cells to format.
In the Format values where this formula is true box, enter =MOD(ROW(),2)=0, (as shown in the illustration.)
In the Format Cells dialog box, click the Fill tab.
Select the background or pattern color that you want to use for the shaded rows, and then click OK.
At this point, the color you just selected should appear in the Preview window in the New Formatting Rule dialog box.
To apply the formatting to the cells on your worksheet, click OK.
The best part is that the row shading is dynamic. You'll find that the row shading persists even if you insert or delete rows within the original range.
If you are eager for more tips and tricks, or ways to increase your efficiency in Excel, join us for an upcoming class.
More about Expand Learning and our Microsoft Excel Training We invite you to attend Microsoft Excel training in Salt Lake City, Utah. Our locally owned and operated classroom is designed to allow you to get the most out of your Excel class with training delivered by local Excel pros.
Attend a Microsoft Excel class in our Salt Lake City, Utah facility and learn to organize, calculate, analyze, revise, update, and present your data in ways that will help the decision makers in your organization steer you in the right direction. It will also make these tasks much easier for you to accomplish, and in much less time, than if you used traditional pen-and-paper methods or non-specialized software. You will also learn to collaborate with colleagues, automate complex or repetitive tasks, and use conditional logic to construct and apply elaborate formulas and functions will put the full power of Excel right at your fingertips. The more you learn about how to get Excel to do the hard work for you, the more you'll be able to focus on getting the answers you need from the vast amounts of data your organization generates.
Expand Learning Solutions offers local instructor-led training classes in Salt Lake City, Utah for all available Excel versions. We offer 3 levels of Excel, including:Excel Basic, Intermediate, and Advanced classes. We also offer 2 day Excel VBA (Excel Programming with Visual Basic for Applications).
If you can't attend at our facility, we still have you covered with Microsoft Excel Training via live online options and self-paced options.