Participation Project #02 Excel Basics

Column Names when Importing

When you import your files, your table should have the column names at the top. When Excel doesn’t know how to format the table, it puts default titles at the top, i.e., Column1, Column2, etc. Check your files to see if your table(s) in your submission look(s) like this:

If it does, don’t panic. County, Region, 1999, 2004, 2009, 2014, 2019, Average price, etc should actually be your column names. Since they are not, you can fix. You just need to change the way you import the file. 

When you go to Data > Get File > From CSV, it will pop up a window with a preview of your data. This should be where you review to make sure the column headers are importing correctly. If they are not, it will look like this:

Instead of clicking Load, or Load To from the drop-down menu next to Load (if it tells you to put the table somewhere other than A1), pick the “Transform Data” option. That will pop up a window that looks like this:

In the ribbon above Column 3, you will see an option that says "Use First Row as Headers".

Once you've click that, your preview should change to show you that the column headers have changed to say County, Region, 1999, 2004, etc. 

This will import your table into the Excel sheet correctly. Alternately, if you’ve already imported it and you already have the Column1, Column2, etc. labels on your table, you can fix this by clicking somewhere inside the table – it doesn’t matter where – and this will add two additional options to the ribbon at the top of the page that are specific to tables (for future reference, this is true when you click on almost anything in Excel, including pictures, graphs, etc.). You will only get the additional ribbons if you click inside the table; if you have a cell outside of the table active, those menus will disappear. The ribbon with the extra options looks like this:

If you click on the one that says “Query,” it gives you an option to “Edit” (this is the first option all the way to the left). Once you click on that, it takes you back to the Power Query Editor (the second image in this section) and you can follow the directions from there.

Saving files

The second thing is saving your files. When you open your file, the first thing you should do is check to make sure you’re working from an XLSX file and not a CSV file. If you’ve opened the CSV, it will not save your changes when you close it. CSV files don’t save the formatting or formulas, or anything other than text. If you accidentally start working from the CSV file, you can still save your changes by going to the File menu, choosing “Save As” and changing the file type from CSV to XLSX. When you download the files from the website, you should extract them to a different location (that you can find later). Open the folder the extraction created and work from that file. But again, if you find that you’ve started working from the CSV file, just follow the directions above and you should still be okay!

Renaming Sheets

To rename your sheets, right click on the tab and choose the option to Rename.

Cell References

Cell references are a combination of a letter (the column name) and a number (the row name). When you have cell B22, for example, you’re referencing a cell in column B (the second column) and the 22^nd You can tell which cell is active if you look at the Name Box, which is circled in the example below.

The columns are always designated with a letter, and rows are always represented by a number.