Microsoft Excel is an innovative and powerful software program for documentation and data analysis. It’s popular among businesses as it contains spreadsheets that are incredibly visual and effortless to use. Excel’s most common uses in organisations include human resources management, data analysis, performance reporting, data calculation, and many more.
But despite its widespread usage, it’s surprising that users aren’t exploiting Excel’s capabilities optimally. Out of the 100+ features and functionalities, only a handful are known to the layperson. Inspired by this knowledge gap, we created this in-depth post, discussing the little-known Microsoft Excel tips that every business needs to optimise the software solution fully. Let’s get to the details!
Microsoft Excel Tips and Tricks for You
Add Columns and Rows At Once
You may have planned your Excel worksheet’s layout carefully before feeding in data. But that won’t stop something from coming up, requiring you to alter the structure by adding extra columns and rows. Perhaps you want to add data in a different column or insert additional rows in the middle of a set of data, and doing this manually or one by one may be a daunting task. Good news: Excel enables you to achieve that feat hassle-free without starting afresh. You can add columns or rows in these three simple steps:
- Select the same number of pre-existing rows or columns that you wish to add (you can highlight as many rows or columns as you want).
- Command-click for Mac or right-click for PC and select Insert.
- The number of rows you selected will appear above the first row you highlighted, and the columns will also appear after the first column you selected.
Leverage Pivot Tables to Recognize Data
Do you wish to draw more insights from your Excel worksheet data? If so, Pivot Tables have got you covered. They can sum up values and even compare information on your worksheet without tampering with your data.
For example, let’s assume a new regional manager wants to know how many employees are located in each branch. In this case, you can effortlessly create a Pivot Table by going to Data> Pivot Table. Excel will then fill in your Pivot Table automatically, but you can always format the data to change its order with the following options:
- Report filter. This option allows you only to see select rows in your dataset. For instance, you can decide to filter the employees only to show those with specific job titles instead of showing the entire list.
- Value. This section allows you to achieve more with your data. You can effortlessly find the sum, average, max, min, count, and other manipulations from a numerical value.
Use Formulas to Simplify Calculations
Besides helping with complex calculations, Excel also comes in handy in simple arithmetic like addition, multiplication, subtraction, and division. You can use the (+) sign to add, the (-) to subtract, the (x) sign to multiply, and the (÷) to divide.
You can also leverage parentheses to follow the BODMAS rule and ensure that calculations are done in the proper order. For example, instead of grouping (10+10×10) together, in which case the 2nd and 3rd 10 would be multiplied before adding the 1st 10, you can separate them into (10+10)x10 to calculate according to the BODMAS rule.
You can also get the average of the numbers in your cell using the formula =AVERAGE(Cell Range). And if you want the total of the column of numbers, the formula is =SUM(Cell Range).
Transpose Rows and Columns
Moving pre-existing data from columns to rows or vice versa has never been easier with Excel’s transpose trick. Here’s how to go about it:
- Highlight the data you wish to rearrange and copy (Ctrl C).
- Highlight the cell where you wish to transfer the data, then right-click to display the dropdown menu. Proceed to Paste Special right from the menu.
- Select the Transpose checkbox from the Paste Special menu and click OK.
- The highlighted data will automatically rearrange or flip, with columns as rows and rows as columns.
Split Up Text Information Between Columns
Microsoft Excel also empowers you to divide information that’s in one cell into two different cells. For instance, perhaps you want to extract an employee’s company name through their email address. Or maybe you want to separate their first and last names to create your email marketing templates. These are both possible, thanks to Excel.
For starters, you want to highlight the column you wish to separate, then proceed to the Data tab and select Text to Columns. This will prompt a module with additional details to appear. Next, you want to choose either Delimited or Fixed Width. Selecting the first option means splitting up the column based on characters like tabs, commas, and spaces. On the flip side, the second option means you want to use the exact location (where you want the split to occur) for all the columns.
Bonus: Essential Shortcut Keys and Tricks
Besides the Excel tips discussed above, familiarising yourself with the essential shortcut keys also helps improve your experience with the software solution. Most of these shortcuts are applicable in other Microsoft 365 products like PowerPoint, Slides, Word, SharePoint, etc.
- Ctrl-Shift Up/Down Arrow. Use this shortcut to select all the cells above or below the selected cell. However, it’s critical to note that you can only select the cells if they have data and not empty.
- Ctrl Z. If you want to undo an action you’ve just completed on the worksheet, this is the keyboard shortcut to use.
- Ctrl Home. Press this shortcut to move to the A1 cell.
- Ctrl D. press this key to duplicate the data in the cell underneath
- Shift F11. Also known as the insert tab shortcut, Shift F11 enables you to create a new worksheet in your current workbook – Macro1. You may need to add the Fn key for this shortcut to work if you’re a macOS user.
- Ctrl End. Use this shortcut to move to the last cell containing text.
Final Remarks
Hopefully, this post was insightful, and you picked a thing or two about Microsoft Excel to improve your usage and experience. Remember, this is just the tip of the iceberg. There are several other tips and tricks that need a stand-alone post for comprehensive coverage. But if you wish to discover more Microsoft solutions and how they can be helpful to your business, be sure to check out Microsoft 365 Support page! Or contact us.