Category: Cloud Software

Software that operates, at least partially, online

sparklines no options

Sparklines in Google Sheets – How and when to use them

Posted on | by Prolific Oaktree | 1 Comment on Sparklines in Google Sheets – How and when to use them

Sparklines can be added to Google Sheets to act as miniature charts to show trends in data.  They are simpler than Charts but not as versatile.  If you have ever tried to insert a decent chart into a spreadsheet, you can appreciate the simplicity of using Sparklines.

More specific sparkline articles…

Video explanation


To insert a Sparkline, you must use a formula as there is no Sparkline function in the menus.  The syntax is

=SPARKLINE(data,[options])

Using the formula above on a simple data set with no options specified would result in simple Sparklines charts as shown below.

sparklines no options

If you wish to make Sparklines charts that look different than the simple line chart, there are many options available.  Be careful when designating the type of chart as the syntax deviates from typical syntax used when creating spreadsheet formulas, or at least it is different from typical Excel syntax.  Prepare for curly braces and lots of commas and quotes…

 

Chart types

Line

The default option for Sparklines, the same as shown above.  There is no need to specify this as it would be redundant.  See more information on line chart options in this post.

=SPARKLINE(A1:E1,{"charttype","line"})

Bar

This options makes a “stacked bar chart”.

=SPARKLINE(A5:E5,{"charttype","bar"})

sparklines charttype bar2Column

Make a chart with columns representing the values.  See more information on line chart options in this post.

=SPARKLINE(A5:E5,{"charttype","column"})

sparklines chartype column

WinLoss

Shows only two different outcomes i.e, true/false, thunder/lightning, peanut butter/jelly.  OK, maybe only one of those was a good example.  See more information on line chart options in this post.sparklines chartype winloss 

=SPARKLINE(A13:E13,{"charttype","winloss"})

 

 

Follow image below for the live Google Sheet with sparklines

docs share icon

sheets insert drawing menu

Text boxes in Google Sheets – How and when to use them

Posted on | by Prolific Oaktree | 5 Comments on Text boxes in Google Sheets – How and when to use them

Inserting a text box into Google Sheets

As discussed in this post, there are many times when a text box is a great solution for displaying large pieces of text in a spreadsheet. Inserting a text box into Google Sheets is possible, but finding out how is not readily apparent. If you have a need for a text box in your data, go to Insert -> Drawing.

sheets insert drawing menu

After selecting Drawing, the Insert Drawing options appear below. Select Text box, which is the second from the right.

sheets insert text box

Once you select insert text box, you will be able to type inside a rectange as shown below. You can also change the border, background color, and text styles. 

insert drawing text box

This is all well and good. If you knew exactly what text you wanted to type and insert and you knew you were never going to change it, then you are done and it was easy. However, in the real world, you never know exactly what you are going to type and you often need to add and delete text. Modifying text, while not terribly hard, is not as easy as it should be. You have to double click on the box, wait for another window to pop-up, and then start typing. Having said that, it’s still easier than typing the content into cells, merging and applying text wrap. It’s just not quite as easy as using a text box in Excel.

Watch the video

comment note menu

Google Sheets – Comment vs Note and how to use them

Posted on | by Prolific Oaktree | 13 Comments on Google Sheets – Comment vs Note and how to use them

Google Sheets has recently enhanced its cell commenting ability to include the option of inserting a “Note” or a “Comment.” The differences between the two are not obvious. Following is an overview of what they do and how to choose which one you should use. Follow along with the linked Google Sheet used to create this article.

Both comment and note can be selected by right-clicking on the cell (if you’re using a mouse on a Windows PC).

comment note menu

Use the Note function to leave a simple note in the cell just like you would with Excel’s comment function. With Note, you can simply add information about the cell. Only use this if it doesn’t make sense to add the note directly into the cell. Notes can be helpful, but if they are not needed, they add unneeded complexity. Often, users will overlook notes and only notice information that is typed into cells, so use them only when called for.

google sheets note

Video explanation

The Comment functionality is Note on steroids. Notes are usually sufficient if one user is creating and viewing a spreadsheet. However, if you have multiple users, especially in different physical locations, the Comment functionality can be very helpful. It enables users to have a back and forth conversation about a cell without altering its contents. The Comment functionality can be especially useful in a supervisor/staff relationship where one person is reviewing another’s work.

Google Sheet Comment

In summary, use the note function when you want to leave a quick note and nothing else. Use comment if you want to start a conversation with someone else about a cell.

 Live examples in Sheets

Go to this spreadsheet for examples of comments and notes that you can study and use anywhere you would like.

protect cells right click

Google Spreadsheets – How to protect cells and ranges of cells

Posted on | by Prolific Oaktree

Google has a feature for their spreadsheets that allows you to protect a cell or range of cells from being edited.  The protection is granular and can be customized to the point where you can choose exactly who can and cannot access cells.  The embedded video at the end of this post shows how to do this.  Here are the steps.

  • Highlight the cells or range of cells that you want to protect
  • Right-click and choose “Protect range…”

protect cells right click

Video explanation

  • After choosing Protect range, you have the option of naming the range or you can leave it as is.  Note that you can also protect an entire Sheet (or Tab) from this dialog box as well.  In the screen shot below, I will name the range Management Only to denote that only the people that boss you around are allowed to modify these cells.
  • Use the dialog on the right hand side of your screen to adjust the specifics.
  • Specify a cell or range of cells here, give it a name, and decide who can and who cannot have access.

protect cells and ranges options

  • Note that you can only set permissions for specific users if they have already been invited to view the spreadsheet before you perform this function.
  • You’re done!

Insert Special Characters into Google Sheets Using Google Docs

Posted on | by Prolific Oaktree

In the days of yore, one could insert special characters into a Google Spreadsheet.  However, after the 2014 refresh, this handy feature was taken away from us.  For whatever reason, Google decided that people using spreadsheets would not need the ability to insert special characters.  How dare they!

It seems to me this was not the best decision.  If Google wants to make inroads into the business spreadsheet market, where Excel is the unquestioned champion, it should be catering to the professions that use spreadsheets the most.  Accountants, yes you know one or two of them, are very heavy spreadsheet users.  Any decent size company employs at least one of them, and they use spreadsheets daily.  

Many of these accountants have a pedigree of Public Accounting where they are taught to use “tickmarks” to their workpapers.  These tickmarks are essentially symbols, and they can’t be easily replaced with regular characters.  Thus, Google has made their product markedly less useful to accountants in public practice, which is where millions of accountants work after college graduation for at least a while and thus start to develop their business software preferences and prejudices.

However, if you want special characters and don’t want to stop using Google Sheets, I finally have a solution. Eight years after writing this post, I released a Google Sheets add-on called Insert Special Characters. It gives you a sidebar from which you can insert any special characters without leaving the Google Sheets app.

However, if you want special characters and don’t want to stop using Google Sheets, I finally have a solution. Eight years after writing this post, I released a Google Sheets add-on called Insert Special Characters. It gives you a sidebar from which you can insert any special characters without leaving the Google Sheets app.



The Insert Special Characters Google Sheets Add On