104.1 - gSheets - Executive Tutorial - Interface and Keystrokes
This page introduces the gSheets interface and all relevant menus you might use. This page is for folks who are new to spreadsheets as well as those familiar. Those familiar with Excel should feel at home with a few new options and functions. Those not familiar should be able to get a feel for the nature of working in spreadsheets without even knowing what they might do with them (yet).
This is not a click by click tutorial. Here is more of a click by click tutorial and there are many more online... http://www.gcflearnfree.org/googlespreadsheets/getting-started-with-google-sheets/1/
This page has the following sections:
The images for this were snipped on 10/30/2016. There may be some deviations from these as time progresses. if they get way off, I'll redo them, but the primary goal here is to convey primary functionality of all the menu items to newbies and seasoned spreadsheet users in a simple fashion, and these basic commands and labels will not change as they are consistent with the mature industry.
This is not a click by click tutorial. Here is more of a click by click tutorial and there are many more online... http://www.gcflearnfree.org/googlespreadsheets/getting-started-with-google-sheets/1/
This page has the following sections:
- Interface intro
- Keystrokes
- Right Click Menu -- on a Cell(s)
- Right Click Menu -- on a Row(s)
- Right Click Menu -- on a Column(s)
- Right Click Menu -- on a Tab
- Menu Bar Icons
The images for this were snipped on 10/30/2016. There may be some deviations from these as time progresses. if they get way off, I'll redo them, but the primary goal here is to convey primary functionality of all the menu items to newbies and seasoned spreadsheet users in a simple fashion, and these basic commands and labels will not change as they are consistent with the mature industry.
1) Interface Intro
This is the basic gSheet Interface. For those who were familiar with MS Excel prior to the introduction of the ribbon in Excel 2007, this should look and feel very familiar. The visual simplicity feels good to me.
2) Keystrokes
You are going to need to know how to do the following and/or how to use the following keystrokes:
In general,
https://drive.google.com/file/d/0Bxq-zPwhecJUbUVoQnJHWDdTYlE/view?usp=sharing
- You need to know how to enter text, numbers and dates into cells -- Click on a cell and type. Double click on a cell and type. You will use other keystrokes below to bullk fill cells based on selected content and you will use the Data Validation ultility to configure alternative data entry methods for individual cells (like date entry with a calendar, for example).
- TAB key -- The enables you to move from cell to cell. it also terminates entry in one cell and moves to the next cell to the right. It's kind of like the enter key. You need to learn to "TAB OUT" of a cell after you finish typing in it to submit your data. You will likely not do this dozens or hundreds of times before you get the hang of it.
- ENTER key -- This enables you to submit data while entering a cell. It seems that gSheets trys to guess where you might want to go next and it typically moves the active cursor down, or down adn to teh right, much like a carriage return on a typewriter. If you just want to submit and move to the cell to the right, use the TAB key.
- ctrl - c -- The universal shortcut keystroke for COPY. You need to know how to use it as it pertains to text inside of cells, a single cell, multiple cells, a column, multiple columns, a row, multiple rows, an entire tab/worksheet
- ctrl - v -- The universal shortcut keystroke for PASTE. You need to know how to use it as it pertains to text inside of cells, a single cell, multiple cells, a column, multiple columns, a row, multiple rows, an entire tab/worksheet
- ctrl - x -- The universal shortcut keystroke for CUT, which combines with ctrl-v (PASTE) to completes a "MOVE". You need to know how to use it as it pertains to text inside of cells, a single cell, multiple cells, a column, multiple columns, a row, multiple rows, an entire tab/worksheet
- ctrl - z -- The universal shortcut keystroke for UNDO as it pertains to stuff in cells, a single cell, multiple cells, a column, multiple columns, a row, multiple rows, an entire tab/worksheet
- ctrl - y -- The universal shortcut keystroke for REDO and UNDO as it pertains to stuff in cells, a single cell, multiple cells, a column, multiple columns, a row, multiple rows, an entire tab/worksheet (some programs use ctrl-shift-z instead of ctrl-y for this).
- escape key -- This is VERY powerful. If you start to edit a cell and then decide you want to cancel the edit before submitting it (by tabbing out of the cell or hitting enter) select the "escape" key ("esc" located in the upper left of most keyboards). This is like the undo, except you use it before the data change is complete.
- copying cell data via drag -- as it pertains to a single cell, multiple cells, a column, multiple columns, a row, multiple rows
- moving cell data via drag -- as it pertains to a single cell, multiple cells, a column, multiple columns, a row, multiple rows
- using drag to populate cells based on other cells already on the sheet -- with the same data as the target cell, with incremental data related to target cell(s)
- resizing column width -- manually, autosize and resizing more than one at a time
- resizing row height -- manually and via numeric input (note: this is NOT used much and should generally be avoided)
In general,
https://drive.google.com/file/d/0Bxq-zPwhecJUbUVoQnJHWDdTYlE/view?usp=sharing
3) Right Click Menu -- on a cell
"Right Click" on any cell and you should see the following menu options...
SUMMARY: All of these items can be performed via these menu options and via other methods. Familiarize yourself with all of these, but most you will not use via this menu.
The same functionality is available for multiple rows at one time.
- Cut , Copy, Paste, Paste Special || Insert Link || Defined Name Range, Protect Range || Insert Comment, Insert Note, Clear Notes || Conditional Formatting, Data Validation
SUMMARY: All of these items can be performed via these menu options and via other methods. Familiarize yourself with all of these, but most you will not use via this menu.
- Cut, Copy Paste -- These are the same as your shortcut keys. Ctrl- X, C, V I generally use my shortcut keys but either works
- Paste Special -- This is extremely valueable and powerful tool you will need as you become more familiar with the idea that when you paste, you are pasting cell formatting and equations too, adn this can help strip that out on the past if needed.
- Insert Link -- This converts text into a clickable link, much like turning text into a link on a webpage.
- Define Named Range -- This enables you to reference a group of cells by a name as opposed to a cell ID or a sheet!cell ID. This is an advanced feature that becomes more relevant as you start to build more complex spreadsheets. No need to familiarize as a beginner.
- Protect Range -- This enables you to protect a group of cells from modification. No need to familiarize yourself with this as a beginner.
- Insert Comment -- This enables you to associate a note with a cell in a manner like an editor. This is used for collaboration on websites, to make comments about something in a cell or in that area of a spread sheet. No need to familiarize yourself with this as a beginner. (I've never used this...)
- Insert Note -- This enables you to associate a static note with a cell(s). These are not used very often, but occasionally they could come in handy.
- Clear Notes -- This clears/deletes the note that was attached to a cell(s).
- Conditional Formatting -- this allows you to color a cell in a certain way given a condition. (negative values might be red for example. That might be good for an accounting application). This can be a very valuable tool in advance applications.
- Data Validation -- This is an incredibly powerful tool. This enables cells to behave more like drop down boxes in websites. It gives spreadsheets the feel of being a database type of system with select options. It also enables you to set a cell or column as a "date" field with the small calendar that pops up when you double click. We'll come back to this later, but it is incredibly powerful.
The same functionality is available for multiple rows at one time.
- Click on a cell, and before lifting up, drag your mouse into the cell on the right (or above or below or to the left).
- Let up on the mouse button
- Hover over the selected cells and right click. You will see generally the same menu options
4) Right Click Menu -- on a Row(s)
"Right Click" on the number 9 on the left edge of the spreadsheet, for example.
SUMMARY: This is the primary method for inserting and deleting rows. All other functions are used less or performed via different methods.
The same functionality is available for multiple rows at one time.
- Cut , Copy, Paste, Paste Special || Insert 1 above, Insert 1 below, Delete Row, Clear Row, Hide Row, Resize Row || Define Named Range, Protect Range || Conditional Formatting, Data Validation
SUMMARY: This is the primary method for inserting and deleting rows. All other functions are used less or performed via different methods.
- Cut, Copy Paste, Paste Special -- Row level cut, copy, paste.
- Insert 1 above, Insert 1 below -- This inserts a row above or below the current row.
- Delete Row -- This deletes the existing row.
- Clear Row -- Clears the values out of all the cells in the existing row but does not remove the row from the worksheet. (not used often). Instead of doing this, highlight a row and hit "delete key". It will delete contents but not the row, just like this clear row command.
- Hide Row -- This hides the row and leaves a small arrow on the left column to show that something is hidden. This is NOT typically applied to a single row. This is typically applied to groups of rows, and sometimes very large groups of rows. This becomes useful if you use gSheets for things like a running sales ledger. In such application, it enables you to hide all transactions prior to the current day.
- Resize Row -- This enables you to change the height of the row. ( I do NOT use this often in daily spreadsheet use and I do NOT suggest using it unless you know exactly what you are doing and why).
- Define Named Range, Protect Range -- Same as above. Not used in basic applications.
- Conditional Formatting, Data Validation -- Same as above.
The same functionality is available for multiple rows at one time.
- Click on number 5 on the left edge of the spread sheet. The row should be highlighted.
- Push down the "shift" key (and don't let up).
- Move the mouse over the 10 on the left edge of the spread sheet.
- Click on the number 10.
- Rows 5 through 10 should be selected.
- Let up on the shift key
- While anywhere over top of the highlighted rows, right click, and a similar menu will appear with batch options
5) Right Click Menu - on a Column(s)
"Right Click" on the column header cell with letter "B", for example.
SUMMARY: This is the primary method for inserting and deleting columns. All other functions are used less or preformed via different methods.
Click on column "D" and hold down shift and click on column "F". Let shift up, and then right click on the selected columns. The menu options are nearly identical, and the "sort" A-Z option" is still there.
- Cut , Copy, Paste, Paste Special || Insert 1 left, Insert 1 right, Delete Column, Clear Column, Hide Row, Resize Column || Sort Sheet A-Z, Sort Sheet Z-A || Define Named Range, Protect Range || Conditional Formatting, Data Validation
SUMMARY: This is the primary method for inserting and deleting columns. All other functions are used less or preformed via different methods.
- Cut, Copy Paste, Paste Special -- Column level cut, copy, paste.
- Insert 1 left, Insert 1 right -- This inserts a column to the left or right of the current row.
- Delete Column -- This deletes the existing column.
- Clear Column -- Clears the values out of all the cells in the existing column but does not remove the column from the worksheet. (not used often). Instead of doing this, highlight a column and hit "delete key". It will delete contents but not the column, just like this clear column command.
- Hide Column -- This hides the column and leaves a small arrow between the column headers to show that something is hidden. This is NOT typically applied to a single column. This is typically applied to groups of columns. This becomes useful if you use gSheets for things like a database but you only want to view certain fields at any given time.
- Resize Column -- This enables you to change the width of the column. Instead of using this, you will just drag the edge of the columns to resize or double click them to auto-size.
- Sort A-Z, Z-A -- This sort option assumes you are wanting to sort this entire tab (worksheet) as a database table with this column as the sort field. If you perform a sort and it messes things up, remember, Ctrl Z.. and do it right away.
- Define Named Range, Protect Range -- Same as row(s) above. Not used in basic applications.
- Conditional Formatting, Data Validation -- Same as row(s) above.
Click on column "D" and hold down shift and click on column "F". Let shift up, and then right click on the selected columns. The menu options are nearly identical, and the "sort" A-Z option" is still there.
- I can only assume this Sort A-Z option might sort by Column D first, then Column E, then Column F. Play with it... I don't get into that level of sorting often and when I do, I just play with things and use ctrl-Z until I figure out what I need to do to get the desired result.
6) Right Click Menu - on a Tab
Right clicking on a Tab is the same as clicking the small down arrow on the tab. The options include:
SUMMARY: Most of this functionality can NOT be found elsewhere.
- Delete, Duplicate, Copy to, Rename, Change Color, Protect Sheet, Hide Sheet, View Comments || Move Right, Move Left
SUMMARY: Most of this functionality can NOT be found elsewhere.
- Delete -- Delete the current sheet. Ctrl Z will undo a deleted sheet. You will use this occasionally.
- Duplicate -- Duplicates the existing sheet right next to the current sheet. It will say "Copy of {sheetname}". You will use this occasionally
- Copy to... -- Copies the existing sheet to another workbook. I don't use this that often. I typically open the other sheet and highlight the entire contents on a tab and us ctrl c and ctrl v to cut and paste into new worksheet. It's an old school habbit I have. Not something that is used that often. Rather unusual to copy entire sheets unless using some type of template based reporting system and even then, there are other options.
- Rename -- You can select rename to rename the tab, but you can also just double click on the tab name to rename, so no need to come into this menu.
- Change color -- Adds a colored line under the tab name for color coding. Also a good way to group like tabs from other tabs in a single work book.
- Protect Sheet - That prevents anyone from making changes to a sheet. The sheet must then be unprotected to edit. More to protect a sheet contents from errant keystrokes than security.
- Hide Sheet -- This removes the tab from the tab bar. In general I don't advise this as I personally will forget sheets are hidden. I don't use this often. Might be used more as this is deployed in larger organizations.
- View Comments -- When there are comments on a sheet there is a symbol in the tab and the upper right corner of each cell with a comment is yellowish-orange. This will show all the comments on the sheet at one time.
- Move Right / Move Left -- More typically you would just grab the tab and slide it right or left.
7) Menu Bar Icons
Moving from left to right... I've separated them by the vertical dividers in the menu bar...
- Print, Undo, Redo, Format Painter
- Format as Currency, Format as %, Decrease decimal points, Increase decimal Points, Drop Down for Numeric Data types
- Font Name
- Font Size
- Bold, Italic, Strikethrough, Font Color
- Cell Fill Color, Cell Border Management, Merge Cells
- Inner Cell Justification (left, center, right), Inner Cell Justification (bottom, middle, top), Text Overflow/Wrap/Clip
- Insert Link, Insert Comment, Insert Chart, Toggle Filters (access filter views), Insert Sum (and other functions)
- Reverse spread sheet to read from right to left, make text from left to rigth, make cell text right to left
SUMMARY: Most of these are intuitive. Many of these are duplicates of what will be found in the menus, as outlined below and a few are duplicate of options found on the right click menus. Other functionality on this menu can be accessed else where and some is just not used often.
- Print -- This icon is how I initiate a print. There is no print preview. Select print, and then you will see a preview. You will need to familiarize yourself with the print options related to grid-lines, page orientation and fit width.
- Undo/Redo - I use ctrl-z and ctrl-y.
- Format Painter -- this is HUGE and this icon is the only way to execute this. Highlight a cell, select the format painter button, and then highlight other cell(s). IT will copy all aspects of the "formatting" of the cell and the data in the cell onto the new cell. This functionality also exists in most word processors.
- Format as Currency, Format as %, Decrease decimal points, Increase decimal Points, Drop Down for Numeric Data types - These are regularly. I also just go straight to Format > Number menu a lot instead of using the drop down here.
- Font Name, Font Size, Bold, Italic, Strikethrough, Font Color, Cell Fill Color -- Straight forward. Underline Text option is intentionally not included here. In general, text underlining is avoided in spreadsheets. You will use the border tool to underline and entire cell instead or you will use other text decoration or cell decoration to denote delineation.
- Cell Border Management - This is a BIG DEAL. This is generally used a lot in all but database applications. Familiariaze yourself with what you can do with the borders of a cell.
- Merge Cells -- Merging a group of cells representative of a page width and then centering the text in the resulting cell is the most common use I've had for this. It would apply when trying to print out a spread sheet so it looked more like a word processing document or form. In general avoid merging cells other than for this use or another use in which you know exactly what you are doing and why.