Posts

Showing posts from 2017

Google Sheets - Session #1 Summary for Support Staff

Image
(Part of my administrative assistant series on Google Sheets.)

Below is a summary of all the topics covered in Session #1 of the series I have been doing for support staff.  This one covered a lot of the "basics" of using Google Sheets.  If you are able to perform all these tasks, you should be ready to dive in deeper!  As several of the attendees noted, if you have used MS Excel you are able to apply much of what you know to Google Sheets.

The next session dives into using formulas.
Session 1 - Introduction to Google Sheets (November 8, 2017)Basics of Data Entry Using Fill Formatting Numbers Border Options Print Options Freezing Rows and/or Columns Text Rotation Text Wrapping Alternating Row Colors Conditional Formatting
Adding/Inserting Rows/Columns

Google Sheets - Conditional Formatting

Image
(Part of my administrative assistant series on Google Sheets.)

Conditional formatting is a great way to dynamically view your data, even while entering it.  Many people spend a lot of time highlighting data individually, not realizing that this feature is built in and can save a lot of time.

Conditional formatting can be turned on by going to the Format menu and selecting it from about 3/4th of the way down -

First you highlight the data you want to format.  You can click on an entire row or column, or even multiple rows & columns if you want to apply the same rule over a large amount of data.
Second you click on Add new rule -

There are several options that come up -

The data range to which it should be applied (you can change it here manually if you did not highlight the correct data)The condition for formatting the cells - the list of your options is the next graphic belowHow you want those cells formattedYou can even add multiple rules for the same data set on the flyIf you wa…

Google Sheets - Text Wrapping

Image
(Part of my administrative assistant series on Google Sheets.)

One item that frustrates several people I work with is how text appears when it does not fit into its cell.  Knowing how to change text wrapping is key for getting the information to appear as you want it.

The text wrapping icon is about 3/4 across the formatting toolbar -
When you click on it, there are three options -
The options are:

Overflow: lets the text flow into the next cells ONLY if those cells are emptyWrap: wraps the text within the cell so all is visible, automatically adjusts the row height as necessary for it to fitClip: clips the visible text to the size of the cell, the rest is "hidden"

Google Sheets - Print Options

Image
(Part of my administrative assistant series on Google Sheets.)

Not long ago, there were very few print options in Sheets.  That has changed - there are many options now.

Location of print options:
I had someone recently tell me that there still weren't print options.  They are there!  It just might not be intuitive that the options are found when you select to Print :-)


Selecting Print, actually brings up a print preview, with print options in a menu on the right side -

Standard Options:
There are many options available in the menu:

what is printed (current sheet, the entire workbook, or just the selected cells)the paper size you are printing topage orientation (landscape or portrait)Scale (normal, fit to width, fit to length, fit to page, and custom)Margins (normal, narrow, wide, and custom)

I personally am even more excited about the options that are found under the Headers & footers area:

what information you want to appear in the header/footereven the ability to edit custom f…

Google Sheets - Formatting Number Entries

Image
(Part of my administrative assistant series on Google Sheets.)


Number entries can be easily formatted within Google Sheets.  In fact, if you know how you want the entries formatted prior to entering the data, you can even pre-format the cells so that the formatting occurs as you enter data!

Options on the Formatting Toolbar:
The most common formatting options are found right on the Formatting Toolbar -
Here you can easily change a number to currency or a percentage, establish the number of decimals desired, and access the formatting menu.
Menus for Formatting:
There are actually two locations you can see the menu options for formatting - under the Format Menu and selecting Number, in addition to the formatting toolbar when clicking the "123" icon -
The menu has many of the most popular formats, but notice at the bottom there is even a choice for More Formats where you can explore additional currency and date/time formats, or even create a custom format.  
Number as Text: If you…

Google Sheets - Inserting Rows/Columns

Image
(Part of my administrative assistant series on Google Sheets.)

There are several ways to insert rows and/or columns in a spreadsheet in Google Sheets.  The easiest way is to highlight the row (or column) you want to add next to by clicking on the row or column name (the letter or number...).  Then use one of these methods:

Go to the Insert Menu and select Row above or Row below, as wantedRight click on select Row above or Row below, as wanted

But what about if you want to insert more than one row?  Do you have to add one row at a time?
NO - you do not.
You can instead highlight as many rows (or columns) as you wish to add -
Then using one of the two methods above add MULTIPLE rows or columns at one time -
A time saver for those of you that were adding individual rows or columns :-)

Google Sheets - Using "Fills" for Quick Data Entry

Image
(Part of my administrative assistant series on Google Sheets.)

Probably one of the biggest time-savers is using a fill to quickly enter a large amount of data.  Entering the same item(s) over and over again can get tedious quickly, even if using copy and paste.  Here's how and when you will want to know and use the fill option when working in Google Sheets.

First, when can you use this?  Fill will work for the following situations:

you are entering the same data for all cells an easily recognized pattern of entries is usedthe entries are a numerical seriesEntering the same entry: Type the entry into the first 1 or 2 cells that should contain it; then select those cells so they are highlighted -  Grab the little blue square (as I am above) in the bottom-right of the highlighted area, and drag down/across as far as you want the entry continued -

Entering data from a pattern: 
Enter the first couple entries for the pattern; then select those cells so they are highlighted -
Grab the li…

Google Sheets - Basics of Data Entry

Image
(Part of my administrative assistant series on Google Sheets.)

There are several tips that can help when doing data entry on Google Sheets.

General Terminology:

CELL - an individual location for data

ROW - line of cells across the spreadsheet; denoted by a number

COLUMN - line of cells down the spreadsheet; denoted by a letter

CELL NAME - denoted by the column name and row name of the cell; example - the cell found by the intersection of row 4 and column b has a name of B4.


Using your Keyboard:
You can ditch the mouse/trackpad while doing quick data entry by keeping your fingers on the keyboard.  Use:

TAB - to move across a rowRETURN/ENTER - to move down a column You can also use the arrow keys if you have them on your keyboard. 
Keep a leading ZERO: You can keep a leading ZERO (0) in a cell by first inserting an apostrophe -
When you click on ENTER/RETURN/TAB, the apostrophe will not show but the ZERO will remain-

Enter Line Breaks inside a Cell: If you want data on multiple lines within…

Google Sheets - Freezing Rows or Columns

Image
I'm working with several people whom are dealing with very large spreadsheets right now.  Unfortunately it took me too long to realize that they were unaware of this very useful Sheets feature - the ability to freeze some of the rows and columns so they could always view (in their case) the student names in the first column, and the data fields in the first row.

Here is a spreadsheet with some sample data.  I want to always see the top row (Row 1) and the first column (Column A) -

Go to the View menu, mouse over the first option of Freeze, and you get a pop-up that shows your choices for freezing rows and columns -
The options 1 row/column and 2 rows/columns are available as they are the most common needs.  But occasionally you need more than that, so there is the customizable option of Up to the current row/column - this is based on the cell you current have active (selected) on the spreadsheet.

Once you select what you want to freeze, a think gray border line appears where you…

Google Sheets - Notifications

Image
Have you ever wondered how you can get notifications when changes are made by others to a Google Spreadsheet?  It's very easy to setup.

If you go to the Tools Menu, select Notification rules... -

You have 2 options to select from in the pop-up window.  The first is if you want all changes made to the spreadsheet or just when a form is submitted (this second option can also be set up in the form, if one is connected to the spreadsheet).  The second is if you want a daily notification or an email each time a changes occurs.  Once setting these, click Save -

After you have set the notifications, you will receive an email that looks like this -

If the email is not in your inbox, check your Spam/Junk as it is coming from Good Docs, not the person that made the changes.  Also, you will not receive notifications for the changes you make yourself.

Google Forms - Copy Summary Graph/Diagram to Use Elsewhere

Image
If you are looking at the summary view of the results for some Form data you have collected, you can  copy a chart or diagram that you want to use elsewhere very easily and quickly.

Simply hover over the chart or diagram, and a copy icon will appear in the upper right corner of it.  Click on that -

You will get a message that it has been copied to to the clipboard in the lower left corner - 
You can then paste it into the location you want to use it.  Here's an example in a G Doc - 
I also pasted it into: Slides, an email, a Sites webpage, in my Blogger.  A nice feature addition that saves time from taking screenshots!