This Cyber Monday Envato Tuts+ courses will be reduced to just $3. Don't miss out.
If you're looking for a free alternative to Microsoft Office, you've likely heard of Google Docs, and may have even come across Microsoft's own Office.com web apps. But, one of the most full-features Office alternatives online today is from a company you might have not heard of before: Zoho. It's Zoho Docs app is a great online word processor that you can learn how to use in our previous tutorial, and it also has great spreadsheet and presentation apps to compliment it.
In this tutorial, you'll learn how to use Zoho's free Zoho Sheet app to edit and create full-featured spreadsheets from your browser.
Making Spreadsheets in Zoho Docs
You'll first need a Zoho account to get started with Zoho Sheet's spreadsheets. Just signup at docs.zoho.com, or sign into Zoho Docs with your existing account. Then, head back to your Zoho Docs home menu and click the Create button. From the drop-down menu, click Spreadsheet.
You'll now be brought to a new Zoho Sheet window. As with Zoho Writer, you can name your Sheet by clicking on the Untitled document button on the upper-right-hand corner of the screen and typing in its new name. Tap the Return key on your keyboard when you're finished naming your Sheet.
Like you would in Excel, you can edit and add information to your Sheet by clicking into one of the various cells in the Sheet. Once in a cell, you can type in the value and navigate between cells using the arrow keys on your keyboard.
The Home Tab
Like in Zoho Writer, Zoho Sheets is made up of multiple tabs with the first being the Home tab. From the Home tab, you can make basic edits to your document. For example, you can cut, copy, and paste from this tab using their respective buttons from the left-hand side of the window.
Towards the center of the tab you'll find font type, size, and color options as well as bolding, crossing-out, and italicizing options. Towards the right-hand side of the window, you can play with border options from the Border button and align text using the alignment options.
Using Zoho Sheet's Format Painter
If you look towards the left-hand side of the window, you'll see an icon that looks like a paintbrush. When clicked, this button will bring you to the Format Painter. The format painter allows you to copy the format and style of a cell and apply it to another cell.
To use the Format Painter, click on the Format Painter button and click into the cell whose style you'd like to copy. Then, click into the cell(s) you'd like to apply said cell's format too. It will be applied automatically.
Using The Auto Sum Feature
Like in most popular spreadsheet programs, Zoho Sheets has an Auto Sum feature. Auto Sum allows you to quickly add up values in a range of cells. To use Auto Sum in Zoho Sheet, click into a blank cell that you'd like your sum to appear in. Then, look towards the right-hand side of the Sheet window and click on the Auto Sum button. This button is shaped like the letter "E".
Now, click on the cells that you'd like to add together. You can do this by clicking-and-dragging over said cells. Then, press the Return key on your keyboard to have the cells added.
Inserting and Deleting Columns and Rows
Compared to other Spreadsheet apps, Zoho Sheet hides column, row addition, and removal tools in a strange spot. To insert or delete a column or row, look to the far right-hand side of the Home tab and click on the row or column buttons. From the drop-down menu, choose whether you'd like to add or delete the column or row you're currently clicked into.
From the bottom of these drop-down menus, you can also change your column's and/or row's width/height by clicking on the width/height button. From the pop-up, change the value and press the OK button that is located towards the bottom of the menu.
The Format Tab
Like you might expect, the Format tab in Zoho Sheet is where you can do more in-depth formatting to your spreadsheets. Starting from the left-side of the tab, you'll see a button that is shaped like a calendar. You can use this button to format dates and times that show up in your document.
Additionally, to the right of the calendar icon, you'll see a dollar sign icon. This button can be used to add a currency symbol to any cell that has a number in it. Clicking on the button will give you a pop-up menu with multiple currency icons.
To apply a currency symbol to a cell, make sure you're clicked into the cell that you'd like to assign a currency symbol to. Then click on the currency button and select the symbol you'd like to use from the drop-down menu. It will then be applied.
If you would like to turn a decimal into a percent, you can do so by clicking on the percent button that is located to the right of the currency button. Or, if you'd like to increase or decrease the number of digits after a decimal point, you can do that using the decimal increase and decrease buttons to the right of the percent button.
Further Formatting Your Cells
If the type of formatting you would like to apply to your document doesn't have a shortcut, don't fret! Look towards the center of the Format tab and click on the Format Cells button. From here, you can choose from a ton of other formatting options including Regional, scientific, fraction, and other options. Formatting cells by way of the Format Cells window.
To apply a formatting option from the Format Cells pop-up window, click into the cell you'd like to add the formatting to and then click on the Format Cells button. Now, select the type of formatting you'd like to apply to your cell from the sidebar and look towards the bottom of the window where you'll find a preview of your formatting. If it looks right in the preview, click the OK button that is located at the bottom right-hand corner of the window.
Diving even deeper into formatting, Zoho Sheet lets users automatically format cells using conditions. To setup conditions, click on the Conditional Formatting button that is located to the far right-hand side of the Format tab. When you do this, a pop-up window will appear.
Look towards the top of the pop-up window at the Applies To: textbox. Click into this textbox in order to choose what cells the following formatting applies too. To select the cells, just click and drag over them and click the box button to the right.
To create rules for formatting, look towards the center of the screen where you can set up up to three rules. To set up a rule, click into each drop-down menu and select the information you'd like to have formatted. You can then set up the formatting. To preview your conditional formatting click on the Preview button that is located to the right of each rule. To apply your new rules, click the OK button that is located towards the bottom right-hand corner of the conditional formatting pop-up window.
The Insert Tab
The Insert tab is where you can, well, insert things into your spreadsheet. To insert new columns and rows via the Insert tab, just click their respective buttons from the left-hand side of the tab.
You can add images to your spreadsheet by way of the Image button from the center of the Insert tab. From the pop-up menu, you can choose to either upload a photo or pull one from a URL. Once your photo has been uploaded, click the Insert button from the bottom right-hand corner of the window.
Adding hyperlinks to a cell is simple as well. Just highlight the cell you'd like to add your hyperlink to and click the hyperlink button from the right-hand side of the Insert tab. You can then add your hyperlink via the pop-up window.
Adding Charts To Your Spreadsheet
Like in Microsoft Excel, Zoho Docs can add charts to a spreadsheet. To do this, click on the Chart button from the center of the Insert tab and look at the pop-up menu. You'll be prompted to select the type of chart you'd like to add to your document. For this example I'm going to use a line graph.
You'll now be prompted to choose your data range. To do this, click into the Data Range
textbox from the top of the Add Chart box. You can select your data range by clicking-and-dragging over the data you'd like to use in your graph. Then, fill out the rest of the information on the page as appropriate. When finished, click the Next button that's located at the botton right-hand corner of the window.
You can now name your chart as well as the X and Y axis. When you're done doing this, click the Done button that is located towards the bottom of the window.
Creating Macro Buttons
If you often find yourself using the same macro (more on those later) in a Zoho Sheet, you can always create a button for said Macro in your sheet. To do this, click the Button button from the far right-hand side of the tab. A new button will appear on your screen. Name this button and then press the Return key on your keyboard.
You'll now notice a small pop-up window with three options: Assign Macro, Edit Label and More. From the Assign Macro button, you'll be able to choose what macro you'd like to make your new button execute. Clicking on the Assign Macro button will let you either create a new Macro (I'll be covering this later) or choose an existing one.
Like you may have guessed, the Edit Label button lets you edit your button's name. If you click into the More button, you'll be given two options: clone and delete. These two buttons do exactly that they say: clone and delete your button.
The Formulas Tab
Clicking into the Formulas tab will give you access to basic formula functionality found in Excel and other popular spreadsheet applications. On the left-hand side of the tab, you'll see a button labeled Insert Function. Clicking on this button will give bring you to a window filled with different functions you can use in your spreadsheet.
To use a function, you can click on its name to use it in your current cell. The process to use each function is different, so I won't be covering the specifics in this article. Documentation for each function can be found on Zoho's reference page.
If you ever need to refigure a function in one of your cells, you can do so by clicking the Recalculate button from the left-hand side of the tab. You may need to do this if you change a value in your cell.89
Defining Names In Zoho Sheet
Formulas can be pretty difficult to understand, namely due to the fact that their syntax is strange. For example, finding the sum of cells C20 to C30 is =SUM(C20:C30). To make it a little easier on the eyes, Zoho Sheet lets you name these formulas using the Define Name button.
When you click on this button, a pop-up window will appear. Towards the top of this window, you'll see a header appropriately named Add New Name. Directly below this header, input the name you'd like to use for your formula. To the right of this box you'll see a box labeled "Refers to:". In this box, input the formula you'd like that name to be associated with. Then, click the Add button.
You can view your formula names under the Defined Names header at the bottom of the pop-up window.
The Data Tab
The Data tab in Zoho Sheet is a pretty broad tab. For instance, you can sort data using the Sort button, or you can create pivot tables and validate data. I'll be covering each of the main ideas of this tab here.
Creating Pivot Tables In Zoho Sheet
Pivot tables are essentially a quick way of viewing a summary of large amounts of data at once. For example, if you own a coffee roastery and want to summarize your sales per shop, per origin, and per roast, you can do so with pivot tables.
To create a Pivot table, click the Pivot button from the left-hand side of the Data tab. From the drop-down menu, click on the Create Pivot Table button. From the pop-up menu, fill out the required information and select the data range. When you're done, click the Design Pivot button.
From the design window, you'll be able to drag-and-drop your columns into their respective boxes on the right-hand side of the window. When you're done designing your pivot table, click the Click here to generate pivot button to preview, and then click the Done button to add the pivot table to your spreadsheet.
Goal Seek In Zoho
Goal Seek alters data in a formula to find results. To use Goal Seek in Zoho Sheet, click the Goal Seek button from the center of the data tab and look towards the top of the pop-up window. In this pop-up window, you can input the target and variant cells by clicking on them in your document, and then inputting the expected result on the bottom line.
Data validation is very useful if you plan on sharing your spreadsheet with others. Using this feature, you control the type of data and the value of data entered into selected cells. For example, if you only want numbers 1-10 entered into a certain group of cells, you can use data validation only allow said numbers in these cells, and lock out all other numbers.
To setup data validation, click on the Data Validation button from the right-hand side of tab. From the pop-up menu, you can set the cell range and the data criteria. To set your alerts and warning text, click on the Alerts and Help text button from the bottom of the menu.
Help text will appear when a user clicks into a cell with data validation, while an error alert will tell the user after inputting invalid text. You can set these alerts by way of their respective menus.
Linking External Data To Your Spreadsheet
One nice thing about Zoho Sheet is its ability to link external spreadsheets to your new Zoho sheet. To do this, look to the far right-hand corner of the data tab and click on the Link External Data button. From the pop-up menu, you'll be prompted to select your data type and privide the external data's URL. Click the next button when this is complete.
You can now input which sheet you'd like this data to appear on as well as the starting cell. After this, click the next button and schedule how often you'd like you spreadsheet to pull this data.
The View Tab
The View tab allows you to the first row or column or a selected pane on your spreadsheet. When a row or column is frozen, it will follow you as you scroll. This is helpful for viewing labels when editing a document. Additionally, you can enter full screen mode from the view tab.
The Review Tab
From the Review tab, you can add comments to a cell by clicking into a cell and then clicking on the add a comment button from the center of the tab.
Additionally, you can create versions of your spreadsheet by clicking on the Create Version button. Like in Zoho Writer, creating a version of a spreadsheet will save a copy of your document's current state. You can then load this state, or version, from the View Version History button.
If you look to the far right side of the Review tab, you'll see a a button labeled Audit Trail. Clicking on this button will show all changes made to the document. If you look towards the top of this window, you'll be able to filter these changes by user, date, sheet, or range.
Using Macros In Zoho Sheet
In short, a macro is a way to program or record keystrokes or steps for tedious tasks in excel. You can make a macro in one of two ways: by recording them, or by programming them.
You make Macros in the Macro tab. Once in this tab, look towards the left-hand side of the window where you'll find two buttons: Create Macro and Record Macro. Clicking on Create Macro will open a window where you'll be able to give your macro a name and description. Once you're done with this, click the Next button.
You'll now be brought to a window where you can program your macro by hand using Visual Basic. I won't be going in-depth on how to program a macro in Zoho Sheet, but if you're familar with Excel macros, you'll be right as home with macros in Zoho Sheet. If you're not, you'll want to read this documentation.
You can also click on the Record Macro button to create a new macro by recording your keystrokes.
To run a macro, look towards the center of the tab and click on the Run Macro button. You can view your macro library by clicking on the view macros button.
Collaborating In Zoho Sheet
Like with documents in Zoho Writer, you can add collaborators to your spreadsheets in Zoho Sheet. To do this, click into the Share tab. Look to the far left-hand corner of this tab and click on the Share tab. From the pop-up window, you can add collaborators to your spreadsheet by way of their email address.
Enjoy Using Zoho Sheet For Spreatsheets
And that's it: you're now ready to start using Zoho Sheet to make spreadsheets in your business and home office. In the comments, let us know how you think Zoho Sheet compares to Excel and Google Docs Spreadsheets—and also let us know if you have any trouble getting used to the spreadsheet workflow in the Zoho apps.