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!
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:
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:
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:
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.
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.