Microsoft Excel – Tips & Shortcuts to Help with Productivity

Microsoft Excel is a data analysis and data validation spreadsheet software. It's part of the Microsoft 365 Office suite of products and helps users to store and track critical data.

Many professionals use Excel to keep track of important information within their business, whether that be spreadsheets of financial information, budgets, sales figures, or lists of customer details.

Most people working in business, whether it be finance, sales, marketing or HR will have used Excel at some point to store or review their data.

Many of us have a basic understanding of Excel but if you’re not an expert on formulas and datasets then it can sometimes be a long-winded process to input and generate the data you need.

In this week’s newsletter, I dive into some helpful tips to help increase your productivity when using Excel, helping you save valuable time!

How to Use Formulas

There are a wide variety of?formulas?that you can use to help clarify your data. If you’re just getting used to using Excel or you haven’t tried formulas before, these can help you take the first step with saving time when calculating numbers.

• Equal sign:?Before you begin creating a formula, you’ll need to write an equal sign?(=)?in the cell where you want the result to appear.
• Addition: To add the values of two or more cells, use the?+?sign. Example:?=B4+E8.
• Subtraction: To subtract the values of two or more cells, use the?-?sign. Example:?=B4-E8.
• Division: To divide the values of two or more cells, use the?/?sign. Example:?=B4/E8.
• Multiplication: To multiply the values of two or more cells, use the??sign. Example:?=B4E8.

If you would like to then go one step further and use these together, you can create a formula that does all of the above; adds, subtracts, divides and multiplies all in one cell. Example:?=(B4-E8)/((A7+D4)*2).

Create Charts and Graphs

Not only can you store data in Excel, but you can create visuals of this data in formats such as line graphs and pie charts. To do this, follow these steps:

• Select the datasets in multiple cells
• Click ‘Insert’ followed by ‘Recommended Charts’
• In the ‘Recommended Charts’ window, you will find the chart types that Excel recommends, or you can click ‘All Charts’ to see other options.
• Pick the chart type you would like and click on ‘OK’
• To customise your charts, use Chart Styles, Chart Elements and Chart Filters. You can add colours and data labels.

You can save chart templates to use again in the future by using the ‘Save as a Template’ feature.

How to Use Functions

To automate some of the calculations and tasks you would use in a formula, you can do this using functions.

·???????? SUM: this function automatically adds together a range of selected numbers or cells. To complete a sum, you would input the starting cell and the final cell with a colon in between:?SUM(Cell1:Cell2). Example:?=SUM(B4:B38).

·???????? AVERAGE: this function averages out the values of a range of cells. Similarly to the SUM function, you would input the starting cell and the final cell with a colon in between:?AVERAGE(Cell1:Cell2).?Example:?=AVERAGE(B4:B38).

Filter Your Data

When you have large amounts of data, say for example, sales figures, but you don’t want to have to view all the information, only certain criteria, you can choose to filter. This allows you to look at certain rows at one time.

To add a filter, click the “Data” tab and select "Filter". By clicking the arrow next to the column headers you’ll be able to choose whether you want your data to be organised in ascending or descending order, plus which specific rows you want to show.

Remove Duplicated Data

If you have for example, a large database of contacts but there are many duplicate entries which need removing, instead of having to review all entries one by one, you can remove duplicates easily. Here is how…

Highlight the row or column that you want to remove duplicates of, go to the Data tab, then Data Tools and select “Remove Duplicates”. A pop-up will appear to confirm which data you want to work with. Select “Remove Duplicates”, and it is done!

?

For more information about Microsoft 365 and the business packages available, please contact me by emailing [email protected] or call us on 0161 388 8188.