1. Managing and formatting data
Now that we know how to work with data within tables, we can look at best practices for managing and formatting your data in Excel.
2. Ways to manage data
Let's start with managing data. One of the strengths of Excel is the ability to store and manage data in a tabular layout. All sheets have rows and columns, so this can be done easily, even when manually entering raw data. Here is an example of a small subset of data of math test results for ten students.
Features available in Excel to further improve the data management process include creating named ranges, creating subtotals, and even data validation processes.
3. Named ranges
One way to better manage your data is to name a range of cells in a column or row. By naming a range in your data, it becomes easier to reference that range in formulas in different areas of your spreadsheet, saving you a lot of time. These named ranges can then be edited, created, or even deleted in the Name Manager feature in Excel.
4. Subtotals
Even when preparing data, performing calculations and aggregations for numerical variables can be important to spot any obvious errors.
Excel provides a Subtotal feature that allows you to use more common aggregation methods, like sum, count, or even average, across a specific column in your data. This feature creates a subtotal for the different data points in your chosen column and even adds a grand total for the whole dataset.
5. Validating data
Our final feature for managing data in Excel we will cover is data validation. This is especially useful when working with shared workbooks and collaborating with others. Data validation allows you to control the data or values entered into a cell. This includes the type of data that can be entered.
You can add instructions that appear for the user to know what they can enter while also customizing the error message that will appear if an invalid value is entered.
6. Formatting data
Let's move on to formatting. Excel has various options for formatting sheets, cells, and values. For example, you can format the font size in a cell, format sheets to include borders around all cells, and format values to match the data type, such as currency or dates.
But there is even more, we can do. As well as creating custom formats for all the different data types displayed in Excel, we can also format columns and rows based on specific conditions using the conditional formatting feature.
7. Custom formats
Through the Format Cells menu, Excel offers a wide range of different format types for all the data types or variable categories. It also has a Custom section to set your desired formats.
8. Conditional formatting
We can fill cells with different colors using the Font settings. But we can go one step further when preparing our data and utilizing the Conditional Formatting feature in Excel. This feature allows you to format and highlight cells that meet specific conditions that you can set. This way, you can make patterns or trends more apparent.
Excel allows you to add data bars and icons or use different color scales to highlight the desired information. You can create, edit, and clear the conditional formatting rules using the Manage Rules feature in Excel.
9. Let's practice!
It's time to put this newly acquired knowledge to some good use.