Easy Excel - Workshop File
Easy Excel - Workshop File
Worksheets
Useful Features
Instructions
Exercise
Example Result
Although you are not required to do so, if you decide to use this
material to present, or you adapt it and redistribute it, I would
appreciate an email at julie.marcoux@dal.ca so that I can
mention it in my annual report.
Last updated
April 2024
Description
Explanations and screenshots about navigating and selecting, cell formats, conditional
formatting, sorting, filtering, and printing
Instructions on features to apply, in order, to the Exercise spreadsheet to make it look more
like the Results spreadsheet.
List of books used for hands-on exercise. Follow the instructions from the instructions
spreadsheet to transform into something that looks like the Results spreadsheet.
Beautifully formatted list of books. Apply the instructions in the instructions spreadsheet to
the exercise spreadsheet to achieve a similar result.
julie.marcoux@dal.ca
https://dal.ca.libguides.com/prf.php?
account_id=53640
Page 4 of 30
Menu
Scroll down after selecting item in menu - desired instructions are at the bottom of the screen
Navigation and Selection
Cell Formats
Conditional Formatting
Sort
Filters
Printing
Menu
Navigation and Selection
Selection
Navigation
Freeze Panes
Insert and delete rows and columns
Hide and show rows and columns
Open workbook in multiple windows
Selection
Clicking a cell Start typing to modify the contents of a cell (overwriting previous content).
Double-clicking a cell Start typing to modify the existing text or formula of the cell.
Clicking row numbers Select one or more rows.
Clicking column letters Select one or more columns.
Click-Hold and then moving your mouse Select adjacent elements.
Ctrl-Hold and clicking Select non-adjacent elements.
Ctrl-A If in a range, select all cells in a range (do it again to select all cells in worksheet). If not in a range, select all ce
Clicking (in corner of worksheet) Select all cells in worksheet.
Note: Clicking away from an active selection executes the contents (if applicable) and then escapes the selection bef
Note: To move a column or a row: click on the row number or column letter, move your cursor to the edge until it tu
hold shift and click-hold your mouse, move your cursor to where you want to move the row or column.
Navigation
Ctrl-Home Select the first cell in your worksheet (or the first cell under a freeze panes).
Ctrl-End Reach the last cell (lower right corner) where data exists or has existed in a worksheet.
Ctrl-[Arrow] Move from the active cell to the last cell before a blank, in the direction of the chosen arrow.
Ctrl-Shift-[Arrow] Select cells, starting from the active cell and selecting cells until there is a blank, in the direction of the chosen
Ctrl-Shift-Home Select cells, starting from the active cell and selecting cells all the way to the first cell in your worksheet (or the
Ctrl-Shift-End Select cells, starting from the active cell and selecting cells all the way to the last cell (lower right corner) wher
Freeze Panes
Allows you to freeze rows and/or columns in place, so they are displayed even if you scroll away.
Click under the row you want to freeze and/or to the right of the column you want to freeze. Click "Freeze Pan
Click "Unfreeze Panes" (same position in the menu) to restablish the standard view.
Page 5 of 30
View - Window
Select one (or more) rows or columns after where you want to make an insertion.
Home - Cells
After rows or columns have been inserted, you can choose to copy the format of rows and columns surroundin
Click on the Format Painter tool that temporarily appears and select the best option.
Home - Cells
View - Window
Page 6 of 30
In Windows, you can select “Arrange All” after creating a new window to automatically arrange your windows
View - Window
Menu
Cell Formats
Copy formatting of a cell, row, or column
Align text inside a cell
Add a border to a cell
Modify the color of a cell
Wrap text (adjust height of rows to see text)
Change direction of text
Format of a cell
Merge cells
Cell and worksheet styles
Select the cell, row, or column containing the desired formatting. Click on "Format Painter".
Home - Clipboard
Select all cells, rows, or columns where the formatting should be pasted.
Home - Alignment
Home - Font
Page 7 of 30
The tool directly available in the Font section of the Home ribbon does not allow you as much control as this m
Home - Font
Home - Alignment
If you change the width of columns after activating text wrapping, click twice on "Wrap Text" to deactivate it a
Home - Alignment
Format of a cell
Home - Number
It's very important to select the appropriate format, especially when dealing with numbers, dates, and time.
Merge cells
Home - Alignment
Page 8 of 30
Only merge cells when you are completely finished with a worksheet. Consider making a copy of your workshe
Home - Style
For cells
For worksheets
Menu
Conditional Formatting
Home - Styles
Menu
Sort
Enter the column to sort - if the suggestions seem strange, check if "data has headers" is correctly applied and
Menu
Filters
Regular Filter
Advanced Filter
Regular Filter
Select any cell in a range, and then click on the filter tool.
Apply filters to temporarily view only rows of data that fit with specific criteria.
Make sure to explore the possibilities of text, data, number, and color filters.
Advanced Filter
Harder to use than the regular filter, but offers some interesting possibilities.
Create a 'table' using the exact same column titles as the ones in the range that you wish to filter.
Enter criteria for each column.
Enter on the same row for 'AND".
Enter on a different row for 'OR".
For example, the above table finds all paper books published after 2015.
Or books published by Routledge, regardless of year and binding.
Page 11 of 30
Select any cell in a range, and then click on the advanced filter tool.
Menu
Printing
Try the following actions to more easily prepare a worksheet for printing:
Page Layout - Scale to Fit If you want to print a very wide table on two pages, select two pages instead of one for the width of the print
File - Print Then choose the following option to keep the pages in order:
Page 13 of 30
ection
again to select all cells in worksheet). If not in a range, select all cells in the worksheet.
tes the contents (if applicable) and then escapes the selection before selecting your target.
number or column letter, move your cursor to the edge until it turns into a black cross,
our cursor to where you want to move the row or column.
ou can choose to copy the format of rows and columns surrounding it.
arily appears and select the best option.
locations simultaneously.
e speadsheets.
ame file, any updates you make will happen in all windows.
Page 15 of 30
n of the Home ribbon does not allow you as much control as this method.
tivating text wrapping, click twice on "Wrap Text" to deactivate it and reactivate it. Height of rows will be recalculated.
nished with a worksheet. Consider making a copy of your worksheet and merging cells in the copy instead.
atting
o specific criteria.
Page 18 of 30
w is formatted.
seem strange, check if "data has headers" is correctly applied and check if the entire data range is selected.
Page 19 of 30
ch printed page.
pages, select two pages instead of one for the width of the print area.
e pages in order:
Instructions
Apply these instructions to the Exercise worksheet. Following these instructions should result in a table similar to the one in the Results worksheet.
Part 1
1 Freeze the top row of the table (using Freeze Panes).
2 Bold A1, center horizontally, apply a font size of 12.
3 Copy-paste A1's formatting to the entire first row.
4 Click in cell A2 and try to use keyboard shortcuts to select the rest of the table and apply a horizontal left-align.
5 Add a thick outline for cells in rows 2 to end of table.
6 Add a thick outline for cells in row 1 of the table.
7 Add a thin interior border to the entire table.
8 Roughly adjust width of columns to better fit width of content. Apply text wrapping.
9 Center all text vertically.
10 Add a beautiful fill color for cells in row 1 of the table.
11 Change the orientation of text on row 1.
12 Change your mind and re-establish the original orientation (Ctrl-Z or undo or select the appropriate option in the text orientation menu).
13 Change the format of cells in column A to reveal the ISBN numbers.
14 Insert a row before row 1, and a column before column A. Resize to make everything look more attractive. So pretty!
15 Hide rows 5 to 15. Oh no! The data is gone?!
16 Don't worry, the data is still there! Reveal all rows in the worksheet.
Part 2
17 Use find/replace to remove " USD" from the "US List" column.
18 Format cells of the "US List" column to the "Currency" format (with two decimals).
19 Use conditional formatting to reveal the most expensive books.
20 Use conditional formatting to highlight the books where the author is an editor.
21 Use conditional formatting to reveal books published after 2020.
22 Sort the table by author and then title. "Knowledge Preservation Through Community of Practice" should be the first book in the list.
23 Use a color filter to list together books published after 2020.
24 Remove the color filter. Use a text filter to list together all edited books.
9788763003254 MANAGING THE GLOBAL SUPPLY CHAIN; JULIANA HSUAN ... ET AL. 4th ed.
Pub
Author Publisher LC/NLM/Dewey Class Binding
Year
AGRIFOGLIO, ROCCO SPRINGER 2021 HD30.2 Paper
INFORMATION AGE
ASGARY, NADER H 2019 HD62.4.A746 2015 Paper
PUB
BANG NGUYEN / Editor ROUTLEDGE 2018 HF5415.5.D1655 2016 Paper
BUSINESS EXPERT
BEER, LAWRENCE A 2020 HF1365.B44 Paper
PRESS
PALGRAVE
BIJAOUI, ILAN 2016 HD62.5.B55 2015 Cloth
MACMILLAN
ELSEVIER ACADEMIC
DEPAMPHILIS, DONALD M 2020 HD58.8.D467 Cloth
PRESS
PALGRAVE
HIDALGO, CARLOS 2018 HF5415.1263.H53 2015 Cloth
MACMILLAN
BUSINESS EXPERT
HYNES, GERALDINE E 2022 HD30.3.H96 2015 Paper
PRESS
BUSINESS EXPERT
KAPOOR, AVINASH 2023 HF5415.32.K37 2015 Paper
PRESS
BUSINESS EXPERT
PRINCE, MELVIN 2019 HF5415.2.M37 2015 Paper
PRESS
BUSINESS EXPERT
PRINCE, MELVIN 2017 HF5415.2.M37 2015 Paper
PRESS
PALGRAVE
VERHEZEN, PETER, 1960- 2018 HD59.2.V47 2015 Cloth
MACMILLAN
HF5415.1265W4525
WEINMAN, JOE, 1958- JOHN WILEY 2020 Cloth
2015
BUSINESS EXPERT
YANKELEVITCH, SAM 2016 HD30.3.Y36 2015 Paper
PRESS
BUSINESS EXPERT
YANKELEVITCH, SAM 2017 HD30.3.Y36 2015 Paper
PRESS
COPENHAGEN BUS
2023 HD38.5 Paper
SCHOOL PR
Content
YBP Select US List Slip Date
Level
ADV-AC Research-Recommended $54.99 10/14/2015