Join Curt Frye for an in-depth discussion in this video Create a database in Excel, part of Database Clinic: MS Excel.
- [Instructor] Excel worksheets can handle just over one million rows of data, 1,048,576 to be precise. And that's a lot of data in the spreadsheet world, but it's a small data set in terms of what we usually handle in the database world. Fortunately, there is a solution. In this movie, I will show you how to enable PowerPivot and import large data sets into Excel. I'm working in a new blank Excel workbook, and when I look on the ribbon I see that I have Home, Insert, Page Layout, and so on, but I don't have a tab for PowerPivot.
That means I need to enable those options. To do that, I will click the File tab and then click Options to open the Excel Options dialog box. From here, I will click the Advanced category, and scroll down until I see the Data section. There it is, almost all the way at the bottom, and the option I'm looking for is the next-to-last one here. It says Enable Data Analysis add-ins: PowerPivot, Power View, and 3D Maps. If you don't see this option, then that means you do not have Office 2016 Professional, and you need to get a different version of the program.
But, hopefully, that appears for you. So, I'll go ahead and check the box and click OK, and I see that I have Power Pivot on the ribbon as an available tab. So, from here, I'll click Power Pivot, and then I'll go over to the left side and, in the Data Model group, I'll click Manage. This will allow me to bring in data from an outside source. I don't want to use the database or data service, so I will click From Other Sources, and then I'll scroll all the way down to a Text File.
This can be a confusing point, so I'll take a second to explain what's going on. A comma separated value file is technically an Excel file, but it has a .csv extension on it. An Excel file, in terms of Table Import Wizard, would have either a .xls, .xlsx extension, or something similar. That means we're looking for a text file, based on these definitions. So, I'll click Text File and click Next. I can give this connection a friendly name, so instead of Text, I'll say California, abbreviated as capital C capital A, Demographic Info, and next to File Path, I will click the Browse button, then, in the Chapter02 folder of the Exercise Files collection, we don't see anything.
That's because, when we take a look at the file type that is being searched for, we see Flat Files, specifically .txt. The file we're going for has a .csv extension, so I'll click that list arrow and the bottom selection is comma separated values, .csv. When I click that, I see the file that we want to work with, so I will click it, and click Open. And, after a moment, Excel will create the data connection, and I see that we have our fields.
The first row contains our column headers, so I will check the Use first row as column headers box. Takes a moment to reconfigure. There, we have County, County Name, Year, Race, and so on, so it looks like we're done. We can go ahead and click Finish, and the data will import. We're done, there were just about 3,650,000 rows transferred. We're done here, there were no errors, so I can click Close. The dialog box disappears, and we have our data in Power Pivot for Excel - Book 1.
From here, I can save the file, so I will click the save button, or I could press Control + S. Then, in the Save As dialog box, I'll navigate to the Chapter02 folder of the Exercise Files collection, and then, for the file name, I'll type CA 2010 to 2060, and click Save. After a moment, the save operation will be done, and we will have our data. If I want to see the data again, I can click Manage, and that'll take me back to the PowerPivot window, where the data actually resides.
- 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