Microsoft Excel is a powerful yet daunting application. Many have joked that it is the backbone of global accounting and finance, but most of us do not utilise it fully because we do not know how.
At Peak Performer, we have prepared a comprehensive list of tips that will change the way you use Excel.
Let’s start with these easy-to-use formulas. They are useful for carrying out quick operations on large datasets that would be time-consuming otherwise.
READ MORE
The Impact Of Blockchain Technology On Finance
XLOOKUP: XLOOKUP allows you to retrieve data in a spreadsheet by row. You can use it to retrieve a product price using the product ID. It takes three values: the lookup value, the lookup array, and the return array. Using our example, Type the product ID into an empty cell and use it as your lookup value. The values you input into XLOOKUP are the cell containing product ID, the column containing product ID and the column containing product price. Assume that the lookup value is in cell F3, the product ID column is B2 and the price column is C2. The formula will look like this: =XLOOKUP (F3, B2:B15, C2:15).
COUNTIF: This formula allows you to count the number of times a criterion is met. For example, we can count how many products have the same price. It takes in two values the range and the criteria. To use it in our example, type the formula into an empty cell. Then select the range by highlighting the price column and then input the price as the criterion. It will return the number of cells that have the same price. There is no need to waste time counting it out. You can also edit it in the formula bar to any other value. Assume that the price column is C2 and the price we need is #15,000. The formula will look like this: =COUNTIF (C2:C15, #15,000)
IF: ‘IF’ returns a given value if a condition is met. We can use it to compare the cost price and selling price of our products. It takes three values, the condition test, the value-if-true, and the value-if-false. Let’s assume the selling price is B2 and the cost price is C2. Our formula will look like this: =IF(C2>B2, “loss”, “profit”). It beats typing it out for yourself for every product.
REPT: REPT allows you to repeat a text a certain number of times. It is a very powerful function that can easily be modified to give you a bar chart. You might think it’s very complex, but it takes only two values. The text you want to repeat and the number of times you want to repeat it. You can use it to arrange the price of our products in a bar chart. To create the bar chart, we will use the ASCII character 220 as the text we want to repeat. Then we use the price column as the number of times they should be repeated. Let’s assume our price column is B2. Our formula will be =REPT (“220”, B2). To type the ASCII code simply hold down the ALT button while you type it. Drag your cursor across the whole column to apply it to all the prices. Voila, you have a bar chart right in your spreadsheet.
PMT: PMT is a financial formula in Excel that allows you to calculate the payments for a loan with constant payments and a constant interest rate. It takes three required values the principal amount of the loan, the number of years you have to pay it off, and the interest rate of the loan (in percentages). Click enter and you have your annual payment value. For monthly payments, divide the rate by 12 and multiply the year by 12.
UPPER, LOWER, and PROPER: These formulas can be applied to cells to change the case of the letters. UPPER converts text to uppercase, LOWER converts text to lowercase and PROPER starts every word in a string with an uppercase. They can take in a range of cells as their solitary input.
Those are six formulas that will change the way you use Excel.
These are some easy-to-use tools that will allow your data to become easy to explain and use. They allow you to extract meaningful information from your data and make it accessible to more people. They will also change the way you interact with data.
Pivot Tables: This is one of the best-kept secrets in Excel. It can increase your productivity immensely. Say you have a table for your spending, that contains the amounts you spent on recurring products for a whole year. A pivot table can sort your spending based on products, based on the month and give you the total for each product for the year. How do you use this awesome tool? Simply click a cell on your table, click Insert, and click recommended pivot tables. Then select whether you want the table in a new sheet or within the same sheet.
Quick Analysis: REPT is good for showing off, Quick Analysis however is the easiest way to create charts in Excel. It allows you to create all sorts of charts including pie charts and histograms. To use it first, highlight your table and then click on the menu box that appears in the corner. Choose the chart you want to create and click OK. Excel doesn’t seem so mighty, does it?
Data Tables: A data table is very different from inputting values into random cells. Data Tables allow you to organise data based on the headers of columns. It also allows more filters than the regular spreadsheets in excel. Data Tables essentially set the values in a cell to a particular type based on the data inputted in the cells. To use it, highlight the table you want to manage, click on the Insert Tab and then click table.
We also have some tricks to help you increase your productivity when using Excel. These tricks help with quick solutions and help you avoid repetitive tasks.
Flash Fill: Flash Fill allows Excel to complete data for you when it senses a pattern. For example, if you have two columns filled with the first names and last names of employees. You can use Flash Fill to complete the third column: full name. Start by typing the full names in the first two rows of the Last name column. Then click on data and click Flash Fill. Excel will input the full names for the remaining rows. It gives you access to Table Tools which comes with a different set of actions that can be applied to the table.
Values Starting with Zero: Have you typed in a phone number in Excel only for the leading zero to be deleted? The quick way to rectify this is to add a quote mark in front of the number in the formula bar.
Applying Formulas to Multiple Cells: You don’t have to copy a formula from one cell to other cells manually. You can do this instead. Apply the formula to one cell, hover at the right corner of the cell and then drag the cross sign that appears down the column.
Sum Up a Column: To quickly sum up the values in a column, in the last cell click (Alt + =) and then the sum would appear in that cell.
Delete Duplicates: To delete duplicates, select the range of data that contains duplicates. Click on the Data Tab and then click on the Remove Duplicates button. Check whether your data has headers, and then click on OK.
Transpose Data: This allows you to change the orientation of your data. That means you can shift them from vertical to horizontal. Simply copy the data, right-clicks to see the Paste Special button and then click on Transpose. This will switch the data in the columns to rows and vice versa.
Finally, here are some useful keyboard shortcuts. Keyboard shortcuts turn you into a power user and allow you to flow seamlessly while using Excel.
- Ctrl + End: will take you to the last cell of your data
- Ctrl + Home: will take you to the first cell of your data
- Ctrl + Space: will select the column of your active cells
- Shift + Space: will select the row of your active cells
Excel Spreadsheets become easy to use and very efficient when you know the right tricks and tips to get the most out of them. Hope you try out these tricks next time you have that huge spreadsheet project.
