Google Sheets easily works with data created in different applications, including Microsoft Excel, OpenDocument Spreadsheet format, and text files. As long as this external data is saved in a format Sheets can recognize, it can be imported into Sheets where it can be manipulated and formatted.
The practice workbook in Google Sheets contains credit card statements for January and February. The statements have been formatted and a Balance column has been added.
The source data file is a .txt file we will download which contains the statement for March. This statement will need to be imported into Google Sheets and formatted to match the previous months.
Copying the practice workbook
If you have been working through the course in sequential order, please note that these are new exercise files and will need to be copied to your Google Drive.
To begin copying the practice workbook in a new browser window,
Our source data is a .txt file saved in a zip file. Before importing the .txt file into Google Sheets, we will need to download and save the zip to the hard drive of our computer and then extract the .txt file. Use the button on the right to begin downloading the file.
A common way to share data is by saving it in an unformatted text file with each field of text separated by a delimiter such as a comma or tab. A delimited text file has a consistent structure for each record and each record is preceded by a return.
We are going to be working with a text file that contains a credit card statement for the month of March. Before we import this file into Google Sheets, take a moment to look at it in its text form.
To open the file, March_Statement.txt,
navigate to where the exercise files were extracted, Double-ClickMarch_Statement.txt
The file opens in a text editor:
This is a tab-delimited text (.txt) file. Each entry of the statement is on its own line with each individual element separated by pressing the Tab key. When this data is brought into Google Sheets, each entry will be in its own row and each element will go in its own cell.
To work with this file in Google Sheets, we will need to import it.
To close the March_Statement.txt file, in the text editor,
Click
Importing a file into Google Sheets
Let's return to the Copy of Google Sheets: Working with non-numeric data: Working with external data workbook in Google Sheets and begin importing the .txt file.
Because the .txt file isn't a Google app file, it wasn't copied to My Drive. We will need to import it into the workbook.
Return to the Copy of Google Sheets: Working with non-numeric data: Working with external dataworkbook in Google Sheets.
To open the Import file dialog box, on the menu bar,
Click File, Click Import
To upload a file from your computer, in the Import file dialog box,
Click Upload
To choose a file,
Click
To upload the file March_Statement.txt,
Navigate to where the file was extracted on your computer, Double-ClickMarch_Statement.txt
The Import file dialog box changes:
In this dialog box, we see the file to be imported, March_Statement.txt. By default, the Import location drop-down menu shows "Create new spreadsheet" and the Separator type is "Detect automatically." The checkbox next to "Convert text to numbers, dates, and formulas" is checked.
Because we want the data to go in a new spreadsheet, we will accept the default import location. We want Google Sheets to recognize that this is a tab-delimited text file. We will change the separator type to Tab.
To change the separator type, in the Separator type drop-down,
Click , Click Tab
To finish importing the data, in the dialog box,
Click
The dialog box changes slightly and now tells us that the file was imported successfully. There is also a link to open the file now.
To open the imported data, in the dialog box,
Click Open now >>
The delimited data is imported into a new workbook. Both the workbook and the worksheet tab have been named to reflect the name of the imported .txt file, March_Statement.
Having the workbook and worksheet using the same name could be confusing. Let's change the name of the workbook.
To select the current workbook name, above the menu bar,
ClickMarch_Statement
To rename the workbook, type:
MarchStatement Enter key
Cleaning up the data
Now that the workbook has been saved in Google Sheets, we can begin working with the data.
This is a fictional credit card statement for the month of March. Before we get too involved in cleaning and formatting the data, let's examine another Sheets workbook, Copy of Google Sheets: Working with non-numeric data: Working with external data. This workbook has statements from January and February.
To return to the workbook, Copy of Google Sheets: Working with non-numeric data: Working with external data, in the browser window,
Click the Copy of Google Sheets: Working with non-numeric data: Working with external data tab
The Import File dialog box is still open. Before we can continue, we need to close it.
To close the Import File dialog box,
Click
This workbook contains two monthly statements: January and February. Each worksheet contains a credit card statement for the corresponding month. Each has eight columns. The Balance column (column H) is calculated using the previous balance in the second row. The dates are in short format and payments are shown as negative numbers. We want our March statement to look the same. There is also a third worksheet, Spending, where spending for each month has been calculated.
Let's return to the MarchStatement workbook and begin cleaning and formatting.
To return to the MarchStatement workbook, in the browser,
Click the MarchStatement tab
There are several things to be done to the data in the March statement to make it look like the January and February data. We will begin by widening the columns to fit the data.
To select columns A through H, in the column headers,
Press & Drag from column A through column H
To widen the columns, in the header row,
Double-Click between any two column headers
Each column is now as wide as it needs to be to fit the data.
Let's format the column headings to be bold font. We'll select all of row 1 and apply the bold formatting.
To select all of the contents of row 1, in the row headers,
Click the row 1 header
To make the column headings bold, on the Home tab,
Click
Now let's remove a column and add a row to the worksheet. The Memo column is unnecessary and we need a new row to hold the previous balance.
To select the Memo column, in the header row,
Click the column H header
To delete the column, on the keyboard, press:
Delete key
Deselect the selected column.
To select row 2, in the row headers,
Click the row 2 header
To insert a row above row 2,
Right-Click row 2, Click + Insert 1 row above
Deselect the selected row.
To add the description,
Click in cell C2, type: PREVIOUS BALANCE
The final change we need to make to the MarchStatement workbook is freezing the top two rows of data. When rows are frozen in Google Sheets, they will remain at the top of the worksheet as we scroll down through the data. (Columns can also be frozen.) When freezing more than one row, the active cell should be in the row beneath the rows to be frozen.
To make cell A3 the active cell,
Click in cell A3
To freeze row 1 and row 2, on the menu bar,
Click View, Point Freeze, Click 2 rows
The gridline between row 2 and row 3 is now darker and thicker indicating the above rows are frozen.
To see how the frozen rows work, using the mouse,
scroll down
Now that the March statement looks similar to the previous statements, we're ready to move on to working across worksheets and analyzing the data.
, 
