Working with Data in a Spreadsheet

Working with Data in a Spreadsheet

Sort Data

Data can be sorted from the Data toolbar using Sort  menu .

To sort data

  1. Select the data that you wish to sort or arrange.

  2. Click  Data > Sort .

  3. The options available under Sort include

    Sort Ascending

    Sort Descending

    Custom Sort

  4. Choose to sort data according to your required criterion.

Custom Sort  can be used to set your conditions for sorting.

Charts

Charts are tools that help you to visualize and present  data  in a  pictographic  form.

Create a Chart

To create a chart

  1. Click  Insert > Chart .

  2. Click  the type of the chart required.

  3. Select  the data range required for the chart.

  4. Enter the title of the chart and the X and Y axes titles.

Note:
  • The available chart types are: 
  • Bar Chart 
  • Column Chart 
  • Line Chart 
  • Pie Chart 
  • Scatter Chart 
  • Area Chart
  • Web Chart
  • Bubble Chart
  • Stock Chart

Publish a Chart

  1. Click  Publish  present in the menu when you select the chart

  2. Use the code snippet you get to embed or publish your chart.

  3. You can also choose to revert the publishing of the chart by clicking Revert Publish found in the menu.

Edit a Chart

1.  Click  Edit  found in the menu when you select a chart.

2.  You can do your preferred modifications to the chart now.  

Save a Chart as an Image

  1. Click  More > Save Image  present in the menu.

  2. The chart is saved as a Portable Network Graphics (.png) image.

Delete a Chart

  1. Click  More > Delete  from the menu.

  2. The chart will be deleted.

Formulas

Formulas are used to perform calculations that are required to facilitate the understanding of data.  A formula begins with an equal to (=) sign and is followed by syntax that lets you do the required calculations. Formulas can use basic mathematical operations including arithmetic and logical operations.

Insert Function

A function is an inbuilt mathematical operation that helps you perform complicated tasks with relative ease. A function follows the same format as that of a formula and begins with an "equal to" symbol. The syntax varies depending upon the function used.

To insert a function

1.   Click  Formulas > Insert Function .

2.   Choose a function to be inserted from a variety of functions available.

3.   Enter the addresses of the required cells to be used in the function that fits the syntax of the chosen function.

The functions available in Zoho Sheet can be found here

Array Formulas

Array formulas are used when a set or group of numbers are to be  considered as input or output data for operators.  These formulas are generally applied when the data is used in a group together.

To apply array formulas:

  1. Select the cells in which you want the resultant array the result of the output formula to  be present.

  2. Type the array formula you wish to apply over the array. Like other formulas, an array formula is preceded by an "equals" (=) sign.  

  3. You may find it comfortable to name the array you plan to use as input by using  Define Name  feature found in the context menu.
  4. After you enter the formula, press  Ctrl  + Shift + Enter  instead of Enter alone.
  5. The array formula will be executed.

Cell Reference

There are two ways in which a reference to a cell can be made.

Relative Reference

A formula references a cell relatively if the reference changes  when the formula or the function is copied or filled down .

Absolute Reference

A formula references a cell absolutely if the reference to the cell remains constant when the formula or the function is copied or filled down.

To toggle between absolute and relative references:

Absolute reference to a cell is indicated by the use of the symbol "$". To change the reference in a formula

  1. Type the required formula or the function in the resultant cell with all the input ranges to achieve your required result.

  2. Go to the range that you want to change the reference to and press F4.

  3. You can toggle the reference of that range. You can choose only columns, only rows or both rows and columns to be absolute for a cell range.

Recalculate

You can use  Recalculate  after the data present in the cells have changed. If you click Recalculate, all changes in data are accounted for and formula cells are updated.  

Auto Sum

Use  Auto Sum  to calculate the sum of the numbers present along a row or a column.

Define Name

You can define a name for a specific formula that you have created to help you arrive at a result using the given formulas provided in Zoho Sheet. To name a formula,

  1. Select  Formulas  > Define Name .

  2. Enter a new name for your formula in  Add Name   datafield .

  3. Choose what you wish to name. You can choose to name the following

    1. A cell

    2. A range of cells

    3. A specific value

    4. A formula

  4. Click Add.

  5. You can also view the other user generated formulas that have been named in the same window.
     

Goal Seek

Goal Seek is used to solve for faulty values when the target cell does not provide the expected result. In order to use Goal Seek, follow the given steps.

  1. Click  Data > Goal Seek .

  2. Enter required target cell and the cell that gives you a varying result and not the result you were expecting.

  3. The target cell should be formula cell while the variant cell should not be a formula cell for Goal Seek to work.

  4. Click OK to arrive at the required value at the variant cell. 
         

Solver

Solver is generally used in order to arrive at an optimum solution for a given value that follows a given set of conditions. Solver currently supports SUM() and  SUMPRODUCT () functions. To use Solver, follow the given steps:

  1. Choose  Data > Solver .

  2. A pop-up menu is displayed.

  3. Choose the target cell, i.e., the cell which contains the value that is to be optimized. It must contain a formula.

  4. Choose whether you want the target to be maximized, minimized or to be equal to a specific value.

  5. Choose the range of the variable cells, that is, the value of the inputs corresponding to the target cell that is to be optimized. The data present in these cells will be modified according to the optimized value.

  6. You can choose the required constraints for optimizing the required cell. The constraint cells should be formula cells. You can choose the constraints to be lesser than, greater than or equal to a chosen value.

  7. The target cell is optimized based upon the constraints you specify and you receive a prompt asking you to accept or reject the optimized values. Click OK if you want to accept the optimized values.

Filters

Filters are powerful data analysis tools which are used when a particular set of values need to  be analyzed. They are helpful when data need to be sorted and displayed according to a particular condition or criterion.

Select Filter Range

The range in which the filter is to be applied can be selected using any of these methods:

  • Choose the column upon which you want to apply the filter.

  • Choose the range of the filter automatically by clicking a particular cell which is present in the required cell range.

  • Choose the desired filter range by selecting the required cells.

Apply Filter

In order to apply a particular filter, use any of the following methods:

Method 1

After the required filter range is selected, click  Filter  icon    on the menu bar in  Home   menu.

Method 2

Click on  Data > Filter .

Method 3

Right click on the required range and choose Filter from the contextual menu.

Once you choose the filter option, the columns or the data ranges that have been chosen are added with a triangle that can be used to set the conditions required for filtering.    

Choose the required filtering criterion. The available options include the following:

  • Filter by Cell Value  - This filter displays only those cells with the selected value. It displays all the values present in the particular column and lets you filter based upon the value.

  • Filter by Cell Color  - This filter displays those cells with the same background (cell) color.

  • Filter by Text Color  - It displays only those cells which have text in the selected color.
      

You can choose these options right from the menu bar Filter icon or from the  dropdown  menu  that appears on clicking the triangle. Choose the  checkbox  next to the required data cell.

You can also choose to arrange or sort the data according to a particular field.

Custom Filter

There is also an option available to use a filter that satisfies the condition set by you. Click on Custom Filter from the  dropdown  menu from the triangle icon. Custom Filter lets you choose your condition according to which the data must be filtered. 

Depending upon the data type chosen, the options that are used to set the conditions or criterion for filtering vary.  When the data entry is numerical, the options provided are the following:

  • is greater than - displays all numbers greater than the chosen value

  • is greater than or equal to - displays numbers greater than or equal to the chosen value

  • is less than - displays numbers less than the chosen value

  • is less than or equal to - displays numbers less than or equal to the chosen value

If the fields or data to be filtered is made of text fields, you can use the following criterion for filtering.

  • equals

  • does not equal

  • begins with

  • does not begin with

  • ends with

  • does not end with

  • contains

  • does not contain

  • matches

  • does not match

If you choose "contains" from the custom menu, it displays all the fields that hold the particular

phrase entered as criterion.  You can also use custom filtering with dates as sorting criterion. The options that are available to sort filters in the form of dates include:

  • After

  • On or after

  • Before

  • On or before.

The given conditions can be used to sort and display only those dates that follow the set criterion are displayed.

Clear Filter

A filter once applied can be cleared if you want to view the entire spreadsheet again. In order to get the entire sheet back, use the following steps.

  1. After a filter is applied, the column to which the filter is applied by a filter icon in the place of a triangle.

  2. Click on the filter icon and the context menu, click  Clear Filter .

  3. Filters that pertain to the chosen column are cleared.

Remove Filter

When filters are enabled in a sheet, the icon representing filters in the menu bar is displayed. In order to remove filters present in the sheet, click the icon.

Data Validation

Data Validation is useful when the contents of a cell that are to be used as input should pertain to certain conditions or fall within a range. Data validation ensures that data entry remains correct and alerts you if you go wrong in entering data into the spreadsheet.

Create Data Validation

To create a data validation condition for a particular cell, click that cell. If you wish to apply data validation to a range of cells, use drag and drop to select the range of cells.

  1. Click  Data > Data Validation .

  2. You can choose the required range of data validation from the following dialog box.

  3. The options that are available include the following:  Decimal number, Whole number, Text, Text length, Date & Time, List, Cell range .  These options are used to set the required condition.

  4. You can also use data validation in case of data in the form of text. You can choose a list of values that will be entered into a particular column before using data  validation. There will be a  dropdown  list displaying the values you have chosen and you  can  pick your value from the available list. 

You can also set a legend near a particular cell that uses data validation to regulate its data and a warning error alert when the data validation condition is not followed. It can be done from  Create Validation  box.

Clear Data Validation

Data validation can be cleared from a cell or a range of cells if found unnecessary. You can clear Data Validation by performing the following steps.

  1. Choose the particular cell or the range of cells from which you want to clear data validation.

  2. Click  Data Validation >   Clear Data Validation .

A warning dialog box appears. Click OK and data validation conditions set for that particular cell are removed.

Manage Data Validation

Data Validation conditions that are set all over the spreadsheet can be managed using Manage Data Validation in Data Validation menu.  

Manage Data Validation displays all the cells and cell ranges in which data validation has been put in place. You can choose the settings icon near every validation condition in order to edit it, copy it or clear the available validation.

Pivot Tables

Pivot Tables are effective data analysis tools. A pivot table helps you organize and summarize the data available to you.

Create Pivot Table

  1. Click  Data > Pivot > Create Pivot Table .

  2. Give the pivot table a title and choose the range of the cells from which the table is to be constructed. You can also choose to describe the table to be created.

  3. Click  Design Pivot .

Design Pivot

You can design the pivot table according to your specifications.

1. Drag and drop the required fields in either rows or columns.

2. Decide which fields appear as rows and which ones appear as columns in the pivot  table. Set the data fields as required.

3. Use Filters to sort and display only the values that meet fixed criterion for better  analyzing purposes.

4. You can view the preview of the pivot table by clicking  Click Here to Generate Pivot . Click Done when you want to finalize your pivot table. The generated   table is now displayed in a separate sheet.

5. If you wish to edit the created pivot table, click  Edit Design  from the generated pivot  table.

Create Pivot Chart

Creating a pivot chart is similar to the creation of a pivot table. A pivot chart is a visual representation of data available in the form of a pivot table.

  1. Click  Data > Pivot > Create Pivot Chart .

  2. Enter the name of the chart and click  Design Pivot .

Design Pivot Chart

  1. Choose the X-axis, Y- axis, Color and Text required from the available data fields.

  2. You can preview the pivot chart to be generated by clicking  Click to Generate Preview .

  3. Use  Edit Design  if you want to change fields present in the chart.

View Pivot Reports List

  1. Click  Data > Pivot > View Pivot Reports List .

  2. You can view the list of all pivot charts and pivot tables that have been created present in the workbook.

Delete Pivot Chart

To delete a pivot chart, click  Data > Pivot > View Pivot Reports List  and click Delete present near the table or the chart you wish to delete.

Link External Data

Link External Data can be used to link  webpages ,  CSV  Data or  RSS  Feeds.

To link external data

  1. Click  Data > Link External Data . The following dialog box appears. 
       

  2. Choose the type of data you want to link the spreadsheet to.

  3. Set the location where the external data where is to be linked in the spreadsheet. By  default, it is set to the cell selected currently.

  4. Once you choose the location, set how often the data is to be modified or sourced from the webpage.

  5. Click OK.

Freeze Panes

Freeze Panes can be used to distinguish the required cells visible while accessing a large amount of data  present in the sheet.   

To freeze panes

  1. Click  View > Freeze Panes .

  2. The rows and columns that are present before the currently selected cell are frozen in order to be viewed when the sheet is scrolled down.

Note : Freeze Panes can be applied to a maximum of 5 rows and 5 columns.

Freeze First Row

This feature is used to freeze the first row so that it can be used to compare with other entries present in the spreadsheet.  It can be applied from  View  menu or the context menu .

Freeze First Column

Use  Freeze First Column  to retain the first column while you scroll along columns to check and verify data.

Unfreeze Panes

Click  Unfreeze Panes  in View Menu  to remove the frozen state of panes.

 

Text to Columns

Text to Columns is used to convert text with multiple words into separate  columns based on delimiters.

To use Text to Columns

  1. Select the required cell or column which consists of the text you want to convert into separate columns.

  2. Click  Data > Text to Columns . The  Text to Columns  dialog box appears.
     

  3. Choose the delimiters you want to set when text is split into columns.

  4. Set the destination of where the new columns are to be placed.

  5. Click OK.

Lock Cells

Lock Cells can be used when the owner of a spreadsheet wants to ensure that edited cell ranges or sheets cannot be modified anymore. The owner can also ensure that the ranges or sheets shared with other collaborators can be edited by only by those with access to those cells. 

Lock Cells

Locking Cells and Sheets is available only to owners and co-owners of a particular spreadsheet. If you have been shared the sheet with "Read Only, Read/ Write" or "Read/Comment" permissions, then you cannot use Lock Cells.

To lock a range of cells

  1. Select the particular cell or the range of cells that you wish to lock.

  2. Select  Data > Lock Cells . The Lock Cells dialog box opens.

  3. Choose the collaborators for whom you want to lock the particular cell or the range of cells. The default lock settings for the collaborators will be set at off. You can lock the range of cells by clicking the Lock icon present near the respective collaborator for whom you want wish to restrict access.

  4. The Lock icon turns green near the specific collaborator once you have decided to lock those cells for him.

If you lock a specific range of cells, that particular range remains locked by default even for collaborators with whom you share the spreadsheet later once you have locked the range.

Lock Sheet

You can also lock a particular sheet to be made inaccessible to edit for other collaborators.

To lock a sheet

  1. Click  Data > Lock Sheet .

  2. Lock the sheet to those collaborators for whom you wish to lock the cells. The sheet stays locked to newly shared collaborators as well.

  3. You can also lock the sheet by right clicking the required sheet and using the context menu.

You can unlock both cells and sheets in the same way you had locked them. Select the range of cells that you wish to unlock and click the Lock icon near the collaborators to unlock the ranges or sheets.

Manage Lock Cells

Protection applied to various cells can be managed from Manage Lock Cells dialog box.

To manage lock cells

  1. Click  Data > Lock > Manage Locked Cells .

  2. The dialog box shows all the cell ranges and sheets that have been locked by the user for various collaborators. A green lock icon indicates that the particular cell range has been locked for that specific collaborator.

  3. The access to various cell ranges and sheets to various collaborators once provided can be completely managed from this dialog box. You can view, change and delete various lock settings from here.
     

You can lock a specific cell range for a specific collaborator using this dialog box.

Highlight Locked Cells

Highlight Lock Cells lets you highlight and recognize the cells and ranges that have been locked for all collaborators.

To highlight locked cells

  1. Click  Data > Lock > Highlight Locked Cells .

  2. The cells that have been locked for all collaborators will be highlighted and visible now.

  3. To bring cells back to their original form, disable Highlight Locked Cells. The cell ranges will be restored back to their original form.
     

You can view the Manage Lock Settings dialog box by clicking the lock icon present in the range.

Share this post : FacebookTwitter

Still can't find what you're looking for?

Write to us: support@zohodocs.com