10 spreadsheet shortcuts
AllshortcutsworkinbothExcelandCalcexceptwherestatedShortcut Points toF4 Cycle through absolute/relative referencesAlt-Enter Start a new line in the current cellCtrl-1 Open the Format Cells dialog boxCtrl-F2 (Calc) / Shift-F3 (Excel) Insert functionCtrl-D / Ctrl-R Fill from cell above (both)/fill from cell to left (Excel only)Ctrl-Shift-$ / Ctrl-Shift-% Format cell as currency or percentageAlt-= Total up selected cells in cell below (Excel only)Ctrl-Space / Shift-Space Select entire column/rowCtrl-Page Up/Page down Move to previous/next worksheetCtrl-; Insert current dateAlternatively select New Rule for more
options using a set of rule-type
templates. In Calc, you’ll find similar
options under Format > Conditional.
PRINT SUBSETS OF YOUR DATA
By default, both Excel and Calc only print
the active worksheet – you can change
this to print all worksheets or just the
currently selected cells after choosing
Print. You can also define a print area,
which will become the default print
selection for that document going
forward: in Excel switch to the Page
Layout tab on the Ribbon. Select the cells
you wish to include, then click Print Area
Set Print Area (this is where you go to
clear the print area later if necessary). In
Calc, select your cells and head to Format
Print Ranges > Define. You can add
other cell ranges to this list later via
Print Ranges > Add or edit existing
settings via View > Page Break Preview.
AUGMENTING CHARTS
If you’ve created a chart, and want to
add more data to it, select the new data
series (your old data, plus any additional
data), then drag and drop it onto
the chart. When you delete or amend
existing data from your data series this
is automatically updated on your chart.
In Excel, you can move an embedded
chart by right-clicking the chart,
selecting Move Chart and choosing
where to place it – either to a new sheet
or as an object within an existing one.
SORT IT OUT
Re-order data in a sheet by selecting
all the data (to ensure it all gets
rearranged, not simply the column you
plan to sort), then click Sort on the Data
tab of the Excel Ribbon. Choose which
column to sort on, what you’re sorting
(cell values or formatting) and which
order you’re sorting in (A-Z or lowest to
highest or Z-A). A Custom List option
enables you to sort using specific criteria
like days of the week (so Sunday comes
first, then Monday, and so on). Calc offers
a similar set of options via the Sort
button – choose your column(s) under
Sort Criteria, then use Options to define
how they’re sorted.SUMMARISE YOUR DATA
Sometimes you want to produce reports
using different subsets of data – this is
where a Pivot Table or Chart comes in.
You’ll find the options you need on the
Data tab of the Ribbon or under Insert >
Pivot Table (Calc). It can take a little
trial and error to understand exactly
what’s going on – visit http://www.excel-easy.
com/data-analysis/pivot-tables.html
for a beginner-friendly guide.CONDITIONAL FORMULAS
A conditional formula tests parts of a
formula for specific conditions beforeprocessing the calculation. For
example, you can instruct your
formula to either include or skip cells
that have a number greater than, equal
to or less than a specific figure.
There are two functions that can
help here. SUMIF, which lets you apply
a single condition to a calculation. Type
=SUMIF(A1:A5, “>100”), for example,
and the function will only add up those
cells that contain a number greater
than 100 in them. For more
complicated conditional formulas, use
the =IF command in conjunction with
the Formula or Function Builder.CELL FORMAT TRICKERY
Change the colour of a worksheet tab
to make it more easily identifiable by
right-clicking the tab and selecting
‘Tab colour’. If you’re struggling to get
text to fit in the usual direction in a
cell, rotate it: To do so in Excel, click
Format on the Home tab and choose the
Format Cells > Alignment tab. In Calc
it’s Format > Cells > Alignment tab.Align text vertically if
you’re struggling to make
it fit the available space.