Uploading Files to Spreadsheet.com

Uploading Files to Spreadsheet.com

One of the great things about Google Sheets is that, when it comes to uploading images, you have the option to either upload images over cells, where they can be moved and resized freely or upload the images inside the cells. It is impossible to do the latter in Microsoft Excel. Spreadsheet.com does this and then some.

Not only can you upload images inside the cells, but you can also upload other file types such as PDFs, DOCX et cetera – a feature that is non-existent in both Google Sheets and Excel.

Uploading an Image over Cells

To upload an image over a cell(s) in Spreadsheet.com, go to Insert > Image and select the desired image from the respective source:

Go to Insert > Image

It’s worth noting that Spreadsheet.com gives you the option to upload your image from various sources. You can upload from your device, Google Drive, Box, OneDrive or Dropbox:

 

Various upload sources are available

 

 

Uploading an Image inside a Cell

To upload the image inside a cell, you have to first set the data type of the column to “attachment”. To do this simply click on the respective column header, click on the dropdown on the far-right of the column header, click on “Edit data type…” and select “attachment”:

 

Go to the column header and click on “Edit data type…”

 

Set the type to “Attachment”

 

Notice that in the above pop-up, you have the option to make the cells of the column small, medium or large in order to comfortably accommodate the file types to be attached. There’s also the option to set the data type as strict which would prevent entry of any other data type in the column save for the specified one.

Once the column data type is set as “attachment”, images can be added inside any cell in that column. To do this, simply click on the cell to add an image to, click on the “attach” icon:

 

Uploading other file types inside cells

As mentioned before, Spreadsheet.com edges out both Google Sheets and Excel considering the fact that you can upload files such as PDFs. But how do we upload other file types? Quite simply:

  • Set the column data type to “attachment” (as explained above)
  • Upload the desired file and it’s going to appear inside the cell. 

A PDF file stored inside a cell would look like this:

Upon clicking the PDF, the document should open.

Note: Attachments cannot be added to your primary column. You’d first have to de-select the primary column setting before you can add attachments to any cell in that column. Conversely, you cannot set a column to be a primary column if the specified data type is not “Standard”. You can read more about primary columns here.

Google Sheets – Custom Number Formatting Made Easy

You can have complete control over the look and feel of the numbers in your spreadsheet. You can follow along with this example by starting with this template.

This article will walk you through the process of customizing the appearance of the numbers in your spreadsheet. It will teach you how to have control over the visual presentation of your numbers, with currency signs, arrows, and more.  This is called Custom Number Formatting. To have a comprehensive understanding of Custom Number Formatting, see this video from the Prolific Oaktree Youtube Channel.

Besides the default look which presents your data in the black font color, you can infuse some level of creativity in your presentation by assigning different font colors to enhance the message of your presentation. For instance, you may want to present debits in red and credits in green.

Take a look at the image below…

red and green custom numbers
Red and Green custom numbers

The data in red have a minus sign (-) before each, which indicates negative, hence, the use of red. On the other hand, the data in green is positive.

Changing the number formatting allows you to change the look, but the values of each cell will not change. It only gives it a different appearance through the color assigned to individual rows of data.

How To Format Your Number in Google Sheets

The procedure is simple; locate your menu bar at the top of your spreadsheet and select Format>Number. 

Google Sheet- How to format number
How to Format Numbers

You will notice that there is a preset format for the display of your data. If that is what you want, you do not need to change anything. Just click on it and you have your option activated.

However, if what you want is to give the data in each of the cells on your spreadsheet a custom look, you need to dig deeper. We will walk through how to get that done. 

Read here for Conditional Formatting an Entire Row.

Customize the Look of Your Data in Google Sheets

The way your spreadsheet looks is up to you. If you are not satisfied with the general format, this is how you can change it.

  • Highlight the columns with the numbers to be changed.
  • Click on Format on the menu bar.
  • Click on Number from the pop-down menu.
  • Check if there are pre-defined number format for you to use (if not..).
  • Click on More Formats.
  • Click on More Number Formats.

A dialogue box will pop up for you to choose the syntax:

 

Google Sheet- Custom Number formats
Custom Number format

The syntaxes are below:

Input
0; -0; “-“; “not a number”

Output
Positive number is formatted with 0

Negative number is formatted with -0

Zero is shown as a dash (“-“).

A non-number will be shown as “not a number”. Anything in quotes in programming as a syntax remains as it is. The quotes signify that it is a string variable. The semicolon is to separate the columns.

If you intend to format a long number such as “$8,000,000,”, you would use the “$#,##0.00” syntax. In case you don’t intend to include the decimal points, simply enter “$#,##” and click the “Apply” option. Voila!

How To Insert Currency Before a Number in Google Sheets

To insert a particular currency before your number The sign should precede the numbers in the syntax dialogue box. Such as we have below: 

$* 0.00 – positive

$* – 0.00 – negative

The asterisk (*) is to give space between the number and the currency sign and the two zeroes after the decimal is forcing the display of tenths and hundredths.

 

How to Add Colour to Number in Google Sheets

As earlier stated, you can give colors to your numbers for easy understanding.

To achieve customized color for your data, type the following syntax:

0[Green]; -0[Red]; ‘-‘[Black] 

NOTE: The name of the color to set for each number format will come after each of the numbers in parentheses. Make sure you enclose the color for each cell in square brackets (Check the image below).

For the image below, the name for positive numbers is GREEN, negative numbers is assigned RED, while zero is set to appear in BLACK.

Google Sheet- Custom Colour format
Custom Colour format

How to Insert Special Character in Your Data in Google Sheets

Adding special characters to your presentation can make your Google Sheet easier to understand.

To achieve this

  • Go to the “search box” if you use Windows and type “character map”.
  • Click on the result highlighted in the image below
Character Map
  • A box of special character will pop up on your screen. Scroll through to select any character of your choice.
  •  Copy the selected characters and paste them on your Google sheet
  • Select the cells you want to customize
  •  Select Format>Number>More Format>Custom Number Format
  • Add your character to the custom syntax you already have in the pop-up
  • Make sure your special character is placed after each parenthesis as we have below…

             #,##0[Green]▲; ‘-#,##[Red]▼’; ‘-‘[Black];”Not a number”

 

The result is what we have below…

Hopefully, the techniques shown in the article will help you to tell the story of your data in the most compelling way possible.

 Live examples in Sheets

Go to this spreadsheet for examples combining data.

different tables being combined

Google Sheets – Combining Tables With Different Data Using VLOOKUP

When using Google Sheets, often times you can find yourself wanting to pull data from one table into another. However, these two tables don’t always have the same types of data in the same order. As long as there is at least one value in common, you can use a few tricks to bring data from different tables together into one combined table.

See this YouTube video for a walkthrough of the steps.

We will go over how to do this using several relatively basic steps and ending with the super-useful VLOOKUP formula. VLOOKUP typically looks to the right (we’ll get there), but we can also trick the function and have it look to the left.

 Live examples in Sheets

Go to this spreadsheet for examples combining data.

combine tables with FILTER

Google Sheets – Combine Data without Query | Different Worksheets

This post is written to accompany the YouTube video showing how to combine multiple tables of data in your Google Sheet.

Often times, data that you want to analyze is spread across multiple sheets and multiple files. If you want to combine tables found on multiple worksheets and/or multiple worksheets, these four different techniques will help you join them together. Each technique results in different output. Choose the one that works best for you.

These methods are meant for data with like headers and data types.

Combine tables using UNIQUE
Using UNIQUE
Four circumstances covered
  • Keep Original Order
    • Keep the order of the original data by stacking each list.
  • Sorted
    • Sort the resultant table by any column.
  • Duplicates removed
    • Remove any duplicate lines of data if you don’t want them in your sample.
  • No blank rows
    • Remove any blank rows from your new table.

Also, data from another file can be pulled into these formulas using the IMPORTRANGE function.

 Live examples in Sheets

Go to this spreadsheet for examples combining data.

Google Sheets – SUM or COUNT Values If Cells Have Notes

Overview

If you’re using Google Sheets and you have a list of amounts that you want to sum or count based on whether or not there are notes in the cells, there’s no built-in function to do it. However, there are a relatively easy set of steps to make your own functions to get it done. You’ll be able to COUNT based on cell notes and you’ll be able to SUM as well. Previously, we’ve made custom functions to COUNT or SUM by background color.

This video will walk you through the same steps described below.

Custom formulas in action

COUNT or SUM by note
Cells being counted and summed by notes

SUM if there are notes

If you look in the live spreadsheet, you will see the custom formulas being used for summing based on whether a cell has notes. This does not work for comments, only notes. The summing is done by a formula with the nice little name of SumIfNote which takes the inputs of your range, TRUE/FALSE for with/without notes, and a trigger to recalculate as explained in the associated video.

formula for SumIfNote
Formula used to sum if there are notes

COUNT if there are notes

CountIfNote returns a 3 (which you can see above) since there are three cells with notes.

CountIfNote Formula
Formula to count cells if they contain notes
Watch the video

This site has a companion YouTube channel that has pretty much, well almost exactly, the same content. If you like this, you’ll like that.

Creating custom formulas

It is far easier to grab a copy of the linked sheet. How do we make these functions and any other custom function that you’re so inclined to write? First, go to Tools and you go to Script editor.. and to copy and paste code below.

 /**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/

function SumIfNote(sumRange, note, refresh) {
  var ss=SpreadsheetApp.getActive();
  var aSheet= ss.getActiveSheet();
  var sRange = aSheet.getRange(sumRange);
  var values = sRange.getValues();
  var sumResult=0;
  var rangeRow = sRange.getRow();
  var rangeColumn = sRange.getColumn();
  for(i=rangeRow; i<rangeRow+sRange.getNumRows(); i++) {
    for(j=rangeColumn; j<rangeColumn+sRange.getNumColumns(); j++) {
      if((aSheet.getRange(i, j, 1, 1).getNote() != "") == note) {
        sumResult += values[i-rangeRow][j-rangeColumn];
      }
    }
  }
  return sumResult;
}

function CountIfNote(sumRange, note, refresh) {
  var ss=SpreadsheetApp.getActive();
  var aSheet= ss.getActiveSheet();
  var sRange = aSheet.getRange(sumRange);
  var values = sRange.getValues();
  var countResult=0;
  var rangeRow = sRange.getRow();
  var rangeColumn = sRange.getColumn();
  for(i=rangeRow; i<rangeRow+sRange.getNumRows(); i++) {
    for(j=rangeColumn; j<rangeColumn+sRange.getNumColumns(); j++) {
      if((aSheet.getRange(i, j, 1, 1).getNote() != "") == note) {
        countResult += 1;
      }
    }
  }
  return countResult;
}

Script editor

After you go to Tools then Script editor, you come up with a blank screen. But if you don’t, just do a new script file. Paste the code into the blank window. Repeat for each code section above and name them countColoredCells and sumColoredCells. For each file, the script editor puts the “.gs” at the end of the file name which indicates that it is a Google Script. After you make these two, save them, come back to your spreadsheet, type in the formulas, and it should work for you. See the video and linked sheet for further clarification.

 Live examples in Sheets

Go to the linked sheet for examples of counting cells by notes that you can study and use anywhere you would like.