Data can be daunting. Using Excel can be even scarier. Microsoft Excel is a powerful tool for analyzing large amounts of data, but you really need to understand the application to get the most out of it. Here are a few considerations and helpful hints to improve your proficiency in Excel and get the most out of your data.
- When to use Excel
Excel is not the only way to present data. Sometimes we display content using Excel when it’s not the best tool to communicate the data to our audience. If the majority of your data is qualitative (i.e. short paragraphs, open-ended survey responses, personal information that isn’t tied to statistical data, etc.) it might be more effective to present the data in a table in Microsoft Word or a Word Cloud (like Wordle), which shows the most common words in a set of text. Furthermore, if you want feedback on your data, the formatting in Excel could be a bit too complex for inexperienced users to manipulate. However, if your data is statistical in nature, avoid using tables in Microsoft Word, which aren’t quite built for analysis.
- Be Careful When Adding Text
Excel is easier to maneuver if the data is numerical. However, if you have to add text to your spreadsheet, keep the language consistent and simple. For example, if you’re using Excel to create an Action List, either use the word “Complete” or “Done” for finished deliverables, but not both. It will be harder to filter complete vs. incomplete tasks if you use different words each time. Consistent language helps the program compare apples to apples.
- Excel Formulas
Nothing can intimidate a non-math-minded person more than having to figure out a formula in Excel. Before you start, make sure your data is clean, consistent and as quantitative as possible (you can use numbers to represent words). There are hundreds of resources and videos online to help you understand and apply formulas (including Microsoft Office Support). Some websites even have template spreadsheets with the formulas already filled in!
Here are a few videos for commonly used Excel formulas:
- Sum Function (add up the total for a certain range of data)
- IF Function (to compare data against a certain metric)
- Count Function (see how often a numerical value appears)
- CountIf Function (how often a value matches a certain criteria, such as a name or survey answer)
Other Excel “Hacks”
- Use “Alt-Enter” instead of the “Enter” or “Shift” to add a new line of text to a cell
- COLOUR-CODE: Use the “Conditional Formatting” function to colour-code your Action List.
- FREEZE PANES: To scroll through a long list of data while keeping several rows or columns of data visible, select the row/column after the ones you wish to keep in view, and then select “Freeze Panes”.
- COPY AND PASTE: If you want to copy and paste content from Word or another application into only one cell, make sure you double-click directly into the cell before you select “Paste”. Otherwise the content will be pasted with the hidden formatting, including paragraph breaks which split your content into multiple cells.
- PRINT YOUR DATA: Make use of “Page Setup” to make sure your spreadsheet prints in a logical and legible way. You can select exactly how much content appears on each page using the “Set Print Area” and “Insert Page Break” buttons.
With these suggestions, some experimentation and a bit of patience, you’ll be an Excel Pro in no time!