Paste Special I

In a series of short tips, we introduce the Paste Special feature.

A quick and easy way to multiply an array of numbers by a constant without using a formula…

In the example below, we wish to multiply B5:B20 by 10. Temporarily enter 10 into an empty cell. Select it and then Cut or Copy

Select the target range, then click on Edit – Paste Special . In the Paste Special dialog, unselect Paste All and Formats. Also select Multiply. Now click OK …

We are done! The modified cell range.

Read More

Paste Special II – Removing formulae

The paste special feature is useful for preserving the values in a spreadsheet – but removing the formulae that generated those values. This may be required to preserve proprietary equations.

To remove the formulae for a range of cells, select and copy the desired range. Do not unselect the range. Now click on Edit – Paste Special – see below.

Make sure the Formulas box is unchecked. After you click OK, the formula is removed but the value is preserved.

Read More

Webquery : Importing HTML tables

With the help of the Web Page Query OpenOffice.org Calc import filter, you can insert tables from HTML documents in a OpenOffice.org Calc spreadsheet.

Let us step through the relatively straightforward process of importing data from tables in a HTML document.

The data for this example was obtained from an excellent website with tons of population data from all over the world : citypopulation.de

Here is what the source HTML file looks like : swiss.html

For this example, we will be importing awebpage from the local disk.

The External Data dialog is activated by Insert-External Data.

Once we fill in the path to the local copy of the HTML file and the table inside this file we wish to import – we hit OK and we are done – it is that easy.

Read More

Basic functions : SUMIF

This short tutorial illustrates the two basic ways that SUMIF can be used in a spreadsheet.

The SUMIF function takes two or three arguments.

For the two argument version, the condition is applied to each cell in the range being SUMmed. In the example below, C9 is the sum of all values in C3:C8 that are greater than 5.

In the three argument version, the condition is applied to a separate range of cells. This range can be numerical of textual. In the example below, the values corresponding to Tom are summed in C10

Read More

Freezing row & column headers

For large spreadsheets that do not fit completely in the window, it is useful to be able to freeze the row and/or column headers to better view the data.

Consider the example below, where we have 12 columns and a large number of rows. By selecting the cell below and to the right of A1 and selecting Window – Freeze , you will now be able to scroll down or to the right while keeping the top row and left column frozen in place.

Note that once you close a sheet, the above setting disappears and you will need to refreeze your window when you reopen the sheet.

Read More

Basic functions : SUMIF

This short tutorial illustrates the two basic ways that SUMIF can be used in a spreadsheet.

The SUMIF function takes two or three arguments.

For the two argument version, the condition is applied to each cell in the range being SUMmed. In the example below, C9 is the sum of all values in C3:C8 that are greater than 5.

In the three argument version, the condition is applied to a separate range of cells. This range can be numerical of textual. In the example below, the values corresponding to Tom are summed in C10

Read More

Basic Functions: INDEX, MATCH

We have already seen a table lookup example using the OFFSET function.

Here, we perform a similar operation using INDEX and MATCH

The example below show how well INDEX and MATCH can work together.

MATCH will return the position of a particular value in an array, while INDEX returns the value of a particular position within an array. We see multiple examples of these functions in operation below.

PriceMatrix is C4:E6 Material is C3:E3 Item is B4:B6
Using these names in the formulas below makes for easier readability nad maintenance. For this reason, it is always good practice to take advantage of the ability to name arrays that will be used within formulas.

Read More

Paste Special III: Merging rows

It has been decided to merge the Mungits and Wingdings product lines. We will use Paste Special to merge the sales totals in the spreadsheet below.

Step 1: Select C6:F6 and Edit – Copy

Step 2: Select C5:F5 and Edit – Paste Special

The Paste Special dialog opens. In this situation, we apply the ADD operator to the paste operation which has the effect of adding the pasted data to the existing contents of the selected cells.

Having merged the data, we can safely delete the Wingdings row in the table.

Read More

Basic functions: TEXT

At first glance, the TEXT function may not sound very useful. Here are a few simple applications.

In the top example, without the TEXT function, the total is not formatted correctlt (as a currency). However, the TEXT function allows us to apply the correct format to the number component of the string.

In the second example, we see how the TEXT function allows us to reformat existing data – such as dates.

Read More
  • 1
  • 2