10 useful tips and tricks for Excel

In Microsoft Excel you can make a mailing list, keep an accounting, and of course make all kinds of other calculations. The basics of Excel are not that complicated at all. With these ten handy tips for Excel you can use the classic program even more conveniently and quickly.

Tip 01: Select all

We'll start with a fairly simple trick, but one that's very useful. If you want to select everything in a spreadsheet, you can use the key combination Ctrl+A. The problem with that key combination is that it doesn't always select all the data from your worksheet in Excel. If you have a series of data in your spreadsheet followed by an empty column and you press Ctrl+A, often only the block with the data in which the mouse pointer is located is selected. Press Ctrl+A again and you will have all the data. And if you are a bit clumsy (and we have experience with that) and if you press the A one too many times (or accidentally press another key), everything will be replaced by that letter A. Fortunately, that can be restored with the function Undo. Does this happen to you often now and want to have another way to select all the data from your worksheet? Then click at the top left on the intersection above row 1 and to the left of cell A on the triangle and everything is selected.

Tip 02: Add multiple rows

Adding a row or column in Excel is fairly easy. You can in the Home tab in the ribbon under the heading Cells click on Insert, and choose that leaf rows or Leaf Columns. Another option is to right click on a cell and then choose Insert / whole row or Whole Column. If you choose a row, it will be inserted above the row where your mouse pointer is located, if you choose a column it will be added to the left of the column where your mouse pointer is located. Do you not want to insert just one row, but about twenty? Then this is of course cumbersome. Fortunately, Microsoft has thought of this. Let's say you want to add twenty rows, then click a row number on the left and drag the mouse down until you have twenty rows selected. Right click on this selection and choose Insert. Excel now automatically understands that you want to add rows, and adds exactly as many rows as you selected. Of course it works that way with columns as well.

Tip 03: Flash Fill

Suppose you have a document full of names and addresses, but each value is written completely differently. One name has a capital letter, the other does not. One zip code has a space between the numbers and the letters, but the other has everything glued together. You can of course choose to leave everything as it is or to adjust everything manually. But what if we tell you that it can also be much simpler? Excel can easily rectify many things for you with the Flash Fill function. Take the rows with first names, assuming that first and last names are in a separate column. Select the entire column with first names, create a new worksheet in Excel and paste this column into that worksheet. Now type in the column right next to the first three first names in the way they should be written (ie with a capital letter). As you type, Excel already indicates that it can correct the rest of the column. If you press Enter during this preview, this will immediately be executed on the entire column. If you want to initiate the action yourself later, you can do that with Ctrl+E.

Calculator

Although Excel is very good at calculating things, it can sometimes be faster and more pleasant to calculate something in the Windows Calculator (and then do something with that value within Excel). For example, if you don't want to accidentally change your current worksheet. Some keyboards have a hotkey that calls up the Windows Calculator, but that's not true for all keyboards. Did you know that you can easily add a shortcut to the calculator within Excel? To do this, click the down arrow next to the Save, Undo, and Redo icons at the very top, then More assignments in the menu that expands. Now select the option Calculator in the left pane (if you don't see it, choose from the drop-down menu at the top first for All assignments) and add it to the right pane. The calculator is now added as an icon in the Quick Launch toolbar. This is of course not only reserved for the calculator, in principle you can add all functions that exist within Excel here.

Tip 04: Freeze row/column

When you're working with a small amount of data in Excel, it's pretty easy to see which column or row you're working in. Usually you have in the top and leftmost cells of a worksheet which data is in that row/column. But when there's so much data that you have to scroll to see it all, it's pretty annoying when you can't see which row or column a particular cell belongs to. You can easily solve this by pinning a row or column. When you do this, the row or column in question will not move as you scroll in the document, so you can always see what a particular cell belongs to. The function is very simple, but sometimes causes confusion because sometimes half the spreadsheet is suddenly blocked. It has everything to do with where you place the mouse pointer. To freeze the first row and first column, click cell B2. Now when you click in the ribbon on Image and then Block / Block Titles, then everything above row 2 and everything to the left of cell B (that is, in this case row 1 and column A) will be locked. You can disconnect via Block / Unlock Title.

Tip 05: Add up super fast

You can make the most complicated calculations with Excel, but sometimes you just want to know what the sum of a number of values ​​in your spreadsheet is. Theoretically, of course, you can neatly create a formula for this, but because it is such a commonly used function, Excel has made that a lot easier. To have a range of values ​​in your spreadsheet add up, all you need to do is click the cell below or next to the range of values ​​you want to add together, then press Alt+=. The SUM function is now automatically applied to the range of values ​​and the result is displayed in the active cell. If you don't like keyboard shortcuts, you can also click the button AutoSum in the tab Start under the heading To process. If you click on the arrow right next to AutoSum, you will find a number of other quick functions there. For example, to calculate the average of a series of values, or to indicate how many numbers the series of data contains (rather than adding those values ​​together).