1.2 4-Steps to Data Analysis and Management
- Importing data – Managing data begins with accessing the data, whether it is part of an Excel workbook or originally from another source such as SQL or Microsoft Access. That data can be imported into Excel easily using the Get & Transform Data section of the Data tab.
- Check and clean data for conformity to Excel standards (best practices). Are there blank columns, rows, or cells? This should be corrected now. The Power Query Editor could be used as an option.
- Sort the data using the various sort tools available on the Ribbon.
- Add formulas/functions as desired. Creating charts from the data can assist in seeing patterns/trends.
Let’s begin with the first step; importing data.
1st Step: Use the Ribbon to import data into Excel from various sources including (Data tab – Get & Transform Data section):
- Comma Separated Values text files
- Microsoft Access tables
- Other Excel Workbooks
2nd Step: Basic Standards for Structuring and Cleaning Your Data in Lists, Ranges, Tables
In should be understood that none of these standards are required in Excel. These suggested standards should be followed when creating/storing data in Excel:
- No empty rows
- No empty columns
- Use Excel functions to appropriately edit your data
- The Power Query Editor is a viable option to use when cleaning and editing data.
3rd Step: Use the Ribbon to select various sorting tools. (Data tab – Sort & Filter section)
- Sorting icons on the Ribbon in multiple locations
- Sorting buttons in tables
- Sorting A to Z or Z to A
- Sort by cell distinction: color, icon, etc.
4th Step: Use formulas/functions as desired.
- Understand that you can use functions in the other steps also.
- Adding functions at the end of the 4-step process is better in terms of the functions executing properly with no surprise, unexpected, or erroneous data results.