introduction to basic spreadsheets
TRANSCRIPT
Introduction to Spreadsheets
Kingston TagoeTechpreneur & IT Consultant
Foundational Training Program
Objectives for this session
• Understand what spreadsheets are• Fundamental operations in Excel• Learn essential tasks• Data manipulation – tips and tricks
What Are Spreadsheets
The computer equivalent of a paper ledger sheet. It’s an electronic document in which data is arranged in rows and columns. This data can be manipulated and used in calculations.
Why Spreadsheets Matter
• Easy formatting of data• Organize data by sorting it• Name ranges of data and use those range names
in formulas • Use cell references rather than values in
formulas• Generation of charts and graphs • Other reasons?
Types of Spreadsheet Apps
Alternatives
Working with Excel Spreadsheets
Excel Spreadsheet Layout
View Options and Zoom Control
Status Bar and Sheets
Ribbon
Row ColumnScroll Bar
Quick Access Toolbar Window Controls & Help
Understanding the Ribbon
• File – managing files• Home – common tools• Insert – insertion of objects (pictures,
charts etc.)• Page Layout – printing and structure of
page• Formulas – functions, calculations• Data – working with data• Review – spell checks, protection,
sharing• View – how Excel appears on your
screen
Spreadsheet Basics
Each Excel file is a workbook and hold many worksheets. Worksheets are made of rows, columns, and intersections called cells.
Each cell on the spreadsheet has a cell address that is the column letter followed by the row number.
Basic types of data that can be entered into a cell:1. text (eg. ”Education”) 2. numbers (such as ”4”)3. formulas (mathematical equation, such as ”=9*4”)
Excel Labs – We’ll work on:
• Data selection• Spreadsheet navigation• Data entry and revisions:
oFinding and replacing dataoFilling dataoSorting data
• Formatting of cells
• Other useful skills
Excel Labs – Data Selection
Action ResultCtrl + Spacebar
Highlight the entire column
Shift + Spacebar
Highlight the entire row
Ctrl + A Highlight the entire worksheet
Data selection keyboard shortcuts
Use the mouse or keyboard shortcuts to select cells, rows, and columns of data: • Single clicking on the column (or row) label
will highlight the whole column (or row).• Clicking and dragging across several row or
column labels will highlight several rows or columns, respectively.
Non-adjacent cells can be highlighted by holding down the Ctrl key and using the mouse to single click the desired cells.
Excel Labs – Spreadsheet navigation
As you enter and edit data you’ll need to move through the worksheet using either the mouse or keyboard shortcuts.
Action ResultSingle Click Cell
Make cell active
Enter Move active cell downShift + Enter Move active cell one
cell upTab Move active cell one
cell to the rightShift + Tab Move active cell one
cell to the leftHome Move active cell to
column A of current rowCtrl + Home Move active cell to A1Ctrl + End Move active cell to last
cell in the spreadsheetCtrl + Page Down
Move to next worksheet
Ctrl + Page Up Move to previous worksheet
Some navigation techniques
Excel Labs – Entry and revision
Using Cut, Copy and Paste
Using the drag-and-drop method to move data:1. Select the cells you wish to move 2. Point to an outside border of the cell3. Click and drag the cell(s) to the new location
The data is removed from its original location and overwrites the contents in the destination location, just as when you apply cut and paste.
Action ResultCtrl + C CopyCtrl + X CutCtrl + V Paste
Cut, Copy and Paste Shortcuts
Excel Labs – Find and replace data
Action ResultCtrl + F FindCtrl + H Replace
Find and Replace shortcuts
Using Find or Replace from the Edit drop menu allows you to quickly find and/or replace text or numbers in multiple cells
Excel Labs – Filling data
Autofill feature allows you to quickly fill in commonly used series of data, such as repetitive or sequential data.
To use Autofill: 1. Type in the first few elements of the series in order
for Autofill to distinguish the pattern (eg. 2, 4, 6)2. Highlight cells distinguishing the series 3. Select the (cross) handle at the bottom right corner of
the cell with the left mouse and drag it down across as many rows as you want to fill
4. Release mouse button when done
Can also Autofill across columns by dragging right instead
Excel Labs – Sorting data & formatting
Action ResultCtrl + B BoldCtrl + I ItalicizeCtrl + U UnderlineCtrl + 5 StrikethroughShift + Ctrl + $
Format as currency with 2 decimal places
Shift + Ctrl + %
Format as percent with no decimal places
Formatting keyboard shortcuts
Formatting optionsUsing the Format Cells Dialog BoxWorking with Format PainterConditional formatting of cellsWorking with Styles
Sorting DataSort in the Ascending & Descending OrderWorking with Sort Dialog BoxNon-alphabetic or numeric sort
Tips, useful skills
Action ResultCtrl + O OpenCtrl + N Open a new fileF12 Save AsCtrl + S SaveCtrl + P PrintCtrl + Z UndoCtrl + Y Redo
Additional keyboard shortcuts
Add & delete a rowAdd & delete a columnResize rows and columnsFreezing panes and allowing for scrollingPrinting a worksheetData import and export
Worksheet modification – How To:
Thanks for working with Excel Spreadsheets