Work Smarter with Microsoft Excel

While there are many business software applications that can streamline your daily work, investing the time to learn how to work smarter with Microsoft Excel will pay dividends in the future. In this blog we will walk through a few key features of Microsoft Excel that will expand your skill set and make your work life a little easier.

SUM and AutoSum

SUM is a function that allows you to add individual ranges, cell references, values, or all three. Here’s how:
1. Select a cell next to the values you want to add.
2. Type =SUM(.
3. Select or highlight the cells you want to add.
4. Type ) then press Enter.

Another way to add numbers in a row or column is to click on the AutoSum button on the Home tab, select the cells you want to add, then press the Enter key.

Click HERE for more details from Microsoft’s support site on these Excel features across Windows, MacOS, Android and Web.

COUNT and COUNTA

The COUNT function lets you determine how many cells in a spreadsheet contain numbers. To use this function, click on an empty cell and type in the formula =COUNT(<first cell>: <last cell>).The COUNT function only looks at numbers and will ignore text. If you use COUNT in a column full of text, it will return a value of zero.

For more details, click HERE to see examples from Microsoft’s support site.

The COUNTA function is similar to COUNT, but it looks at cells containing text. So if you want to count how many cells in a column contain text, simply type this formula into an empty cell: =COUNTA(<first cell>:<last cell>).

For more details, click HERE to see examples from Microsoft’s support site.

Pivot tables

For a long time, pivot tables were big mystery to many users, and stopped job interviewees in their tracks. Keep reading for a break down of why this function is useful and how it actually works.

To clear things up, a pivot table is just a way of presenting detailed data in a simpler, more user-friendly format.  If you need to summarize large data sets, analyze their totals and make it understandable to a non-technical user base, pivot tables can help get your point across.

A real world scenario for using a pivot table? A Sales Director wants to visualize how their team is performing against their weekly, monthly and quarterly goals. With a pivot table, data will be separated into groups within categories to get results without wasting time manipulating a pile of values from simple Excel functions and formulas.

Here are the basic steps to create a pivot table:

1. Select the data on the spreadsheet you want to include in the table.
2. On the Excel ribbon go to Insert > Tables > Pivot table.
3. A Create PivotTable dialog box will appear, with the data range you selected. You can manually edit the data range for your needs. This dialog box will also give you the option to create the pivot table in a new or in an existing worksheet.
4. Once you’re done editing, click OK.
5. After clicking OK, a blank table will appear along with a PivotTable Fields dialog box. In this box, you can select which fields in the data range you selected earlier will appear in the table.

Click HERE for a much more in depth review on the Microsoft support site of how to create and use pivot tables with your data in Windows, MacOS and Web environments.

Filter

Excel’s filtering function does what you think it would do…it allows you to un-visualize (hide) data you don’t need to see in a particular view. In a huge spreadsheet, you might want to focus on only some of the columns or some of the rows, depending on the values they contain.

In the real world, you might be a college professor and want to easily see how many students achieved a grade of 60 or above on the Organic Chemistry mid-term exam. Excel can filter out the smarty-pants students who passed, so you can focus on the group who will need to take your class over again, and study harder, to get a better grade.

Here are the steps to use filtering in Excel:

1. Highlight the data set you want to filter. This should include the headers of the chosen columns.
2. On the Excel ribbon, go to Data > Filter. A dropdown arrow will appear on the headers of the column you’ve selected.
3. You can use the dropdown menu to filter the data by text or number depending on the conditions you set.

For more details on how to use this function, click HERE to see a Microsoft support site article.

Contact Beringer Today!

Mastering the  features described above will significantly improve your Excel skills and your productivity. If you have questions on how to use advanced features in Excel, or in any Microsoft product, rest assured our team can help. Get in touch today!

Beringer Technology Group, a leading Microsoft Gold Certified Partner specializing in Microsoft Dynamics 365 and CRM for Distribution also provides expert Managed IT Services, Backup and Disaster Recovery, Cloud Based Computing, Email Security Implementation and Training,  Unified Communication Solutions, and Cybersecurity Risk Assessment.