104.2 - gSheets - Executive Tutorial - Menus in Detail
- "File" Menu
- "Edit" Menu
- "View" Menu
- "Insert" Menu
- "Format" Menu
- "Data" Menu
- "Tools" Menu
- "Add-ons" Menu
- "Help" Menu
1) "File" Menu
The File menu has:
SUMMARY: The only items you may use semi-regularly on the file menu might be the "Download as" and the "Email as Attachment". Other functionality on this menu can be accessed else where and some is just not used often.
- Share || New, Open Rename, Make a Copy, Move to, Move to Trash, Import || See Revision History, Spreadsheet Settings || Download As, Publish to the Web, Email Collaborators, Email as Attachment || Document Details, Print
SUMMARY: The only items you may use semi-regularly on the file menu might be the "Download as" and the "Email as Attachment". Other functionality on this menu can be accessed else where and some is just not used often.
- Share -- I do not typically share from within the file. I go to gDrive, right click on the file name, and select share from that menu and and configure the sharing there. It achieves the exact same result. If you get in the habbit of doing that via the drive window along with a few other functions, I feel you will gDrive computing a little simpler in general and you will be opening and closing less files.
- New, Open, Rename, Make a Copy, Move to, Move to Trash -- I do ALL of these via the gDrive interface, outside of the file (and I'll rename in the file differently--just type where the name of the file is and it will rename it).
-- I do NOT like using New from within a file because it will not prompt you what directory to do it in and if you are not where you think you are, the file will be created elsewhere.
-- I do NOT like using Open from within a file as it's easier to navigate the file tree in gDrive.
-- I do NOT like using copy from within a file, because occasionally gDrive hiccups on the copy adn it lands in your root drive (a lot of confusion if you aren't in the folder to verify the copy).
-- I do NOT like using Move to, because its easier to drag and drop in gDrive
-- I do NOT use Move to Trash here, as I just am not use to it. I delete files from gDrive.
Googlites love the idea of going without sound folder structure. That is great until you start handling a lot of files in which case some structure is needed - See Revision History ---It enables you to view and undo incremental changes. Very powerful tool to recover accidental data deletion that isn't realized immediately. I have never used it, but I can see it's use. If you have multiple people with varying skill levels using a shared sheet, this could help recover from a mess up.
- SpreadSheet Settings -- Local, Time Zone, Recalculation for NOW, Today, and other gSheet functions. I've never tweaked my settings.
- Download As -- This is the most relevant item on this menu. If you want to download a sheet as a PDF, this is the place to do it. Likewise, you can actually select print, and change your printer to "Save as PDF" and achieve the same result.
- Publish to Web -- This is not something most will be using. You will more often be "sharing". It has it's uses, but advance topic.
- Email Collaborators -- This is likely to let them know of changes to the worksheet or worksheet related to issues. This is where the "comments" entered at a cell level would become relevant. I have not used this. Most who are practicing business solo, will not use this. This could make for a tutoring tool (will think more on that) and definitely ismore relevant for those who collaborate on grants and such.
- Email as Attachment -- One of the greatest features added to MS Excel and Word when using MS Office suite was that ability to create and send a document from the office productivity application without having to save the file, open email, find the file and attach it and send. That has always been a thorny thing for Gmail users in general. This is Googles version of that and it certainly simplifies that static sharing process when "file sharing" is not appropriate.
2) "Edit" Menu
The Edit menu has:
SUMMARY: The only item I may use on this menu is "Find and Replace". If you are new to shortcut keys, this is a great place to remember which ctrl-{key} does what.
- Undo, Redo || Cut , Copy, Paste, Paste Special || Find and Replace || Delete Values, Delete Row X, Delete Column Y || Clear Notes
SUMMARY: The only item I may use on this menu is "Find and Replace". If you are new to shortcut keys, this is a great place to remember which ctrl-{key} does what.
- Undo / Redo -- Use Ctrl C and Ctrl V or use the icons on the menu bar
- Cut / Copy / Paste / Paste Special - Use Ctrl commands or the right click menus
- Find and Replace -- This is very powerful.. too powerful. Read the options carefully. In general, you likely want to find and replace within the worksheet or workbook you are working on... but there is an option to find in all gSheet files. That could be a catastrophic blunder depending on the find/replace items.
- Delete Values / Delete Row / Delete Column -- Delete Values with the delete key. Delete rows and columns by right clicking on them and selecting delete from the right click menus.
- Clear Notes -- Fast way to clear all notes. You can likely highlight entire spread sheet and do the same thing with right click menu.
3) "View" Menu
The View menu has:
SUMMARY: Freeze is something you will likely find very useful and powerful. You should be familiar with the rest.
- Freeze || Grid-lines, Protected Ranges || Formula Bar, All Formulas || Hidden Sheets || Compact Controls, Full Screen
SUMMARY: Freeze is something you will likely find very useful and powerful. You should be familiar with the rest.
- Freeze -- Freeze locks a designate row(s) or column(s) or both in your viewing window... so when you use the vertical and horizontal scroll bars, those cells do NOT move. The most common use for this is in database applications where you have a data table called "Patients" for example. Across the top you have Last Name, First Name, Address, etc. As you scroll down the list you want to always be able to see the field (column) names, thus you freeze the first row. If your data goes off the screen to the right, as you scroll to the right, you don't want the patient name to disappear, so you freeze the first column. Play with this. You my use it daily if you use gSheets as a database.
- Gridlines -- This just turns off grid lines in the window (so you can no longer see them on your computer screen). I seldom/never turn these off. I typically work with them on, and if I don't want them to print, I simple suppress them in the print options box. Some people turn these off when they create a pseudo form from a worksheet. It is a good way to get a print preview of what your sheet would look like without lines, so maybe it's good for that too.
- Protected Ranges -- If you have setup protected ranges with the rigth click menu, this will list those ranges and allow you to unprotect them. (you can not otherwise identify them easily other than the fact they won't allow cell inputs). Not used often. You will see a Protected Sheets and Ranges menu item associated with the "Data" Menu. It leads to the same dialogue box. They should eliminate one of these to avoid confusion, but oh well.
- Formula Bar -- This is a toggle that shows/hides the formula bar (which is just above the letter A and below the image based menu bar.) I generally suggest working with it in view at all times. It shows what is in a selected cell. I often edit cells with a lot of info in the formula bar instead of in the cell.
- All Formulas -- This is a toggle that shows the formulas in each cell versus resulting data (mostly in math related applications, not used in database applications). This would mostly be for troubleshooting formula issues and general orientation to a sheet someone else did or one you did long ago. It's also a great instruction aid to show new folks formulas in cells for different applications.
- Hidden Sheets -- Sheets can be hidden just like cells and columns. Once hidden, you would come here to unhide them. The problem with hiding sheets is that I'd generally forget they were there, so I don't use that often, but it could come in handy with some data validation work where you don't want average users to be able to manipulate the data option lists.
- Compact Controls -- This is a toggle that hides the top line of data/info in the gSheets screens. I don't typically use this, but I may doing tutorials because it does hide the user name associated with a file. Occasionally I'll have a strange screen size viewing issue where I just need a little more of a view, and I might use this, but more often than not, i'd just change the viewing resolution in the browser instead.
- Full Screen -- This is a toggle that hides everything above the column designators. I don't use this. It might come in handy if embedding a gSheet into a web page and you only wanted the table data to show. The ESCAPE key undoes this view.
4) "Insert" Menu
The Insert menu has:
- Row Above, Row Below || Column Left, Column Right || New Sheet || Comment, Note, Function || Chart, Image, Link, Form, Drawing
- Row Above, Row Below || Column Left, Column Right -- Use your right click menus for these instead.
- New Sheet -- Use the plus sign in the bottom left corner of the screen for this
- Comment, Note -- Use your right click menu for these
- Function -- This lists the most used algebra functions. Click on "more" at the bottom to view a full list of functions. Each cell can act like a scientific calculator. This was the original power of a spread sheet in engineering and science applications.
- Chart -- Create a chart from data on a page (I do not use often. Most who use this use it to create charts for presentations or publications)
- Image -- Insert an image (I do not use often). It could be used if you had a logo you wanted to associate with a file. When I create letter head with gSheets stuff, I encourage people to just use text for simplicity reasons.
- Link -- Use your right click menus
- Form -- BUG ALERT DONT CLICK this right away --- This is complex and potentially very powerful, but also confusing. In database applications, a "Form" is used to submit data to create a database "record". A form is used because it is "prettier" for gathering data and more importantly, it separates data entry from data storage. In gSheets, As soon as you click on form, it automatically creates a form based on the first row of the sheet you are on. This form is a separate file created in the same directory. It also creates a tab in your worksheet to capture the data. It should NOT do all of this as soon as you click form... it should come up with an intro screen asking you if you want to create a form and telling you a little about what it is giong to base the form off of and what it is going to do (create a file and add a tab to the existing workbook). If you click on this by accident, just delete the extra file from teh directory and then the worksheet. If you try to delete the worksheet first, it will ask you to unlink the worksheet from the form. To do that, on the worksheet you are trying to delete, there is a new menu item that has been added called "form". On that menu, there is an "unlink" option. If you run into issues later saying your spread sheet is linked to a form and you can't get the messages to go away, you may need to create new spread sheet and copy/paste each tab to the new tab, and then delete existing worksheet. This functionality is buggy and complex. They should in fact have a "form" menu on each spreadsheet, but they likely don't as very few people are actually using this functionality (and they love hiding things people don't use a lot).
- Drawing -- This allows you to create a drawing on a worksheet. I have seldom used this.
5) "Format" Menu
The Format menu has:
SUMMARY: Lots of relevant items on this menu. Some are duplicates of right click functionality. Familiarize yourself with all.
- Number || Font Size || Bold, Italic, Underline, Strikethrough || Align, Merge Cells, Text Wrapping || Conditional Formatting, Alternating Colors || Clear Formatting
SUMMARY: Lots of relevant items on this menu. Some are duplicates of right click functionality. Familiarize yourself with all.
- Number -- Formatting numbers is HUGE deal. This is your doorway to all of that. Some basic functions are available via the icons on the menu bar, but if those don't meet your needs this is the place to come.
- Font Size -- This is available via the menu bar icon. Nothing extra here.
- Bold, Italic, Underline, Strikethrough -- All but underline is available via menu bar icons and the underline omission is NOT a mistake. 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
- Align -- Justification (Left, Center, Right and Top, Middle Bottom) These are available via 2 different icons on the menu bar. Nothing extra here.
- 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.
- Text Wrapping -- This is HUGE. Familiarize yourself with these options: Overflow, wrap, clip Overflow is the one I was waiting for that came out in 2013. It allows text in one cell to appear to flow into the next if the next cell is empty. This was/is required to use a spread sheet like an outline tool. Wrap will wrap long text in a cell, and it will adjust the cell height to show all the text. This is very handy when using a spread sheet as a log of shorts. Clip just visually cuts long text off at the border with the next cell. The text in the cell is not truncated. To view it all, click on the cell and you will see it in the formula bar or double click on the cell and it will pop up in a box. I'm a little surprised this is not on the right click menu but with the default as "overflow", it makes sense it is only here in case you want to select the other options.
- Conditional Formatting -- Available on the right click menu.
- Alternating Colors -- Very powerful tool to add a GUI (Graphical user interface) touch to database records. It "zebra stripes" the data with alternating colors. There is a problem when some data from a record is cut and pasted into another sheet, as it carries along the row colors, and past special does not strip those colors from the pasted cells.
- Clear Formatting -- This will strip off all font decoration and cell decoration. The symbol for this is a Tx and I'm seeing that in more and more web based applications. It is becoming a somewhat universal symbol. This is a very handy tool when you paste something and it carries in formatting you did not want. In theory you use past special to strip that stuff on paste, but it does not always work and in other applications it is not always an option.
6) "Data" menu
The Data menu has:
Summary: These are all very powerful tools. You will use the sort A-Z and Z-A and Filtering the most initially, but you should familiarize yourself with all to know the others are there down the road.
- Sort Sheet By Column A A-Z, Sort Sheet By Column A Z-A, || Sort Range, Named Ranges, Protected Sheets and Ranges || Split Text to Columns || Filter, Filter Views, Pivot Table, Validation
Summary: These are all very powerful tools. You will use the sort A-Z and Z-A and Filtering the most initially, but you should familiarize yourself with all to know the others are there down the road.
- Sort Sheet By -- This is enables you to add new records at the bottom of a list (like and address list of patient list) without having to properly insert a row. It also enables you to resort alphabetically (or in reverse) after a custom query is run on the data.
- Sort Range -- This is a sort just on a selected set of cells. This is available via the right click menu. Not used often as I'm not often sorting small lists that aren't related to the entire sheet they are on.
- Named Ranges -- Generally speaking, when doing more complex equations referencing groups of cells the default naming systems is {tabName}!{cell(s)}. That can make things complicated. Adding a name to a set of cells can often simplify the cognitive analysis process. This was mentioned on the right click menus too. You can define ranges via right click and here. After a range has been named, this is the place to find out info about it and to modify/remove a named range.
- Protected Sheets and Ranges -- We've seen protected ranges on the right click menu, but what if you want to protect and entire sheet? You can do that here. You can also set, view and release protected ranges here too. There was a "protected Sheets" menu item on the "View" menu. This opens the same dialogue box. Technically, they should eliminate one of these menu options as having them in both places is a little confusing.
- Split Text to Columns -- Sometimes you can easily get comma separated values from data stores, and you might want to import that into your own spread sheet for analysis. You can past that info into cells and then use this function to extract the values into unique cells for data analysis and manipulation. This isn't used much anymore as most programs will allow you to export in CSV formats (comma separated value formats) and then gSheets and Excel will recognize those and open with the data in unique columns (so it as automated this for the most part), but it's nice to know the function exists if needed.
- Filters, Filter Views -- This is very, very powerful stuff. Applying filters and saving them is an advanced topic. You need to be very, very familiar with this to use gSheets as a business database system. Seek out online tutorials on this to get the full gist of this. I will likely create some as well and or you will learn how to use these as you start to use specific applications associated with this publishing.
- Pivot Tables -- These are some of the most confusing reporting systems around. The folks that have taken the time to learn how to use them love them. I tend to prefer to run my reports manually, but admittedly I don't run reports to create printed copies of them and this could come in handy for that. This is an advanced topic. Don't mess with this until you have a good handle on Filters and Filter Views first so you know what you can't get from those that these can provide. Do internet search for these for some tutorial info if desired.
- Validation -- This is available via the right click menus too. It's referenced as "Data Validation" on that menu. They have only referenced it as "Validation" here becuase we are on the "Data" menu... and in my opinion that is a little confusing, but oh well. The pop up window specifies "Data Validation". Incredibly powerful stuff. You will become familiar with this as you begin to use advanced spreadsheets. After seeing it in action, it will be easier to figure out when and how to apply it/use it as a spread sheet designer. Do internet search for these for some tutorial info if desired.
7) "Tools" Menu
The Tools menu has:
Summary: There is nothing I use here regularly. Several of these items can be performed from other menus.
- Create a Form || Script Editor || Spelling || Enable Autocomplete, Notification Rules, Protect Sheet || Personal Dictionary
Summary: There is nothing I use here regularly. Several of these items can be performed from other menus.
- Create Form -- We first saw this on the bottom of the Insert menu. It's the exact same command. It really should be on only one of these menus. This also creates the form and new tab with no warning popup.
- Script Editor -- This is a special tool that allows a user to provide custom programming to be executed on the worksheet. The programming language is Javascript. This is comparable to being able to build "macros" in older software. In general, I'd try to stay away from this (and I'm a programmer). If you need to go this far, you probably really need to be working in another system (like a relational database).
- Spelling -- This seems to be a custom spell check tool. my browser is chrome and it does spell check, so this doesn't seem to be of use or seems duplicative given the browser spell check.
- Enable AutoComplete -- This comes in handy when using this as a non-relational database and re-entering cell data that has been entered prior. Uncheck to toggle off.
- Notification Rules -- This is a clever tool that allows me to monitor form submissions and spread sheet modifications via email immediately or with a one time per day notice.
- Protect Sheet -- This protects the sheet from editing. IT is the same functionality we saw on the tab- right click menu.
- Personal Dictionary -- This is the repository for the spell check system built into gSheets.
8) Add-ons Menu
The Add-ons menu has:
Summary: Add ons are like macros written by others that you can load into your version of gSheets. They are kind of like browser extensions for a browser.
Copy Folder Add on -- The copy folder add-on is both cool and bizarre. You can not copy a folder in Google Drive (for various reasons). Someone wrote this to generically copy folders. It is not related to GSheets but this is where it could be mounted/installed to operate.
AutoCrat Add on -- I added this when trying to perform a "mail merge" somewhat like what was done between MS Word and MS Excel. It seemed like it could work.
- {add ons that I have added} || Get Add-ons, Manage Add-ons
Summary: Add ons are like macros written by others that you can load into your version of gSheets. They are kind of like browser extensions for a browser.
Copy Folder Add on -- The copy folder add-on is both cool and bizarre. You can not copy a folder in Google Drive (for various reasons). Someone wrote this to generically copy folders. It is not related to GSheets but this is where it could be mounted/installed to operate.
AutoCrat Add on -- I added this when trying to perform a "mail merge" somewhat like what was done between MS Word and MS Excel. It seemed like it could work.
9) Help Menu
The Help menu has:
Summary: Generally good stuff...
- Sheets Help || Report a Problem, Report Abuse/Copyright || Function List, Keyboard Shortcuts
Summary: Generally good stuff...
- Sheets Help -- I've not used this. I tend to just google stuff I need help with...
- Report a Problem -- I don't do this often. I can typically find forums in which others are complaining of the same thing. I only participate in the feedback process if it really, really affects me. One time I saw system wide action on one of my complaints within 24 hours. I fell out of my chair.
- Function List -- This is just a short cut to the same function list as was found from drilling into Insert > Functions > More
- Keyboard Shortcuts -- This is a nice resource although don't think you will need to remember/use/need most of these. Ironically they are missing ctrl-arrow down to get to last line in a sheet and ctrl-arrow up to get to first line. Those work very well as long as no empty rows, otherwise they will stop at each empty row. Still a good way to work up and down a spread sheet.
Test Video
The video below is just a test to check quality and sound...
File Menu
If you click on the file menu you should see the following...
More coming soon...
If you click on the file menu you should see the following...
More coming soon...