Chat with us!
Become a true Excel professional with these 8 Excel tips
Do you regularly work with Excel and are you curious as to whether you can perform certain tasks faster? Then keep reading. Microsoft Excel is packed with useful tricks, functions and formulas that make your life a lot easier. Discover 8 useful tips in this article that will make you a true Excel professional.
Tip 1 - Flash Fill
Do you work in a document where the layout of almost every cell is different? For example, one cell contains the name "PETER", the next cell says "Eric" and the next state "john". Although it is probably clear which name is in which cell, it does not look professional. An option is to convert all this names manually to the correct format. But luckily this is much easier to do with Flash Fill.
First select the complete column and paste it in a new worksheet. Then, in the next column, type the first 3 names or words in the format that you want them to be written. Excel then indicates that it can fill in the rest of the column and correct the formatting. Press the Ctrl + E key combination to have the action performed.
Tip 2 - Select all
It is regularly necessary to select everything within an Excel file. You can do this by using the mouse and dragging through the entire file. As you will probably understand, this is not the most convenient method. The first and (much) easier solution is to use the Ctrl + A key combination. The second quick solution is by clicking on the triangle that is at the top left, next to the A and above the 1. This allows you to select the total content of the Excel file with one click.
Tip 3 - Secure rows and columns
Do you have a lot of data in Excel? Then there is a good chance that you sometimes have to scroll considerably to see the data at the bottom of the worksheet. It is very annoying then, when you can no longer read what the title of a column was. Fortunately there is a solution for this. It is possible to fix columns or rows. No matter how far you scroll down or to the right, it always remains visible what the data is about in the different cells.
First click on the row or column that you want to lock. Click on "View" in the ribbon. Via the option "Freeze panes" you can fix these rows or columns. You can cancel this action via "Unfreeze panes”.
Tip 4 - Add multiple rows or columns
Within the Excel file it often happens that you have to add new rows or columns. If you only need to add one row or column, you can do this quickly with your mouse. However, if you have to add dozens or even hundreds of rows, you should perform the same action hundreds of times.
This can of course be faster and easier. First, simply select how many rows or columns to add. Then press the right mouse button and click on "Insert." You can easily add a large number of rows or columns within a few seconds.
Tip 5 - Remove duplicate notations
Sometimes there is duplicate data in an Excel file. Imagine that you have collected the registration data of customers in an Excel-form and some customers did (accidentally) register themselves twice. Now you have thousands of e-mail addresses in the file, with a number of duplicate e-mail addresses. Of course you don't want to count these people twice. You can prevent this problem with a useful Excel function.
First select the row or column that you want to check for duplicate notations. Then go to Data> Remove duplicate values. Next, the duplicate data is displayed. Of course you can choose to delete this data.
Tip 6 - Useful calculations
You can use Excel to perform simple and complex calculations. A calculation always starts with an "=" sign and can be entered in an empty cell:
To perform an add-calculation you use the "+" sign. For example, do you want to add cells A1, A2 and A3 together? Then enter the formula =A1+A2+A3. Subtracting is done by using the "-" sign.
To multiply and divide specific cells, use the "*" or "/" sign respectively. The formula will then look, for example, as follows: =A1*A2*A3. It is of course also possible to combine these different calculation methods in one formula.
Tip 7 - IF-formula
The next tip for Excel is the IF-formula. You can use this function as a user for various reasons. Suppose you check the inventory of your store in Excel. You always order a product when there are less than 40 products in stock. The IF-formula can help you to make this clear within seconds.
First create a new column in the Excel worksheet. For example, suppose you have the amount of products in column B. In the first cell of a new column, enter the formula =IF(B1<40;"Order";"In stock"). When the stock is lower than 40, it is automatically stated that the product must be ordered. Then press the green square at the bottom right of the cell and drag it down. The formula is then applied to the entire column.
Tip 8 - Combining cells
In many cases you have 'loose' data in individual cells. Imagine once again that you had people registered via a form with multiple fields, including first name, last name and e-mail address. You will then find out that you prefer to have the first name and last name in one cell. You can easily combine these with the & formula.
Suppose the first name is in cell A1 and the last name is in B1. First select an empty cell (for example C1). Then enter the formula =A1&" "&B1. The two cells are then simply merged into one cell (C1). By using the green square in the cell you can apply this formula again for the complete column.
Get started with Excel
Thanks to these useful Excel tricks, you can work even faster and more efficiently in this world-famous spreadsheet program! Do you want to get started with these handy Excel tips and are you still looking for an Excel license? Then choose the reliable software licenses from SoftwareLicense4U. We offer 100% original licenses at a extremely competitive price. With our Office 2019 license you not only get access to Excel, you will also receive popular applications such as Word, Outlook and PowerPoint. Quickly view our licenses and get started quickly with Excel!