— Having written in one cell (e.g.

a month), you can click and drag from the bottom-right

(the fill handle) of the cell to automatically fill in the rest
— Can type a number in one cell (e.g. B2 50000) and in the cell below it could write
=B2*103.5% and then entering it will show the results (i.e. a 3.5% increase). Can use
the automatic fill method here as well. If you change the B2 result it will automatically
update the rest as well
— Formatting: highlight the cell range - > Home: Number (can select currency (e.g. to
format like money with dollar sign, etc), etc). There also are increase and decrease
buttons just below it.
— Can create a table after activating any cell within the range - > inserting the table will
show a guessed range
— Value summation: click any table cell - > table design - > tick total row. To change the
summary formula just click that cell and use the drop-down menu
— Even if starting with a number a cell with text will consider it text and hence that
number can’t be used in numeric calculations. If that number is required write it in one
cell and type the rest in the cell to the right
— Excel’s Numeric Limitations: Excel numbers can be precise up to 15 digits. E.g. if you
have an 18-digit number, the 3 units beyond the 15 digits will be treated as ‘000’. This
is apparently rarely a problem | a potential situation where it can be a problem: when
entering credit card numbers (usu. these are 16 digits, so last digit will be substituted
with 0). The solution: enter credit card numbers as text. Can preformat the cell as text,
or can precede the credit card number with an apostrophe — either method works.
Other Excel numeric limits:
o Largest positive number: 9.9E+307
o Smallest negative number: –9.9E+307
o Smallest positive number: 1E–307
o Largest negative number: –1E–307
 These numbers are expressed in scientific notation. For example, the
largest positive number is “9.9 times 10 to the 307th power” — in other
words, 99 followed by 306 zeros. Keep in mind, though, that this
number has only 15 digits of precision.
— Negative values: precede a value with a minus sign or enclose it in parentheses
— Cell text limit: 32,000 characters
— Contents longer than current cell width: if adjacent cell blank - > appears to spill
contents over. If not blank - > displays as much as possible (Note: if need to display
long text string even if next to filled cells: can increase column width, edit text, use
smaller font, wrap the text so that it occupies more than one line: home - > alignment -
> wrap text)
— Dates: earliest date understood = Jan 1, 1900. This date has a serial number of 1. Jan 2,
1900, is 2, etc. This makes entering a formula to calculate the number of days between
two dates easy. | usu. can just type a common date format (e.g. June 1, 2016) and Excel
will interpret the entry and store it as 42522 which is the serial number. Note: need to
write the date in a way that corresponds to your regional date settings. i.e. the previous
date example may be interpreted as text if entered where the regional settings aren’t for
the U.S English system
— Times: extends Excel’s date serial number system to include decimals. I.e. Excel works
with times via using fractional days. E.g. 1 June 2016 = 42522, while noon on that day
 would be internally represented as 42522.5 (as noon as halfway through the day) |
normally don’t have to be concerned about this and can enter the time in a recognised
format. E.g. 1 June 2016 12:00
— Can highlight cells and start writing from the first. Will go through the highlighted
cells. Can leave a cell blank via enter; can go back via shift + enter
— To enter same data in all cells: highlight all required cells - > type value - > ctrl +
enter
— Automatic decimal points: Excel options - > Advanced tab - > automatically inset a
decimal point check box - > set number of decimal places. E.g. for two decimal places
entry of 12345 will becomes 123.45. Note: If you change the settings back, the already
entered data will remain as it is. Note: this is a global setting (i.e. affects all workbooks)
— Using autofill to enter a series of values: e.g. A1: 1 and A2: 3 - > highlight both and
then drag down the fill handle - > will complete a series of odd numbers
— Autocompletion of data entry: e.g. write in a column-cell widget. Later in the same
cell type first few letters - > will suggest completion - > press enter
— Line breaks in a cell: while typing press alt + enter. Note: changes cell format to Wrap
Text. To remove the manual line break: press delete when the insertion point is at the
end on the line that contains the manual line break
— Adding own AutoCorrect entries for shorthand: e.g. if you type cpd - > will fill with
data processing corporation. How: File - > options - > Proofing - > autocorrect options
button - > autocorrect dialog box - > check option replace text as you type - > enter
custom entries
— Fractions: e.g. for 6 and seven eighths - > 6 7/8 - > enter. For just a fraction: 0 7/8
— Highlighting cells via keyboard: click a cell and then while holding shift press the
direction keys
— Deleting many cells: highlight them and then click the clear button in the home section
— Form for data entry: have to add to quick access tool bar: right-click bottom of ribbon
- > add to quick access tool bar - > choose commands not in ribbon option from drop-
down menu - > select form - > add - > ok
— To use a data entry form: arrange data so that it can be recognised as a table (e.g. add
headings for the columns in the first row of data entry range) - > click the form button -
- > fill in the information (Note: if a cell contains a formula, it will appear as text. i.e.
cannot modify formulas using data entry form. It will automatically fill in that section
according to the imputed formula) - > click new button (this adds the entry)
— Entering current date and time: Date = ctrl + ; | Time = ctrl + shift + ; | Note: these
enter a static value which won’t be updated if the worksheet is recalculated | If you want
it to update use: for date - =TODAY() | for time - =NOW()
— Number formatting: formatting only affects appearance, not underlying value. | Must
select cell-range before can apply formatting | Note: if you highlight a formatted cell,
the formula bar will display the unformatted value. Exceptions include: date or time
(even though internally stored as a value), and values that use percentage format display
with a percent sign in the Formula bar
— Automatic number formatting: e.g. if enter 12.2% = Excel knows percentage format
desired and applies automatically. If use commas (e.g. 123,456) automatically applies
comma formatting. Likewise for dollar sign applies currency format.
— Handy default feature: if a cell is formatted to display as percent, can simply enter a
normal value (e.g. 12.5 for 12.5%). For less than 1% = precede with a zero (e.g. 0.52 for
 0.52%) | if not working or if prefer to enter actual value for percentages = options - >
advanced - > editing options - > check enable automatic percent entry check box
— Number formatting via ribbon: can highlight a range of cells before applying
— Shortcut keys to format numbers:
o ctrl + shift + ~ = general number format (unformatted)
o ctrl + shift + $ = two decimal point currency format (negative numbers appear
in parentheses)
o ctrl + shift + % = no decimal point percentage format
o ctrl + shift + ^ = two decimal point scientific notation
o ctrl + shift + # = date format with day, month, and year
o ctrl + shift + @ = time format with hour, minute, and AM or PM
o ctrl + shift + ! = two decimal place, thousands separator, and a hyphen for
negative values
— Number formatting via format cells dialog box: for greater control over number
formats | access via: home - > number - > click the small box at lower right; or home - >
number - > from drop down menu click more number formats; or right-click - > format
cells; or ctrl + 1
o Number category provides three options of control: number of decimal places
displayed, option to use thousands separator, and how to display negative
numbers
o Negative numbers category provides four choices (two of which display
negative values in red), and the choices change dependent on number of
decimal places and whether you choose to use the thousands separator
— When numbers appear to add incorrectly: a format doesn’t change the value — only
appearance. E.g. cell contains 0.874543, you may format it to appear as 87%. If that cell
is used in a formula, the formula uses the full value (0.874543), not the displayed value
(87%). In some situations, formatting may cause Excel to display calculation results
that appear incorrect, such as when totaling numbers with decimal places. For example,
if values are formatted to display two decimal places, you may not see the actual
numbers used in the calculations. But because Excel uses the full precision of the values
in its formula, the sum of the two values may appear to be incorrect. Several solutions
to this problem are available. You can format the cells to display more decimal places.
You can use the ROUND function on individual numbers and specify the number of
decimal places Excel should round to. Or you can instruct Excel to change the
worksheet values to match their displayed format. To do so, access the Excel Options
dialog box and click the Advanced tab. Check the Set Precision as Displayed check box
(located in the When Calculating This Workbook section

— Caution: selecting the Set Precision as Displayed option changes the numbers in the
worksheets to permanently match their onscreen appearance. This setting is applied to
all sheets in the active workbook. Most of the time this is not desired.
— The number formats:
o General: default. Display numbers as integers, decimals, or in scientific
notation if value too wide to fit in cell
o Number: can specify number of decimal places, whether to use comma to
separate thousands, how to display negative numbers (with minus sign, in red,
in parentheses, or in red and parentheses)
 o Currency: can specify number of decimal places, choose a currency symbol,
specify how to display negative numbers. This format always uses a comma to
sperate thousands
o Accounting: differs from currency format in that currency symbols always
align vertically
o Date: can choose from several different data formats
o Time: can choose from several different time formats
o Percentage: can choose the number of decimal places and always displays a
percent sign
o Fraction: can choose from among nine fraction formats
o Scientific: Displays numbers in exponential notation (with an E): 2.00E+05 =
200,000; 2.05E+05 = 205,000. You can choose the number of decimal places to
display to the left of E. The second example can be read as “2.05 times 10 to
the fifth.”
o Text: causes excel to treat the value as text (even if it looks like a number). Can
be useful for such items as part numbers and credit card numbers
o Special: contains additional number formats. In the U.S. version of Excel
includes things like: zip code, zip code +4, phone number, and social security
number
o Custom: can define your own custom number formats that aren’t included in
any other category
— Adding custom number formats: see the specific chapter
— Workbook: each file = workbook (each workbook has one or more worksheets). Each
workbook file is displayed in a window. The sheets can be either worksheets (has rows
and columns) or chart sheets (sheets that gold a single chart). Can open many
workbooks simultaneously.
— To increase amount of information seen: ribbon display options - > auto-hide ribbon
(can gain temporary ribbon-access afterwards by clicking the title bar). To reverse this:
ribbon display options - > show tabs and commands
— Moving and resizing windows: To make all workbook windows visible: can do
manually or choosing view - > window - > arrange all displays in the arrange windows
dialog box (windows that are minimised aren’t affected by this command)
— Switching among windows: only one window is the active window at a time. Methods
to make a different window active: click it if visible, ctrl + F6 to cycle through (shift +
ctrl + F6 cycles in the opposite direction), view - > window - > switch windows - >
select the window desired, click the corresponding Excel icon in the windows taskbar.
— Closing windows: to close the active window: file - > close, close button, alt + F4, ctrl
+W
— Activating a worksheet: to activate another worksheet: click the tab, ctrl + PgUp
activates the previous sheet if existent, ctrl + PgDn activates the next sheet if existent
— Adding a new worksheet: click new sheet control (the plus sign icon at sheet tab
section), shift + F11 (a new sheet is added before the active sheet), right-click a sheet
tab - > choose insert from the shortcut menu - > select general tab of the insert dialog
box - > select worksheet icon - > ok (a new sheet is added before the active sheet)
— Deleting a worksheet: right-click sheet tab - > delete, activate unwanted worksheet - >
home - > cells - > delete - > delete sheet. Note: deleting a worksheet cannot be undone
— Deleting multiple sheets: press ctrl while selecting the sheets you want to delete (to
select a group of contiguous sheets: click first sheet tab - > press shift and then click last
sheet tab)
— Changing worksheet name: double-click sheet tab - > can edit name. Note: sheet
names can contain as many as 31 characters, and spaces are allowed. However, cannot
use the following characters: : / \ [] ? *
— Changing sheet tab colour: right-click the tab - > choose tab colour from the shortcut
menu - > select the colour from the colour selector box. Note: can’t change text colour,
but Excel will choose a contrasting colour to make the text visible. Note: if sheet tab
colour changed, colour will only be visible when sheet is not the active sheet
— Rearranging worksheets: You can move a worksheet from one workbook to another.
You can also create copies of them (either in the same or different workbook). To move
a workbook or copy it: right-click sheet tab - > choose move or copy to display the
move or copy dialog box - > use the dialog box to specify a desired operation | to move
a worksheet: click worksheet tab - > drag it to desired location (to move to a second
workbook, that workbook must be open and not maximised) | to copy a worksheet: click
worksheet tab - > press ctrl while dragging tab to desired location (same conditions
apply to move to a different workbook, as stated before). If the moved or copied
worksheet shares a name with another already existent sheet = it will be altered to be
unique. Note: any sheets copied or moved to another workbook maintain any defined
names and custom formats when moved to the new workbook
— Move or copy multiple sheets simultaneously: click their sheet tabs while holding
down the ctrl key - > then follow previously stated methods
— Hiding and unhiding a worksheet: if hidden, its sheet tab is also hidden. At least one
sheet must remain visible. To hide a sheet: right-click sheet tab - > choose hide sheet |
To unhide: right-click any sheet tab - > choose unhide sheet (Note: this process has to
be repeated to unhide each sheet). Note: the unhidden sheet will appear in its previous
position
— Preventing sheet actions: to prevent some sheet modifying operations (i.e. it’s a
security measure. Though relatively weak). How: review - > change - > protect
workbook - > structure - > provide a password (optional)
— Zooming in and out for a better view: ctrl + zoom, slider, view - > zoom - > zoom,
select a range of cells - > view - > zoom - > zoom to selection. (the selected range will
be enlarged as much as possible, but will still fit entirely in the window). Zooming
affects only the active worksheet window — hence, different worksheets can have
different zoom factors
— Viewing a worksheet in multiple windows: create and display a new view of the
active workbook = view - > window - > new window. To help keep track of the
windows, Excel appends a colon and a number to each window. A single workbook can
have as many windows as desired. Each window is independent (e.g. scrolling in one
doesn’t cause scrolling in the other), however, if changes are made in the worksheet in
one window, the changes are also made in all views of that worksheet.
— Tip: if workbook is maximised when creating the new window, might not notice the
new window. Choose view - > window - > arrange all and choose one of the options. If
you select the windows of active workbook check box, only the windows of the active
workbook are arranged
— Tip: multiple windows make copying or moving information from one worksheet to
another easier. Can use Excel’s drag-and-drop procedures to copy or move ranges
— Comparing sheets side by side: make sure that the two sheets are displayed in separate
windows (the sheets can be from the same or different workbook). In the same
workbook: view - > window - > new window (to create a new window for the active
workbook); Activate the first window - > view - > window - > view side by side. If
more than two windows are open, you see a dialog box that lets you select the window
for comparison. The two are tiled to fill the entire screen | Note: when this feature is
used, scrolling in one window causes scrolling in the other (if this simultaneous
scrolling isn’t desired: view - > window - > synchronous scrolling). If you rearrange or
move the windows, choose view - > window - > reset window position to restore the
windows to the initial side-by-side arrangement. To turn off side-by-side viewing,
choose view - > window - > view side by side again | Note: this feature is for manual
comparison only
— Splitting the worksheet window into panes: to not clutter screen with additional
windows. View - > window - > split. The split occurs at the location of the cell pointer.
If pointer is in row 1 or column A, command results in a two-pane split; otherwise, it
gives four panes. Can drag individual panes to resize them. | The row numbers of a pane
don’t have to be continuous — i.e. can see widely different parts of the worksheet at the
same time. To remove split panes: view - > window - > split again
— Keeping the titles in view by freezing panes: keeps the column headings or
descriptive text in in the first column visible even as you scroll down. How: move cell
pointed to the cell below the row that you want to keep visible while you scroll
vertically and to the right of the column that you want to remain visible while you scroll
horizontally. Then view - > window - > freeze panes - > select freeze pane option.
(Note: Excel inserts dark lines to indicate frozen rows and columns). To remove: view -
> window - > freeze panes - > select unfreeze panes option. Most of the time will want
to freeze either first row or first column: view - > window - > freeze panes drop-down
has two additional options (freeze top row and freeze first column) — this eliminates
need to position cell pointer before freezing panes. | Note: in case where a range is
designated to be a table — might not even need to freeze panes. When scrolling down,
Excel displays table column headings in place of column letters
— Monitoring cells with a Watch Window: might want to monitor value of a particular
cell while working. Watch Window displays the value of any number of cells in a
window that is always visible. Formulas - > Formula Auditing - > Watch Window.
Watch Window is a task pane — hence, can dock to side of window or drag and make it
float over worksheet. To add a cell to Watch Window: click add watch - > specify cell.
Can monitor values in multiple worksheets. | TIP: double-click a cell in the Watch
Window to immediately select that cell — however, only works if cell is in the active
workbook
— Inserting rows and columns: Although number of rows and columns fixed, can insert
where necessary — the already existing rows are moved down to accommodate the new
one. Last row is simply removed from worksheet if empty. Inserting a new column
shifts the columns to the right, and last column is removed if empty | Note: if last row
not empty, can’t insert a new row. Likewise if last column contains information, Excel
won’t let insert a new column
o Inserting new rows: Select an entire row or multiple rows by clicking the row
numbers in the worksheet border. Right-click and choose Insert from the
shortcut menu OR Move the cell pointer to the row that you want to insert, and
then choose Home ➪ Cells ➪ Insert ➪ Insert Sheet Rows. If you select
 multiple cells in the column, Excel inserts additional rows that correspond to
the number of cells selected in the column and moves the rows below the
insertion down
o Inserting new columns: Select an entire column or columns by clicking the
column letters in the worksheet border. Right-click and choose Insert from the
shortcut menu OR Move the cell pointer to the column that you want to insert,
and then choose Home ➪ Cells ➪ Insert ➪ Insert Sheet Columns. If you select
multiple cells in the row, Excel inserts additional columns that correspond to
the number of cells selected in the row
— Inserting cells: you can also just insert cells. Select desired insertion range and then
choose home - > cells - > insert - > insert cells (or right-click the selection and choose
insert). To insert cells, must shift existing cells to the right or down. Thus, Excel
displays option to choose (note: can also insert entire rows and columns via the
displayed dialog box)
— Deleting rows and columns: Select an entire row or multiple rows by clicking the row
numbers in the worksheet border. Right-click and choose Delete from the shortcut menu
OR Move the cell pointer to the row that you want to delete, and then choose Home ➪
Cells ➪ Delete Sheet Rows. If you select multiple cells in the column, Excel deletes all
rows in the selection | Column deletion works in a similar way
— Changing column widths: measured in terms of number of characters of a monospaced
font that will fit into the cell’s width. By default, each column’s width is 8.43 units,
which equates to 64 pixels (px) | TIP: if hash symbols (#) contain a numerical value,
column isn’t wide enough to accommodate the information in the cell. Widen column to
solve
o Before changing column width, can select multiple so that will be same for all
of them. To select multiple: click and drag in column border OR press ctrl
while selecting individual columns. To select all columns: click button where
row and column headers intersect. Can change column width via any of the
following: Drag the right-column border with the mouse until the column is the
desired width OR Choose Home ➪ Cells ➪ Format ➪ Column Width and
enter a value in the Column Width dialog box OR Choose Home ➪ Cells ➪
Format ➪ AutoFit Column Width to adjust the width of the selected column so
that the widest entry in the column fits. Instead of selecting an entire column,
you can just select cells in the column, and the column is adjusted based on the
widest entry in your selection OR Double-click the right border of a column
header to set the column width automatically to the widest entry in the column |
TIP: to change default width of all columns: home - > cells - > format - >
default width (Every column previously not adjusted takes on the new column
width) | Caution: any manual adjustment of column length, will make it so that
no automatic adjustments will be made to column to accommodate longer
numerical entries. Will have to change column width manually
— Changing row heights: measured in points (a standard unit of measurement in the
printing trade — 72pt is equal to 1 inch). Default row height using default font is 15pot,
or 20px | default row height can vary depending on font defined in the Normal style.
Additionally, Excel automatically adjusts row heights to accommodate the tallest font in
the row. So, if font size of a cell is changed to 20pt, for example, Excel makes the two
taller so that entire text is visible
 o Can set row height manually, however via any of the following (as with
columns, can select multiple rows): Drag the lower row border with the mouse
until the row is the desired height OR Choose Home ➪ Cells ➪ Format ➪ Row
Height and enter a value (in points) in the Row Height dialog box OR Double-
click the bottom border of a row to set the row height automatically to the
tallest entry in the row. You can also choose Home ➪ Cells ➪ Format ➪
Autofit Row Height for this task | Note: changing row height is useful for
spacing out rows and is almost always preferrable to inserting empty rows
between lines of data
— Hiding rows and columns: Can be useful in cases where you don’t want users to see
particular information or if you need to print a report that summarises the information in
the worksheet without showing all the details | to hide rows in your worksheet: select
row or rows you want to hide via clicking in the row header on the left - > right-click
and choose hide from the shortcut menu OR can use commands on the home - > cells -
> format - > hide and unhide drop-down list. Use same method to hide columns | TIP:
can also drag the row or column’s border to hide the row or column. Must drag the
border in the row or column heading, drag the bottom border of a row upward or the
right border of a column to the left | A hidden row is actually a row with height set to
zero. Similarly, a hidden column is a column with a width of zero. When using
navigation keys to move the cell pointer, cells in hidden rows or columns are skipped.
In other words, you can’t use the navigation keys to move to a cell in a hidden row or
column | Notice, however, that Excel displays a narrow column heading for hidden
columns and a narrow row heading for hidden rows. Can click and drag the column
heading to make the column wider — and make it visible again. For a hidden row, click
and drag the small row heading to make the column visible. Another method to unhide
rows and columns: home - > find and select - > go to (or its F5 equivalent) to select a
cell in a hidden row or column. Then you can choose home - > cells - > format - > hide
and unhide - > unhide columns
— Understanding cells and ranges: