Work smarter in Excel with these 10 tips

11 Oct Work smarter in Excel with these 10 tips

Microsoft Excel has been a mainstay in the Microsoft 365 suite of products for over 30 years.  It is a powerful program that can be used for everything from simple graphs and charts to full fledged traffic and leads calculators. The primary limit to what can be done within Excel is the users’ knowledge of the product and its features. In this article we will discuss our top 10 tips to help you work smarter and excel at Excel.

Group Rows and Columns

When working within a large worksheet with many rows and columns, it is essential to keep the document well structured and organised. The group function helps to keep worksheets organised and allows users to easily expand and contract sections within the worksheet. In order to group rows or columns, select the rows or columns you want to group, go to the Data tab, select Group and select Group again. Now it is possible to click the minus (-) button in the margin to collapse the rows or columns. To expand them again, press the plus (+) button in the margin.

Perform Quick Analysis

The Quick Analysis allows users to quickly calculate totals, insert tables, apply conditional formatting, and much more. To use the tool, select the desired range of cells, including the titles, and click the Quick Analysis button to the bottom right of the selected cells. This will open a pop-up that has formatting, charts, totals, tables and sparklines that can be created from the data set.

Flash Fill Data

Flash Fill is a feature of Excel that can fill a column based on the data in the first cell of the column. Some examples may include combining first names and last names to form email addresses, reformatting phone numbers to include area codes, or extracting sections of other columns. To use Flash Fill, type the example in the first column, then go to the Data tab and select Flash Fill.

Conditional Formatting

Conditional formatting allows users to automatically change the colour of a cell based on certain conditions. This helps with being able to quickly and easily view important information. To apply conditional formatting, select the cells you want to apply conditional formatting to, then select Conditional Formatting from the Home tab and select the appropriate logic. This will open a pop-up to provide more information and to alter how the cells will be formatted.

Hide a Sheet

Often when creating a workbook, there will be a sheet containing formulas or data for reference in other sheets. To help with organisation, it is possible to hide these sheets, whilst still being able to reference the contents. In order to hide a sheet, right click the sheet and select hide. If you need to view it again, go to the View tab, click Unhide and select the sheet you want to unhide.

Pivot Tables

Pivot Tables are one of the most powerful features in Excel that allows users to extract the significant information from a large, complex data set. To create a Pivot Table, highlight any cell within the data set and click PivotTable in the Insert tab. This will open a pop-up that allows you to select the data range, and where the PivotTable will be placed. After you click OK, the PivotTables Fields pane appears allowing you to select which fields will be included in the table. Once the table has been created, the data can be filtered and sorted.

AutoFill Data Sets

When using Excel, if you find yourself typing out a repetitive series of dates, times or series, AutoFill can help you save time. To use this feature, type the first cell of the series and move the cursor to the bottom right of the cell, when it turns into a plus sign, click and drag the cells you want to fill. Excel will then fill the series using the pattern you started.

Remove Duplicates

When dealing with large datasets or after combining multiple data sets, it is difficult to avoid duplicates. Rather than going through hundreds or thousands of cells looking for the same entries, Excel makes it easy to quickly remove all duplicates. To remove duplicates, highlight the desired row or column and select Remove Duplicates from the Data tab. A pop-up will appear to confirm the data set is correct, select Remove Duplicates and all duplicate data will be deleted.

Add Multiple Rows or Columns

Adding rows or columns one by one is a length and frustrating process. Thankfully, Excel has a feature to make this significantly easier. To add multiple rows or columns, highlight the number of rows or columns you want to add. Then, right click and select Insert.

Make Use of Filters

Filters make it easier to organise a worksheet and quicker to find key information. To add filters to a data set, highlight any cell within the data set, click Filter in the Data tab and arrows will appear in the column header. Click these arrows and options for filtering will appear. To clear filters, go to the Data tab and select Clear.

With these tips you will be able to be more productive and work smarter in Excel. If you want to find out more about how to get the most out of other Microsoft applications, get in touch today!