Join Nate Makdad for an in-depth discussion in this video Using the exercise files, part of Creating Interactive Dashboards in Excel 2013.
- In this course, we've provided free exercise files for all Lynda.com members, and while you can download these files and use them throughout the course I also wanted to show you where we got the data that we'll be using throughout this course, in case you wanted to pull it down yourself. So you can access the New York City Open Jobs Data by going to nycopendata.socrata.com. And from there click on the business link or look at the business data and then there's a link here for New York City jobs. Once we click on this, it brings up the web-based version and we just want to download or export, so there's a tab over here for export.
And then I'm going to download as .xlsx. Once it downloads and opens, you'll see this yellow bar and this may come up later on in the course as well, as you're opening up different versions of the template. Particularly once we get to the macro-enabled template, where you may need to enable the content or enable editing. So always make sure, if you've got this yellow bar at the top, that you enable content or enable editing. So now this is how we downloaded the data, though if you prefer to work with the exercise files you can also work with the exercise files by jumping in at any point by going to the chapter and then the movie number we've provided under each chapter, and movie number kind of jump in files, so you can navigate right to that particular movie and see the work that we've done up until that point.
Within chapter 2, you'll see different versions of the access database being developed. This is to help show how I've built the backend of the report and to illustrate how we'll manage our data. Starting in chapter 4, you'll see different versions of the open jobs template and this is really just to show the progress that we're taking each step along the way. So this should allow you to jump in at any point in time and just pick up right from that movie. I've also added a few extra components not covered in the video, there are some additional calculated fields created in the final access database that will first appear in chapter 4.1.
These follow the same ideas covered around creating variables to enhance our data. And if you're curious to see how they were created, you can jump into the access file and take a look at a text string evaluation, and a date comparison formula. I've also done some pre-work in chapter 2, movie 4. Where we created some work locations and mapped those to specific New York City boroughs. So this is just an extra table that was built for the purposes of illustrating how we can create reference tables in Access.
Finally, there's an extra folder that contains the final projects. So the final database as well as the final Excel template. So you'll be able to compare your files with mine. Alright, so let's get started.
- Defining your requirements
- Setting up data
- Adding reference tables
- Creating hierarchy in the layout
- Choosing color and typography
- Importing data
- Inserting slicers
- Recording a macro
- Editing VBA code
- Improving the user experience
Skill Level Intermediate
Q: Are the exercise files corrupt? When I try to open them, I get a message saying "The file is corrupt and cannot be opened."
<div>A: Often .xlsm file (embedded macros) that are downloaded from the web trigger security settings in Excel. Here is a Microsoft forum with information on disabling these setting: </div><div> </div><div><a target="_blank" href="http://answers.microsoft.com/en-us/office/forum/office_2010-excel/the-file-is-corrupt-and-cannot-be-opened-error-on/93af59c1-946c-4f5f-83c1-bd6f58dbd94f?auth=1 ">http://answers.microsoft.com/en-us/office/forum/office_2010-excel/the-file-is-corrupt-and-cannot-be-opened-error-on/93af59c1-946c-4f5f-83c1-bd6f58dbd94f?auth=1 </a></div><div> </div>