Monday, April 27th, 2020 / by Angie
Let’s face it: Excel is a very complete tool, with a lot of features a casual user might not be aware of. Today we’ll take a look at them and show you how they can improve your workflow!
For a very quick way to hide certain contents, look no further than the outline option! Select the data you want to hide, then click on the Data ribbon, then go to the far-right to Outline and select Group.
The data you selected will now appear a little different—notice that minus sign on row 45? Clicking on it will hide everything in that bracket automatically!
Imagine you were just handed a new sheet, and need to know where exactly all the formulas used are—both so you can make sure they’re right, and change them if you need to.
One way to do this is hitting Ctrl+`—that ` symbol is what many multi-lingual speakers will recognize as a grave accent (used, for instance, like this: à), and its position in the keyboard depends on which type of keyboard you’re using. For American-based ones, it’s usually to the left of the 1 key.
Another way is to select all cells containing formulas. Pres F5, and on the pop-up, click Special and toggle Formulas, hit enter, and that’s it!
We’ve talked about how Visual Basic can improve and add on functionalities to Excel and other Office products. But did you know some very creative people out there used it to create games that run in Excel?
Sometimes, you don’t want to hand out a worksheet with all the data you used, especially when it involved sensitive data. You can do it with outlines, or with cell formatting. To hide a cell through formatting, column, or row, just select it and go to Home->Font->Open Format Cells->Custom and under “Type”, go ahead and enter “;;;” (no quotations). This will leave the chosen cells blank until you re-format them again.
Another option is to go to “Protection” in the Format Cells tab and check the “hidden” box—but that option will only work when you lock your Excel sheet.
If you need to compare two different worksheets in the same book, there’s an option for that!
Go in the View tab, and click on Window->New Window. Then again on View, click on View side-by-side and voilá! You can even scroll both at the same time (or not!) with the Synchronous Scrolling option.
This isn’t really a feature most people don’t know about, but it’s one they might not use often. So if you find that going back and forth between Excel and your Calculator is too much of a bother—well, you don’t have to! Let Excel handle all the basic math through its simple formulas.
You can now add and get Geographical and Stock data in this relatively new Excell feature! And it’s simple to do it too! For Stocks, Just type some text into a cell, (say, “Apple”), go to the Data Tab, and click on Stocks—give it some seconds for Excel to fetch the data, and Apple’s current Stock price will show up in that cell! For Geographic data type a country, province, territory, or city, select the cell, go to the Data tab and click on Geography.
This only works, however, if there’s an online match for your input.
Ever found yourself scrolling up and down to find a specific piece of data you need for comparison or other purposes? Then this feature is for you! Select the rows and/or columns you want to freeze, then go to the View tab, select Freeze Panes, click on it, and you’re done!
There’s a chance you’ve noticed Excel exhibits different behaviors depending on where you do a double click. Double-clicking up top on the column separator, for instance, will resize the column to fit the data within; if you double-click on the cell border of the first cell in a given row or column, it will snap you to the last cell in the series; and if you have data that follows a pattern in increases or decreases (say, assigning student numbers to your class), you can select a couple of cells, double-click on the lower-left corner (where the plus sign shows), then drag the mouse down—Excel will follow the pattern and auto-fill the rest of the cells for you!
Although you can understand what a formula does by reading its original format (say, B3+A4/2), once you start having more complex formulas, it can be quite a pain. The good news is that if you go to Forumlas->Define Name you can give any formula you’re using its own designation, such as Coefficient, Gross Profit Calculation, and so on!
I can not recommend ExcelHelp more highly. We had been struggling to complete a critical project and finding them was a lifesaver. Tom was knowledgable, professional, personable, patient and responsive. Look no further for your database programming needs. Thanks Tom and ExcelHelp.com!Randy Hartnell
We anticipate a long business partnership with ExcelHelp and look forward to working with them. I have been nothing but impressed with your firm’s services, your team members tenacity and the overall results. Sometimes you have to travel a bumpy road trying different firms until you find the one that fits.Posted By: Charlene Faber
Chief Operation Officer, Current Builders
I’d like to express my gratitude for the help and cooperation we’ve received from your entire organization during the development of our Modeling Utility. Over the past several weeks we’ve engaged key customers worldwide...We have confidence that this effort will add significant capability and value to the overall success of the program.Business Development Manager
Network Architecture and Strategy
Posted By: Marty M.