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.
Alternatively 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 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 before processing 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.