CHAPTER 5 Flashcards
What are different ways or methods to get data into Excel
a) Entering data directly in a worksheet
b) Copying and pasting data
c) Importing data from another file
d) Filling data in a pattern
e) Linking a worksheet to an internet source
Entering data directly in a worksheet
We can enter data directly in a worksheet. in entering data, we can enter numbers, formulas, or other types of data.
How do we enter numbers in a worksheet
Entering numbers is straightforward. We type the number, hit enter, and then go to enter the next number. Once we have entered the data, our data is not saved until we save the workbook. When entering data, you should enter one value in one cell. That is, a cell should contain a constant. A variable will refer to that cell. If you need to refer to that value, you should refer to the cell address and not include the value as a part of a formula. Embedding a value in a formula is known as hard coding and can contribute to errors. In entering formulas, we can use one or more operators and / or functions like addition, subtraction, multiplication, division, and exponentiation. We can also use parentheses () to indicate which operation we want to have done first. This is referred to as the precedence of operations.
Order in which the operations are executed
a) Parentheses
b) Exponent
c) Multiplication
d) Division
e) Addition
f) Subtraction
A way to remember this sequence is by recalling the phrase: Please Excuse My Dear Aunt Sally. The first letters in this phrase, represent the sequence of operations.
Copying and Pasting
When copying data from a table in another file, Excel is smart enough to recognize the tabular format. Excel then automatically pastes the data in rows and columns. On occasion, however, Excel might paste
all columnar values to the same column, separated by tabs, spaces, or commas. In such cases, we need to tell Excel to separate different values and put each value in a separate column (and in the appropriate row). Consider a cell A1, that has ten values: 2 3 4 5 6 7 8 9 10 11. All the 10 values are in one cell and are separated by spaces. To command Excel to put these values in separate columns:
a) DATA ribbon -> Data Tools group -> Text to Columns -> Delimited -> Next
b) Select Space -> Next
c) Select a format (Text or Date) or General if no specific format is required
d) Select Finish
In such cases, the character separating the different values is called a separator. In this particular instance, a space is the separator. If you have multiple spaces separating two values, you need to check ‘Treat
consecutive delimiters as one”. This will ensure that Excel ignores consecutive separators. Otherwise, you will get a number of blank columns. Consider always choosing this option. Sometimes, the consecutive delimiters may appear deep in the data set, rather than at the beginning of the data. Selecting this option covers the eventuality that your data may have consecutive delimiters. (In other words, do not assume that the data you are going to import is consistently formatted throughout). In the event you do not have consecutive delimiters, it does not cause any problem.
Importing data from another file
We can get data into Excel from another electronic data source. This external source can be a database, a text file, or even the internet. Excel allows for obtaining data from a variety of sources. On occasion, we may want to import data from a text file (e.g. with file extension such as .txt or .csv).
How to command excel to get data from another file
DATA ribbon -> Get External Data group -> From Text
When importing data from text files, Excel asks us to choose the file type that best describes the data. The data may be in one of the two formats:
a) Delimited: characters such as commas, or tabs separate each field
b) Fixed width: Fields are aligned in columns with spaces between each field.
Delimited data
Delimited data are fields (or values in columns) separated by delimiters such as commas, tabs, or other characters. In data that can be described as Fixed Width, each value in a given column is the same width (hence, fixed width). As this is frequently not the case, we are better off familiarizing ourselves with importing data that is delimited.
The Text Import Wizard dialog box gives us the option of indicating whether our data has headers.
Excel’s Text Import Wizard determines whether the file is delimited, and what type of delimiter is used. However, it is a good practice to check that the default delimiter indicated by the Text Import Wizard is the correct one.
A commonly used separator is a comma. A file with values that are separated by commas is often referred to as a ‘csv’ file where csv stands
for ‘comma separated values’. ‘csv’ can also refer to ‘character separated values’ as you can use characters other than a comma to separate one
value from another. For instance, you may find that data from legacy systems are often stored with columnar values separated by tabs, spaces,
or commas. The characters used to separate columnar values are known as delimiters. (A legacy system refers to older technologies. In the
present context, we are referring to computer systems that store data in older file formats.)
A delimiter may be used consecutively e.g., 10,,,,20 (the number 10 separated from number 20 by three commas). This may mean that the number 10 in one column is followed by three blank columns and then by the number 20. It could also mean that the number 10 should be followed immediately by the number 10. In the latter case, you can use the option to ‘Treat consecutive delimiters as one’. By selecting this option, you ensure that the value ‘10’ is imported into one column and the next value ‘20’ comes in the immediate next column.
An advantage of storing data as a .csv or .txt file is that it makes the size of the file much smaller. Another advantage of storing data as a .csv
or .txt file is that the data can be read by programs that are meant to read text (e.g., Notepad or Word).
Importing data using Query Editor
In some versions of Excel, we may need to use a Query Editor to import data. (The Query Editor dialog box is displayed when we use the ‘Get External Data’ from the Data ribbon.) When we attempt to import data using the Query Editor, we see a dialog box. We then click on LOAD, and all the data is imported into one column. We then select the column with the data and then use the “Text to columns” command. This command opens a dialog box.
Importing data formatted as a Table Word
Let us say we have some data formatted as a table in Word. We can convert this table to text in the following steps:
a) Select the table
b) The Table Tools dialog box will pop up
c) Under the Table Tools dialog box, we have the Layout menu
d) Under the Layout menu, we have Data commands
e) One of these commands is ‘Convert to Text’.
We can then save the file as a text file and then import the data into Excel.
Linking Excel to an external source of data
Importing a file is usually a one-time event. However, if the source data changes we will want to import the updated data. In such cases rather than importing data, we may want to link Excel to the source data.
Once the data is linked, we can refresh the linked data so that it is updated to reflect changes in the source data.
Let us say we want to get price of General Electric shares from the Yahoo.com finance web site. We start the process of linking Excel to this web site by:
a) Clicking on the DATA ribbon
b) Get External Data
c) From Web
We get a new dialog screen titled ‘New Web Query’. In the address box type https://finance.yahoo.com/. (If you get a ‘script error’ message, click no to stop running the script).
The ticker symbol for General Electric is GE. We will type GE in the ‘search finance’ window, and press enter. You will now see data for GE stock. A black-arrow-in-yellow-box indicates data formatted as a table, and that can be imported into Excel.
Clicking on the black-arrow-in-yellow-box changes the yellow background to green, and the arrow changes to a check mark, indicating that that table is now linked to Excel. You can choose more than one
table. Now click on the ‘Import’ button on the bottom right of the screen. You can now choose where to have the data entered in the Excel workbook.
Filling Data in a Series
We can use Excel’s ‘Fill Series’ functionality to fill in data according to a pattern. For instance, if we wish to enter odd numbers from 1 to 25 in column one, we would: First, enter value 1 in cell A1. Then:
a) Home ribbon -> Editing group -> Fill -> Series
b) Enter a starting value in a cell
c) Click columns
d) Click Linear
e) Show Step value as 2
f) Stop value as 25
e) Click OK
Excel populates our data with odd numbers from 1 to 25. If we wanted even numbers, we would have shown the starting value as 2, and we would have even numbers from 2 to 24.
Displaying DATA - Freezing or splitting a screen
Open the Fruit Sales Mini Dataset. This dataset has 20 columns and 100 rows. The first column has the Transaction ID. The first row has the header for each column. The number of rows and columns visible on one screen depends on factors such as font size, row height, and column width. However, most users will see about 20 rows and 10 columns. Once we go past about 20 rows, the column headers are no longer
visible. This makes it difficult to see which column of data we are looking at. To resolve this, we can ‘freeze’ the top row that shows the column heading.
To freeze a row:
a) Keyboard: Alt-W -> f -> Enter
b) Mouse: VIEW ribbon -> Window group -> Freeze top row
Once the top row is ‘frozen’ it remains visible as we scroll down the worksheet. Similarly, we can freeze the left column, so that the headers for rows are visible as we scroll to the right of the worksheet. We can also freeze more than one row or one column. When we select a cell and freeze rows and columns, all rows above the active cell, and all columns to the left of the active cell are frozen.
When we freeze rows and columns in a screen, you cannot scroll inside the frozen area. If you need to do this, you want to split the screen rather than freeze it.
To split a row:
a) Keyboard: Alt-W -> S
b) Mouse: VIEW ribbon -> Windows group -> Split
When you invoke the split command, Excel splits the screen such that all rows above the active cell, and all columns to the left of the active cell, are in the split portion of the screen.
Excel allows you to create the following types of series
a) Growth series
b) Linear series
c) Date series
A number that may be used in a formula should be:
placed in an individual cell.
