Top 10 Excel tips
Format whatever object fast with Ctrl+1
If you select any object in Excel – a cell, a chart, a chart axis, a drawing object – then press Ctrl+1, and you will get the Properties dialog for the certain object. This shortcut offers a very quick and easy way to format whatever object you’re working with.
Use formula auditing
This is a great tool to analyze and trace precedent or dependent cells, check errors and evaluate formulas. The “Watch Window” is a feature to keep a snapshot of an area of the spreadsheet, and then move to another area of the workbook – particularly valuable if you’re managing large spreadsheets or don’t have a second screen.
You can Draw Equations
This feature is available in Excel 2016 (useful if you are having a touchscreen PC). To draw equations you need to go to the Insert tab on the ribbon menu and select Equation and Ink Equation. And after that, you can sketch in the yellow box.
Shortcuts
Some of the best Excel tricks come from the software’s series of simple keyboard shortcuts that allow you to maneuver around the document even more quickly. Here are some of our favorites:
- Ctrl+; – Inserts the date.
- Ctrl+Shift+: – Inserts the current time.
- Ctrl+0 – Hides the selected column.
- Ctrl+9 – Hides the selected row.
- Ctrl+` – Reveals all of the formulas embedded in your spreadsheet.
- Ctrl+PageUp or PageDown – Toggles between sheets in the currently open workbook.
- F2 – Start editing the current selected cell vs. double clicking.
- Shift+F10 – Opens the right-click menu for the selected cell.
Use range names with Ctrol+G or F5 key
If you use range names (which we strongly recommend to do) and you want to choose the range with a specific name references, press either Ctrl+G or the F5 key, which launches the GoTo dialog. If the name is simple, you can click on it in a list in that dialog. But if it’s at all unusual, Excel won’t list it; so you will need to type in the name. Then press OK.
Filters
Explore data in a table quickly. Filtering effectively hides data that is not of interest. Usually there's a value (e.g. Blue cars) that you're looking for and Filters will bring up those and hide the rest. But in more modern versions of Excel, you can now also filter on number values (e.g. is greater than, top 10%, etc), and cell colour. Filtering becomes more powerful when you need to filter more than one column in combination e.g. both colours and vehicles to find your blue car. Alt D F F is the shortcut (easier than it sounds - give it a go). Conditional Formatting and Sorting serve related purposes. Sorting involves rearranging your spreadsheet, which is intrusive and may not be desirable. Conditional formatting brings visualisation. Filtering is fast and effective. Choose well.
SUM
All Excel formulas begin with the equals sign, =, followed by a specific text tag denoting the formula you'd like Excel to perform. The SUM formula in Excel is one of the most basic formulas you can enter into a spreadsheet, allowing you to find the sum (or total) of two or more values. To perform the SUM formula, enter the values you'd like to add together using the format, =SUM(value 1, value 2, etc). The values you enter into the SUM formula can either be actual numbers or equal to the number in a specific cell of your spreadsheet.
- To find the SUM of 30 and 80, for example, type the following formula into a cell of your spreadsheet: =SUM(30, 80). Press "Enter," and the cell will produce the total of both numbers: 110.
- To find the SUM of the values in cells B2 and B11, for example, type the following formula into a cell of your spreadsheet: =SUM(B2, B11). Press "Enter," and the cell will produce the total of the numbers currently filled in cells B2 and B11. If there are no numbers in either cell, the formula will return 0. Keep in mind you can also find the total value of a list of numbers in Excel. To find the SUM of the values in cells B2 through B11, type the following formula into a cell of your spreadsheet: =SUM(B2:B11). Note the colon between both cells, rather than a comma. See how this might look in an Excel spreadsheet for a content marketer, below:
Fast Navigation with Ctrl + Arrow Button
Here is a trick to make fast navigation in Excel many of us know that when we click Ctrl+ any arrow button on the keyboard, you can jump to the edge of the sheet in different directions. But if you want to jump to the bottom line of the data, just click Ctrl+ downward button and easily go to the last cell.
Insert Excel Data Into Word
If you want to insert excel data into words without creating a link-and-embed process, you can paste the data as a graphic. To do this, you can use Word’s paste special tool but you can also use Excel with the following steps:
- Go to the Home tab at the top
- Then select the Copy menu
- You will find the Copy as Picture option
- Using that, you can paste it into words
Use a range name in a formula with =sum( and F3
Suppose you want to use a range name in a formula. For example, you want to sum the Sales range. Enter… =sum( …and then press F3. When you do so, Excel launches the Paste Name dialog. Just choose “Sales” from the list, press the OK button in the dialog, then enter the SUM function’s closing “)” to complete the formula.