CHAPTER 3 Flashcards
The file system in EXCEL
Excel file names are made up of two parts: the file name and the file extension.
Confusingly, the term ‘file name’ is at times used to mean only the file name without the extension, and at times is used to refer to the file name and file extension.
The file name is the set of characters up to the last period. The file extension is the set of characters from and including the last period in a
file name. For example, in the file name “ThisFile.xlsx”, the file name is ‘This File’ and the file extension is ‘.xlsx’. The file extension tells us what
type of file it is. The file extension .xlsx indicates it is an Excel file. Earlier versions of Excel used a three-letter extension: .xls. The four-letter extension tells us that this is a more recent version of Excel. We can
associate different file names with different programs such that when we click on a file name it helps the operating system select the right program to open the file. Typically, when installing software, the software application does this association for us. Thus, when installing Excel, the software automatically links .xlsx (and some other file extensions) with Excel.
Excel uses different file extensions to indicate different
functionalities.
a) File Type: Workbook, Extension: .xlsx
b) File Type: Macro-enabled workbook, Extension:
.xlsm
c) File Type: Macro-enabled add-in, Extension: .xlam
Functionalities of different file types
a) .xlsx is a default file extension for a workbook in
Excel 2013.
b) xlsm is the file extension to indicate that the
workbook has macros. As this file extension has
four- characters, we can also say that this file was
created with a more recent version of Excel.
c) Macros are computer programs within a computer
program. These are created by the user or third
party developers to facilitate various actions in
Excel. For instance, if you have to perform a task
that consists of a series of actions, rather than
repeating this series of actions each time, you can
create a macro to execute this repetitive task for
you. As macros can be written for malicious
purposes, there are usually security concerns
around macros. For this reason, Excel 2013
requires a separate file type to indicate that the
Excel file contains macros.
xlam indicates that the file is an add-in. Excel can provide basic spreadsheet functions without add- ins. An Excel add-in is a program designed to provide additional functionality. This add-in may be created by Microsoft as part of the Excel package, or by a third-party developer, or created by the user himself. An add-in may have several macros embedded within it.
Customizing the default workbook
When we open Excel, it typically opens with a workbook called Book1.xlsx. This Book1.xlsx is stored in a folder called XLSTART.
By formatting Book1.xlsx to suit your needs you can have a customized workbook open each time that you start Excel. Such customization can take the form of:
a) Opening the workbook with one, two, three, or
more worksheets
b) Customizing worksheets with a specific font size,
font type, etc., as the default.
c) Setting the zoom to have a larger view of the
worksheet for an easier reading.
Any workbook that you place in the XLSTART folder is opened when Excel starts.
Customizing the list of previously used files
In using Excel, we may work on the same file frequently. It will be convenient for us to keep this file handy, such that we can open the file easily instead of searching for it in one of several folders. Excel makes this an easy task.
Start Excel. Click on the FILE menu. We will see a list of recently used workbooks. The number of workbooks that we last used can be
changed.
Recently opened files
To adjust the number of workbooks displayed
a) File menu -> Options -> Advanced -> Display (about
half way down)
b) In the box ‘Show this number of Recent
Documents’ indicate the number of workbooks you
want shown.
If the number of recent documents displayed is set to 5, then Excel will show the 5 workbooks that you used most recently. This means that as you use different workbooks, the ones that were opened earlier are going to drop off the list.
If there is a particular workbook that you want to be always available (irrespective of when it was used earlier), you can ensure that that workbook does not drop off the list by ‘pinning’ it to the list of previously
used workbooks.
To pin a workbook to a list of previously used workbooks:
a) File menu -> Recent Workbooks
b) Position the cursor on the workbook that you want
to pin
c) Click on the pin icon that appears on the right of
the file name
d) The direction in which the pin points changes to
indicate that it is now pinned
e) The horizontal pin indicates that the file is not
pinned
Data Types in Excel
The information we enter in a cell can be one of several types: number, text, date, time, and formula are the most common.
Number
A number permits mathematical operations. For example, we can add two numbers together. A number can be presented in different ways: in decimals, in fractions, in scientific notation etc. A number can also be represented with a currency symbol. By default, Excel aligns numbers to the right. However, the user can align the number
differently.
Text
Text refers to information that does not permit mathematical operations. This includes letters, punctuation marks, symbols, and numbers. (In Excel, we can format a number as text. We can also begin a
number with an apostrophe (‘) which will enter a number as text.) When it is not meaningful to perform mathematical operations on a number, we are using that number as text. E.g., my phone number is composed of several digits. However, it does not make sense to add my phone number to another phone number.
Text can be formatted to be in a particular font, font size, color, underlined, bold, italics etc. Different parts of text can be presented in different formats even within the same cell.
By default, Excel aligns text to the left. However, the user can align the text differently.
Date
A date can be represented in different formats. In the United States, we present dates with the month first, followed by date, followed by year as in MMDDYY. In India, the date comes first, followed by the month and
finally the year (DDMMYY). In China, the year comes first, followed by the month and then the date (YYMMDD). The year can be expressed in two digits or in four digits. We can also show the month and day in
letters. Dates are considered numbers in Excel.
Time
Time can be represented in hours, minutes and seconds; in hours and minutes only; in 12-hour or 24-hour formats. Like dates, times are considered numbers in Excel.
Formula
A formula can be a combination of:
a) numbers with operators (e.g., =5+5)
b) letters with operators (e.g., =”John” & “Doe”)
c) letters, numbers, operators (e.g., “James Bond” &
“007”)
Note:
a) In the 5 + 5 example, the + sign is an operator
b) In the “John” & “Doe” example, the & sign is an
operator
c) In the James Bond 007 example, the number 007
is treated as a text.
Other data types
Hyperlinks and images, but these are not commonly used.
Add-ins:
Provide additional functionality to Excel.
Add-ins can be created by:
a) Microsoft
b) Third Party Providers
c) The user
