superguide » 60 Office tips
Excel and Calc
DUPLICATE DATA ENTRY
Want to enter the same data into more
than one cell at once? Enter your first
cell’s data, then click and drag the
selection right (columns) or down
(rows) to fill those cells with the same
information. Once done, click and drag
again if you want to duplicate the data
across multiple cells or columns at
once. To duplicate data across multiple
sheets, select all the sheets (use
Shift-Ctrl as you click each one), then
type your data. It’ll be replicated in the
equivalent cell across your selection.FREEZE ROWS AND COLUMNS
When you work in large spreadsheets,
it’s common to define the first row and/
or column as headings or titles to help
you navigate your data. Of course, as
your spreadsheet grows, those header
fields disappear. Prevent that happened
by ‘freezing’ these key rows or columns
so they always remain visible.
In Excel, click the Freeze Panes
button on the Ribbon’s View tab – you
can opt to freeze just the first row or
column, or freeze a larger selection if
needed (choose Freeze Panes again). In
LibreOffice Calc, you will find both
options (‘Freeze Rows and Columns’
and Freeze Cells > ‘First Column / First
Row’) under the View menu.CLIPBOARD CONSIDERATIONS
Copying data into, between and out of
spreadsheets is an artform in itself.
Start by avoiding the clipboard for
complex data sets in Excel – instead,
use the options in the ‘Get & Transform
Data’ section of the Ribbon’s Data tab to
choose the type of data you’re bringing
in to ensure it’s formatted correctly.
‘Link to External Data’ works in a
similar way in Calc.
When copying and pasting formulas
and data between worksheets, first
Ctrl-click on the worksheet tab where
you plan to copy the data to. Now
navigate to the cell containing the data
you want to transfer – press F2 to
activate the cell, then press Enter to
copy the data into the corresponding
cell in the other worksheet.USE ABSOLUTE REFERENCES
All cell references in spreadsheets are
relative by default – this means if you
copy and paste a cell elsewhere it’ll
change to reflect its new location. For
example, if you copied cell A3
containing the reference =SUM(A1+A2)
to cell B3, it’ll change the reference to
represent =SUM(B1+B2).
If you want cell references to remain
the same wherever they’re posted,
place a $ before the row and column
reference, so =SUM($A$1:$A$4) would
always provide the totals of cells A1, A2,
A3 and A4 regardless of where you
moved or copied the formula around
your spreadsheet. It’s possible to provide
mixed references – $A1, for example,
would always refer to column A, but the
row number would change depending on
where the formula was pasted to;
similarly, A$5 would always refer to row
5, even though the column would change.EASY REFERENCE CELLS
If you frequently refer to specific cells,
assign a memorable name to a cell or
cell range for easy referencing
elsewhere in your document. Cells can
be named with a mixture of letters and
numbers, plus the backslash (\) and
underscore (_) characters. You may not
begin a name with a number.
There are two ways to define names:
one is to simply select a cell or group of
cells, then enter a suitable name into
the Name box (to the left of the ‘fx’ box).
The name will be assigned an absolute
cell reference that includes the sheet
name – Sheet1!$A$1, for example.ASSIGN NAMES TO FORMULAS
Alternatively, click Define Name on the
Ribbon’s Formulas tab (Sheet > ‘Named
Ranges and Expressions’ > Define in
Calc) to define a name for a formula
with relative or absolute references, or
to generate new names from a
combination of existing ones. Just type
the formula into the ‘Refers to:’ or‘Range or formula expression’ box, give
it a suitable name and click OK. To use
the formula, type =name into the cell in
question, replacing name with your
formula’s name.
Use the Define Name box to update
existing names, highlighting another
advantage of using them: update the
cell reference or formula here, and all
of the references across your
spreadsheet will update automatically
too, saving you the hassle of locating
and updating each one manually.EXPAND YOUR SELECTION
To change the number of cells referred
to in a formula, drag the blue range
finder over the cells that you want to
include in your new calculation.VERIFY FORMULAS
If you’re unsure about a formula,
investigate the options in Excel’s
Formula Auditing section on the
Ribbon’s Formulas tab. You can see
which cells are affected by the
formula, troubleshoot errors and also
click Evaluate Formula to work
through complicated formulas to see
how they arrive at their final figure.
Calc’s error-checking tools can be
found in the Function Wizard (Insert >
Function) when building your formula- use the Structure tab to check the
validity at each stage, with red crosses
marking problems that need fixing.
USE CONDITIONAL FORMATTING
Visualising your data can be tricky,
which is where colour comes into play.
And thanks to conditional formatting,
you can colour your cells differently
depending on their value to provide
handy visual alerts. Select your cells in
Excel, then click the Conditional
Formatting button. You’ll see several
presets under Highlight Cell Rules,
such as ‘greater than’ or ‘less than’ – set
your figure and choose the colour to
apply when the rule is met.Conditional formatting enables
you to change the colour of a cell
depending on its current value.If you’re unsure about a
formula, investigate the
options in Excel.