Consolidate weather and accident data sets into a single Excel workbook to improve shareability, minimize the risk of broken data connections via lookup functions, and reduce version control issues. Use Excel’s “Move or Copy” options to quickly and easily transfer data across workbooks and into new tabs.
- [Voiceover] Linking data across separate Excel…workbooks and files can quickly lead to headaches…with shareability, broken connections,…and version control issues.…I'll demonstrate how to avoid those issues…by quickly consolidating data tabs…into one single workbook.…So I have two Excel files open,…04_01_Weather…and 04_01_Accident,…and what I'd like to do is move the three tabs…from my Weather file into the Accident file…so that I can use those fields for deeper analysis.…
So in my Weather file, I'm going to Shift click…to select all three tabs, right click and use…the Move or Copy option.…So this allows me to move selected sheets…to a new book, in this case I'll select the Accident book,…and I want to move those to the end of the workbook.…Now I have the option to preserve a copy of these sheets…in its own separate file or just move them entirely.…In this case, I would just like to move them over…so I'll leave this box unchecked.…
When I press Enter, it takes me to my Accident file,…and as you can see, those three Weather tabs…
Using conditional statements and text functions like LEFT, MID, RIGHT, and CONCATENATE, you'll learn to standardize data sets from the NCDC and create new dimensions. Then find out how to use VLOOKUP to join the data, and use COUNTIF statements to analyze trends. Once the historical data is in place, Chris shows how to use Excel's powerful WEBSERVICE and FILTERXML functions to tap directly into Weather Underground's API, which provides real-time weather information for any location and allows you to estimate accident rates based on current conditions.
Last but not least, you'll learn how to build custom charts to show the expected accident risk based on real-time inputs, and walk through some additional opportunities for deeper analysis.
- Standardizing data
- Creating new data dimensions fields
- Joining data
- Analyzing trends in the data with COUNTIF
- Creating an API feed with WEBSERVICE
- Extracting components with FILTERXML
- Visualizing the findings in a custom combo chart
- Applying your new skill set