Cell formatting in Excel isn’t just about making spreadsheets look pretty; it’s about presenting and communicating data in a clear, powerful, and intuitive way. Learn how to use fill settings, custom borders, font styles, gridline settings, and custom cell formatting rules to design a clean and polished dashboard.
- [Voiceover] Formatting in Excel…isn't just about making things look pretty.…It's about presenting and communicating data…clearly and effectively.…I'll show you how to use fill settings, custom borders,…font styles, and custom cell formatting rules…to design a clean and polished dashboard.…So open up the zero six zero three exercise file,…and select the weather dashboard tab.…What I have here are the components of a dashboard,…but in a very raw state.…So I'll show you how to use a number…of formatting techniques to help polish things up.…
First things first, I'd like to hide the gridlines,…just to minimize distractions and create a clean…and simple look.…So I'll go into the View tab…and simply uncheck that box next to Gridlines,…to make them invisible.…Next, I just need to adjust the chart bounds a little bit.…Really the only reason is that…they overlap a number of cells,…which makes those underlying cells…a bit difficult to select.…So I'm just going to drag the chart bounds to the left,…and give me a bit more room to work with.…
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