So you’re trapped all day at your office, looking at spreadsheet after number spreadsheet, and you’re bored out of your mind. It’s time to think out of the box, and to re-imagine the table. We’ve brought together some of the best hidden tricks you may not have heard about in Excel.
- New line in single cell
Occasionally, the text may need to run over to the next line while typing into cells. If you have ever wanted to do this, Excel reads this when you press enter when you move on to the next cell and inserts the following text there. You might not have noticed, though, that you can click ALT+ENTER, and you can add a new line of text to the same cell.
- Double click to copy down
You don’t need to hold and drag the mouse all the way down to copy a formula or value down the length of your data set. Just double click the tiny box in the cell’s bottom right corner. It will immediately down to the end of the value cell you have put.
- Makes formula easier to understand
If your file contains a lot of rules, or even a few complex ones, seeing stuff like= A6*F9*(H3/S6) can be quite overwhelming. You should assign names such as’ Reference Number’ or’ Coefficient’ to cells or groups of cells to clear things up, and use those names in formulas. To do this, pick the cells you want to call and go to FORMULAS–>DEFINE NAME. Once you do this, you should input the chosen name and press OK, but do not use any numbers. To use this in a formula simply type= Reference Number*A2 or other combinations, whether you named a cell’ Reference Number’ Naming cells always lets you find them in the rope very easily.
- Lock cell with F4
While copying formulas in Excel, you sometimes want to switch the input cells with your formulas BUT SOMETIMES YOU DON’T. If you want to lock one of of the inputs you need to add dollar signs before the letter and row number of the cells. Typing in the signs of the dollar is crazy, and a massive waste of time. Alternatively, press F4 to insert generic the dollar signs after you pick your slot, and lock the cell. When you keep hitting the F4 key, you’ll loop through various options: lock cell, lock row number, lock column letter, no lock.
- Instantly resize column and row
Nothing could be worse than making the text appear outside the column range. Then, trying to press then drag again and again to adjust the column to the right width can be a hassle.
Luckily you can do that immediately. Place your cursor on the line between two column markers (e.g., C and D) until you see a symbol which looks like two opposite arrows.
With that mark, double click on that line separating the columns, and the column will be resized automatically to match the widest piece of text in that column.
- Use and combine text strings
We’ve got a list of first and last names here. We can construct a complete called column using &. In Excel, & joins two bits of text or more together. Don’t forget to add a gap between their names. This will feel like your formula=[ first name] &” “&[ last name]. You can combine cell with actual text references as long as the text you want to include is surrounded by quotes.
- Freeze Panes
When you deal with a large data spreadsheet, it can often be difficult to interpret if you don’t have heading rows that represent key information. You can use the freeze panes feature to lock rows and columns, instead of scrolling back up to the top or side each time you need information This is stored in VIEW–>WINDOW–>FREEZE PANES, and you can pick the row or columns you want to freeze from here. This only functions on an Excel sheet’s first rows or columns, so make sure you’ve got the required data there.
- Select all cells with one click
Have hundreds (or even thousands) of rows of data — and all need to be selected?
Tons of repetitive clicking and scrolling will cause you a finger cramp. And, this basic trick can be used to pick all the cells with one single click.
All you need is to click on that small, gray triangle that appears in your spreadsheet’s top left corner. Click it once and it will be select every single cell in the spreadsheet. It is as simple as this!
- Quick calculation
Most of us certainly have been faced with the problem of having the sum of two different cells, but not wanting to have to go through the trouble of writing a whole new formula in another cell. Okay, you don’t need to go through this hassle any more. Click on the first cell to add two numbers, then hold down the CTRL before opening the next cell. The sum of these two numbers will appear in the status bar below, automatically. You can also click right to find other features such as Median (AVG) and Maximum (MIN).
- Use two windows
Do you have worksheets in the same workbook, but do you want to show them side by side instead of clicking back and forth between the two?
There’s one way you can actually do this, of course.
If you’re on a Mac, in the main Excel menu, press “Window” and then choose “New Window.” If you’re on a PC, go to “Preview” in the Excel toolbar and then choose “New Window.” This will open the current workbook in a completely new window, so you can put it side by side to stop a lot of clicks.
- Calculate your age with excel
DATEDIF is a very unfamiliar, undocumented but extremely useful feature for Excel. It measures the distance between 2 dates. It assists in estimating your age. The DATEDIF syntax is= DATEDIF(StartDate, EndDate, Interval) The interval can be Y, M, D, YM, MD, and YD.
Example: Just copy and paste the following in a cell in Excel and press enter
If you want your exact age in year-months and days then copy and paste the following
=DATEDIF(“03-Dec-1988″,”01-Oct-2012″,”Y”) & ” Years ” & DATEDIF(“03-Dec-1988″,”01-Oct-2012″,”YM”) & ” Months ” & DATEDIF(“03-Dec-1988″,”01-Oct-2012″,”MD”) & ” Days”
(source: businessinsider.sg, goskills.com, quadexcel.com, wordstream.com, and interestingengineering.com)