Join Curt Frye for an in-depth discussion in this video An overview of database creation in Excel, part of Database Clinic: MS Excel.
- [Instructor] As with most of the data sets we'll be working with, you will need to download this file from the location shown earlier or refer to the PDF handout in the exercise files. I've already downloaded that file into the Exercise Files folder for my own convenience. The first task in creating any data analysis solution is to be sure that you can import your data into the tool. In this movie, I'll show how the size of an Excel worksheet constrains what you can do, but then describe how you can get around that limitation if you have the correct version of Office.
I have opened the Exercise Files folder to Chapter02 and I'll go ahead and double click the file that's here, CA_DRU, projected 2010 through 2060, and, as you can see from the file type, it is a Microsoft Excel Comma Separated Values File. So, I'll go ahead and double click and Excel fires up and the program tries to open this file. So, I see we're at six percent, 12%, 19%. This is a very large file, almost 250 megabytes. And I get an error saying that the file was not loaded completely.
I'll click OK to clear this warning dialog box and I see that I have some data and if I press Control+End, I go down to the last row and I see that I have some information, but there's obviously more. So, it looks like I downloaded approximately a third of the file. How do we get around this limitation? I'll switch to PowerPoint and explain what we need to do. As we saw earlier, we couldn't download the entire data set into a single Excel worksheet. Excel worksheets can hold just over 1,000,000 rows of data, but we had substantially more than that.
So, what do we do? First thing we need to do is to install PowerPivot through the Excel Options dialog box. Once PowerPivot is installed, and it's not available on every version of Office 2016, so you'll have to make sure you have the right one, we can create a data connection to the Comma Separated Value File we opened previously. After we get the data loaded in, we'll be able to save the workbook and complete the task as described.
- Creating a database in Excel
- Joining two data sets
- Searching a database in Excel
- Importing and formatting text in an Excel database
- Creating a PivotTable from two data sources
- Calculating averages and percentages