Most of us are probably familiar with Excel, but not knowing the shortcuts and tricks can turn any simple spreadsheet into a long drawn-out process. We have put together some of the best hidden tricks in Excel that you may not have known about.
New Line in a Single Cell
When typing in cells, sometimes your text may need to run over to the next line. If you have ever tried to do this, when you press enter, Excel reads this as you moving on to the next cell and it inserts the following text there. However, you may not have known that you can press ALT+ENTER, and you can add a new line of text to the same cell.
Most of us have probably run into the issue of needing 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 for it. Well, you no longer have to go through this trouble. To add two numbers, click the first cell, then hold down CTRL while clicking the next cell. The sum of these two numbers will automatically appear in the status bar down below. You can also right click and find other functions like average (AVG) and Minimum (MIN).
If you are working with a large spreadsheet of data, it can often be hard to interpret if you don't have heading rows displaying key information. Instead of scrolling back up to the top or side each time you need information, you can use the freeze panes function to lock rows and columns. This is located in VIEW–>WINDOW–>FREEZE PANES, and from here you can select the row or columns you would like to freeze. This only works on the first rows or columns of an Excel sheet, so make sure you have the necessary data there.
Make Formulas Easier to Understand
If your spreadsheet incorporates a lot of formulas, or even just a few complex ones, seeing things like =A6*F9*(H3/S6), can get pretty confusing. To clear things up, you can assign names like 'Reference Number' or 'Coefficient' to cells or groups of cells, and use these names in formulas. To do this, select the cells you want to name and go to FORMULAS–>DEFINE NAME. Once you do this, you can input the name you want and click okay, but don't use any numbers. If you named a cell 'Reference Number', to use this in a formula simply type =Reference Number*A2 or other variations. Naming cells also allows you to find them really fast in the ribbon.
Double Click Tricks
Excel has a lot of built in features that are operated through double clicking a certain area. If you double click on a column separator up top, it will autosize the column to fit the data. You can snap to the last cell in a column or row by double clicking on the cell border of the first cells in that row or column. The selection will automatically jump to the last cell in the series. Lastly, if you have a series of data that automatically increases or decreases with a pattern, you don't have to manually input each value. Simply type out 2 cells and then select them, and double click in the bottom corner when the plus icon appears. This will autofill the data to every box that has data next to it.
If you enjoyed this list, stay tuned to Interesting Engineering, as we will be coming out with more Excel tips in the near future. What are some of the tips and tricks you use in your Excel worksheets? Add to this list by commenting below and help out others just learning, or looking to improve their spreadsheet skills!
Written by Trevor English