The datasheet is a spreadsheet that is used to enter, edit, or format the data that a data chart displays.
In this section, you'll learn about
manipulating datasheets
adding and copying data
removing data
working with datasheet cells
formatting datasheet cells
working with rows and columns
working with data ranges
using formulas and number fills
For further information about working with datasheets, see "Reference: Working with datasheets."
Manipulating datasheets
When you have finished entering data in a datasheet, you can hide the datasheet. You can also move a datasheet to a new location on the screen, and you can adjust the size of a datasheet. In addition to displaying datasheet information in a chart, it can be displayed in a table.
To hide a datasheet
1 Double-click a data chart.
2 Click View Datasheet.
No check mark beside the Datasheet command indicates that the datasheet is hidden.
Note
The datasheet displays by default.
Tip
You can also view or hide a datasheet by clicking the View datasheet button on the toolbar.
To move a datasheet
Drag a datasheet to a new location in the drawing window.
To size a datasheet
1 Point to any side or corner of a datasheet.
2 Drag to size the datasheet.
Dragging inward reduces the size of the datasheet; dragging outward increases it.
To display datasheet information in a table
1 Click a datasheet.
2 Click Chart Layout/type.
3 Click Table.
4 In the Table properties dialog box, enable the Display table check box.
Tip
You can also display datasheet information in a table by clicking the Show table button on the toolbar.
Adding and copying data
Entering and editing data in Corel Presentations datasheets is similar to entering data in a spreadsheet application such as Quattro Pro. The datasheet consists of cells in which you enter data. These cells display in a series of rows and columns in the datasheet.
The following illustration shows what a datasheet looks like.
You can also import spreadsheet data from files created in other applications into a data chart. If you link the imported data to a chart, any changes you make to the chart data in a spreadsheet application, such as Quattro Pro, are automatically updated in the Corel Presentations chart data every time you open the chart.
As well, you can export a datasheet data.
To enter or edit data
1 Click a cell on a datasheet.
2 Click Edit Edit cell.
3 Type data in the Data box.
Tip
You can also enter or edit data by typing directly in a cell.
To import spreadsheet data
1 Click a datasheet.
2 Click Data Import.
3 Choose Spreadsheet from the Data type list box.
4 Enable any of the following check boxes:
Transpose data-switches data from columns to rows and rows to columns
Clear current data-deletes all the data already in the datasheet
Link to spreadsheet-links the imported data to the data chart, resulting in automatic updating of the data when the spreadsheet is changed
Import at current cell-imports data at the selected cell
5 In the Filename box, type the path and filename of the import file.
6 Type a value in the Range box to import a range of data.
Tip
You can also import a predefined range of data by choosing a range from the Named ranges list.
To export data from a datasheet
1 Click a datasheet.
2 Click Data Export.
3 Choose the drive and folder to which you want to export the data.
4 Choose a file format and delimiter type from the File type list box.
5 Type a filename in the Filename box.
6 Click Export.
Removing data
You can clear all of the data in a datasheet, and you can clear specific cells of data in a datasheet.
To clear all data from a datasheet
1 Click a datasheet.
2 Click Edit Clear all.
To clear data from a cell
1 Select a cell.
2 Click Edit Clear.
3 Enable the Data option.
Working with datasheet cells
The Corel Presentations datasheet consists of a series of cells in which you enter or edit chart data. You can select all of the cells in a datasheet.
By highlighting each area with a color in the range highlighter, you can distinguish between datasheet data and the legend and label areas in the datasheet. For information about legend and label areas, see "Working with data charts."
While editing a chart, you can move to any cell in the datasheet by specifying the column and row of the cell to which you want to move. For example, typing C4 specifies the cell at the intersection of the fourth row and the C column.
To select all cells
1 Click a datasheet.
2 Click Edit Select all.
To highlight ranges
1 Click a datasheet.
2 Click View Range highlighter.
3 Enable the View highlighted ranges check box.
4 Open the Legend picker, and click a color.
5 Open the Labels picker, and click a color.
6 Open the Data picker, and click a color.
To move to a cell
1 Click a datasheet.
2 Click Edit Go to cell.
3 Type a column letter and a row number in the Cell box.
Formatting datasheet cells
All the cell data in a datasheet is either a number or a date. You can format the numbers to include percentage signs, dollar signs, and so on. You can create a new numeric data format, or you can choose a numeric format from a predefined list. The formatting you choose applies only to the selected datasheet cells. As well, you can choose date formats for the dates in data charts. You can also create a custom date format. If you want to format cells that do not contain number or date data, such as legend titles, you can choose a format that that does not apply any specific attributes to the cells. You can also clear a format from a cell.
To create a numeric data format
1 Click the datasheet.
2 Click Data Format.
3 Choose Numeric from the Format type list box.
4 Choose User defined from the Numeric formats list.
5 In the Type area, enable one the following check boxes:
Currency
Thousands
Percent
Exponential
7 In the Negative numbers area, enable one of the following options:
Minus sign
Parentheses
You can also
Adjust the number of decimal digit places to the size of the cell Enable the Floating check box.
Choose the number of decimal digit places that display in a cell Type a value in the Digits box.
Note
You can preview a numeric format in the Numeric preview preview window.
To format dates
1 Click a datasheet.
2 Click Data Format.
3 Choose Date from the Format type list box.
4 Choose a format from the Date/time formats list.
To create a custom date format
1 Click a datasheet.
2 Click Data Format.
3 Choose Date from the Format type list box.
4 Click Custom.
5 In the Custom date/time format dialog box, click the Day tab.
6 Choose a day code from the Day codes list.
7 Click Insert.
You can also
Create a custom month format Click the Month tab. Choose a month code from the Month codes list. Click Insert.
Create a custom year format Click the Year tab. Choose a year code from the Year codes list. Click Insert.
Create a custom time format Click the Time tab. Choose a time code from the Time codes list. Click Insert.
Notes
The date format you create displays in the Date/Time sample preview window at the bottom of the Custom date/time format dialog box.
When you enter two-digit years (dd/mm/yy) in a datasheet, 00 to 50 corresponds to the years 2000 to 2050, and 51 to 99 corresponds to the years 1951 to 1999. For example, the two-digit year 26 corresponds to the year 2026. The two-digit year 61 corresponds to the year 1961.
To format to cells not containing numbers or dates
1 Click a datasheet.
2 Click Data Format.
3 Choose General from the Format type list box.
To clear a format from a cell
1 Select a cell.
2 Click Edit Clear.
3 Enable the Format option.
Working with rows and columns
You can insert or delete rows and columns in a datasheet. When you insert a row, it is placed above the selected cell. When you insert a column, it is inserted to the left of the selected cell.
To insert a row or column
1 Click a datasheet cell.
2 Click Edit Insert.
3 Enable one of the following options:
Row
Column
4 Type a value in the Number to insert box.
To delete a row or column
1 Click a datasheet cell.
2 Click Edit Delete.
3 Enable one of the following options:
Row
Column
4 Type a value in the Number to delete box.
To exclude data
1 Click a datasheet cell.
2 Click Data Exclude row/col.
3 Enable one of the following options:
Row(s)
Column(s)
Note
When you exclude rows and columns, you can still change the data they contain.
To include excluded data
1 Click a datasheet cell.
2 Click Data Include row/col.
3 Enable one of the following options:
Row(s)
Column(s)
To modify column width
1 Click a datasheet.
2 Click Data Column width.
3 Type a value in the Width box.
Working with data ranges
You can sort the data in the rows and columns of datasheets from top to bottom and from left to right, or in ascending or descending order. Numeric values display first, text values display second, and empty cells display last.
You can also transpose data ranges, which switches the order of the data in rows and columns. The examples in the table below show data before and after transposing.
Before After
1 2 3 1 4 7
4 5 6 2 5 8
7 8 9 3 6 9
You can display statistics about data in a row, a column, such as the average or standard deviation of data found in a row or column.
To sort a data range within rows
1 On a datasheet, select the rows you want to sort.
2 Click Data Sort.
3 In the Direction area, enable the Left to right option.
4 Type a value in the Key row box.
5 In the Order area, enable one of the following options:
Ascending
Descending
Note
To sort data including the legend and labels headings, you must select the entire row or column, including the legend and labels.
To sort a data range within columns
1 On a datasheet, select the columns you want to sort.
2 Click Data Sort.
3 In the Direction area, enable the Top to bottom option.
4 Type a value in the Key column box.
5 In the Order area, enable one of the following options:
Ascending
Descending
Note
To sort data including the legend and labels headings, you must select the entire row or column, including the legend and labels.
To transpose a data range
1 On a datasheet, select the cells you want to transpose.
2 Click Edit Cut.
3 Select the cell where you want transposed data to begin.
4 Click Edit Paste transposed.
Note
When you transpose data, the selection should contain the same number of rows and columns. If you select more of one than the other, the larger of the two numbers would be taken and the data would be transposed as if you had specified that number of rows and columns. If that happens, the transposed data may overwrite other data in a datasheet.
To display statistics about a data range
1 Click a datasheet cell.
2 Click Data Statistics.
3 In the Display statistics for area, enable one of the following options:
Row
Column
Using formulas and number fills
You can apply formulas while working in a datasheet. You can perform several functions that are found in spreadsheet programs such as Quattro Pro. For a list of the available formulas, see "Reference: Working with datasheets." The results are reflected in the data chart. For example, you can use functions to calculate cumulative totals and averages and to project future data values.
When creating data charts in Corel Presentations, you can fit data to a curve and forecast trend lines, curves, and regression types. Forecasting allows you to predict values such as future population growth, sales potential, and future costs.
The cells in a datasheet can be filled automatically. Each row in the datasheet is one of three series. The Date series begins with the start date and is multiplied by the step number. For example, if the step number is 3, and the date step is a week, the interval is 3 weeks. The Linear series begins with the start value and adds the step number to create each new number in the series. For example, if the step number is 3, the difference between each cell in the fill is 3. The Growth series begins with the start number and is multiplied by the step number to create each new number in the series. For example, if the step number is 3, the interval is a multiple of 3: 1, 3, 9, 27.
To add a formula to rows
1 Click a cell that contains data.
2 Click Data Formulas.
3 In the Use data found in the same area, enable the Row option.
4 Choose a formula from the Use the function list.
If you choose Moving average from the Use the function list, type a value in the Average before, after box.
5 In the Calculate the area, type column letters in the Column(s) box.
6 In the Place the results in area, type a column letter in the Column box.
Tips
If you are working with a scatter chart, use 0 as the results row to indicate the variable row.
You can also add a formula to columns by enabling the Column option.
To add a curve fit formula to rows
1 Click a datasheet cell.
2 Click Data Formulas.
3 In the Use data found in the same area, enable the Row option.
4 Choose one of the following formulas from the Use the function list:
Fit exponential
Fit log
Fit polynomial, and type a value in the Order of curve fit box
Fit power
5 In the Calculate the area, type column letters in the Column(s) box.
6 In the Place the results in area, type a column letter in the Column box.
7 Type a value in the Number to forecast box.
Tip
You can also add a curve fit formula to columns by enabling the Column option.
To create a linear regression trend chart for rows by using a 3-D bar chart
1 Click a datasheet cell in a blank row.
2 Click Data Formulas.
3 Choose Fit linear from the Use the function list.
4 In the Use data found in the same area, enable the Row option.
5 In the Calculate the area, type column letters in the Column(s) box.
6 In the Place the results in area, type a column letter in the Column box.
7 Type a value in the Number to forecast box.
This value specifies a value to predict future values. For example, if you have plotted data for each financial quarter, typing 2 forecasts data for two future quarters.
Notes
You can add additional labels, a chart title, and a legend to the datasheet for the forecasting results column.
You can also create a linear regression trend chart for columns by enabling the Column option.
To recalculate formulas
Click Data Recalculate.
To fill cells with numbers automatically
1 Select two or more datasheet cells.
2 Click Data Fill.
3 In the Direction area, enable one of the following options:
Rows
Columns
4 In the Type area, enable one of the following options:
Linear
Growth
Date, and choose a date/time interval from the list box beside it
5 In the Values area, type a value for the series in the Start box.
6 In the Step box, type a value to indicate the rate of increase for the series.
Notes
When you enter two-digit years (dd/mm/yy) in a datasheet, 00 to 50 corresponds to the years 2000 to 2050, and 51 to 99 corresponds to the years 1951 to 1999. For example, the two-digit year 26 corresponds to the year 2026. The two-digit year 61 corresponds to the year 1961.
Dates entered in a datasheet are stored as numbers. For example, if you typed 1/1/1900, the number 1 would represent that date in the datasheet.
Tip
You can specify negative values if you want a descending series.
Reference: Working with datasheets
You can use formulas in a datasheet to calculate the data you include. You can also import different spreadsheet formats into Corel Presentations when you want to include additional information in a data sheet.
Corel Presentations import spreadsheet formats
You can import the following spreadsheet formats into Corel Presentations:
Corel Quattro Pro for Windows 95 (versions 7-9)
Corel Quattro Pro for Windows (versions 5-6)
Quattro Pro (versions 2-4)
Lotus for Windows (versions 4.0-5.0)
Lotus 1-2-3 (versions 2.01-3.1)
Microsoft Excel (versions 2.1-5.0)
PlanPerfect (versions 3.0-5.1)
Available datasheet formulas
The following table contains a list of formulas you can use.
Formula Result
Absolute value Takes the value of the number in each cell in a single row or column without regard to its sign. If the data is 3, -4, -2, the absolute values are 3, 4, 2.
Average Adds all data values in a row or column, and divides that number by the number of data cells. For example, 1,2,3, 1:3, or 1...3 adds the values in rows 1 through 3 and divides them by the number of cells containing data in those rows. A:C, A..C, or A,B,C averages values in columns A through C.
Cosine Takes the cosine of each value in one row or column. The cosine is the ratio between the adjacent side and the hypotenuse of a right triangle expressed as an angle in radians.
Cumulative total Adds the values in a row or column, and adds that total to the next row or column, thus taking a running total of one or more rows or columns. For example, 1,2,3, 1:3, or 1...3 totals row 1, and adds that total to the totals for rows 2 and 3. A:C, A..C, or A,B,C totals columns A through C.
Difference Subtracts one cell from the previous cell by row or by column. The difference for the first cell in a row or column will always be 0, because there is no previous cell.
Maximum Compares cell values in a row or column with corresponding cell values in the next row(s) or column(s) to find the largest value. For example, 1,2,3 displays the largest value in rows 1 through 3, while A:C or A..C displays the largest value in columns A through C.
Minimum Compares cell values in a row or column with corresponding cell values in the next row(s) or column(s) to find the smallest value. For example, 1,2,3 displays the smallest value in rows 1 through 3, while A:C or A..C displays the smallest value in columns A through C.
Moving average Smoothes the curve of data by charting a moving average. Moving average adds the value of each cell to the values of the cells surrounding it, and divides by the number of cells. You specify how many cells before and after each cell to include in the moving average. For example, if you type 2, the moving average for each cell will be the average of the current cell and the two cells on each side (a total of five cells). If you type 1,2, the formula will include one cell before and two cells after the current one in the formula.
None Deletes a formula in a row or column, leaving only the data.
Percent of total Calculates the cell value as a percentage of all cells in a row or column. The resulting percentages are expressed as decimal numbers.
Sine Finds the sine of each value in a row or column. A sine is the ratio between the opposite side and the hypotenuse of a right triangle built on the angle expressed as an angle in radians.
Sum Adds all the values in two or more rows or columns. For example, 1,2,3, 1:3, or 1...3 adds all the values in rows 1 through 3, while A:C, A..C, or A,B,C adds the values in columns A through C.
Available datasheet fit formulas
The following table contains the fit formulas available for charts.
Fit formula Result
Fit exponential Fits the data in a row or column to an exponential curve: ln(y)=a+bx.
Fit linear Fits the data in a row or column to a straight line: y=a+bx.
Fit log Fits the data in a row or column to a logarithmic curve: y=a+b(ln(x)).
Fit polynomial Fits the data in a row or column to polynomial curves: y=c0+c1x+c2x▓+cn*x¬ (where a is the order of the fit). The higher the order (any number up to one less than the number of data points), the more curvature is allowed. This formula provides the most accurate and close fit for inconsistent and fluctuating data. Fit polynomial is useful for filling in the unknown y-axis data points along a curve but is not as useful for forecasting.
Fit power Fits the data in a row or column to a power curve: ln(y)=a+b*ln(x).
