What Is the Blue Corner of Google Sheet Cell

This Google Sheets tutorial will assist take you from an absolute beginner, or basic user, through to a confident, competent, intermediate-level user.

Google Sheets is a hugely powerful tool, for everything from digital marketing to finance modeling, from project management to statistical analysis, in fact, just about any activity involving the recording and analysis of data.

And if you're (relatively) new, it actually pays dividends to learn how to use Google Sheets correctly. This tutorial volition help you transition from newbie to ninja in curt order!

If you're new to Google Sheets, then I recommend you showtime from the beginning of this article.

Still, if yous've used Sheets before, experience free to skip sections i and 2, and begin with the Data and basic formulas section.

A template is available for copying to your Drive, to back-trail this tutorial:

In improver, various advanced resources are listed for you to have things a step further. Look for this logo: Advanced Resources

Contents

1. How to utilize Google Sheets: Total Beginner
   • What is Google Sheets?
   • How is it different to Excel?
   • How to create your first Google Sheet
   • The Google Sheets editing window
   • Working with data in Google Sheets
2. How to use Google Sheets: The editing window
   • Editing columns and rows
   • Creating new tabs
   • Formatting
   • Removing formatting
3. How to utilize Google Sheets: Data and basic formulas
   • Dissimilar types of data
   • Doing math on numbers
   • Starter functions: COUNT, SUM, Boilerplate
   • Splitting information in cells
   • Combining data in cells
4. How to use Google Sheets: Killer features
   • Adding comments and notes
   • Sharing your Canvass
   • Existent-time Collaboration
5. How to apply Google Sheets: Intermediate techniques
   • Freezing panes for easy viewing
   • Understanding jail cell references
   • Basic conditional formatting
   • Sorting & filtering information
   • Adding Charts
   • Using the Explore feature
   • BONUS: The VLOOKUP function
6. How to apply Google Sheets: Next steps

💡 Learn more
Google Sheets Essentials is my new beginner's course. Information technology's 100% online, on-need video grooming form designed to boost your spreadsheet skills.

ane. How to utilise Google Sheets

What is Google Sheets?

Google Sheets is a costless, deject-based spreadsheet awarding. That means you lot open information technology in your browser window like a regular webpage, but yous have all the functionality of a full spreadsheet application for doing powerful data assay. It really is the best of both worlds.

How is information technology different to Excel?

No doubt you've heard of Microsoft Excel, the long-established heavyweight of the spreadsheet world. It's an incredibly powerful, versatile piece of software, used past approximately 750 one thousand thousand – 1 billion people worldwide. So yeah, a tough human activity to follow.

Google Sheets is similar in many means, simply besides distinctly unlike in other areas. It has (more often than not) the same prepare of functions and tools for working with data. In fact, some people mistakenly call it "Google Excel" or "Google spreadsheets."

With the risk of getting into an opinionated contend about the strengths/weaknesses of each platform, hither are a few primal differences:

• Google Sheets is cloud-based whereas Excel is a desktop programme. With Sheets, you lot'll no longer take versions of your piece of work floating around. Everyone always sees the same, near up-to-date version of Sheets, showing the same spreadsheet data.
• Collaboration is baked into Sheets, so information technology works extremely well. Excel is still trying to play catch up here.
• Both accept charting tools and Pivot Table tools for data analysis, although Excel's are more powerful in both cases.
• Excel can handle much bigger datasets than Sheets, which has a limit of 10 1000000 cells.
• Being a cloud-based programme, Google Sheets integrates really well with other online Google services and third-political party sites.
• Both have scripting languages to extend their functionality and build custom tools. Google Sheets uses Apps Script (a variant of Javascript) and Excel uses VBA.

For the textile nosotros'll embrace in this commodity, there's very piffling departure betwixt the programs, however.

For a deep-dive into the differences between Excel and Google Sheets, have a wait at ExcelToSheets.com

Why utilize Google Sheets?

How'due south this for starters:

• It'due south free!
• It'south collaborative, and then teams can all run into and work with the same spreadsheet in real-time.
• It has plenty features to exercise complex analysis, but…
• …it's also really easy to utilise.

Need more convincing? Here are 5 more reasons from Google themselves.

Tin it notwithstanding do advanced stuff?

Absolutely! You lot can build dashboards, write formulas that make your head spin and even build applications to automate your job. The heaven's the limit!

You'll observe lots of resource on this site for intermediate/avant-garde level users, too every bit comprehensive online training courses.

Ok, where do I get information technology? How to create your kickoff Google Canvass

If this is your beginning fourth dimension with Sheets, caput over to the Google Sheets homepage:

Click on the Get To Google Sheets button in the centre of the screen. You lot'll be prompted to login:

And so you make it at the Google Sheets domicile screen, which will prove whatsoever previous spreadsheets yous've created.

Click the huge green plus button to create a new Google Sheet:

Opening your first Google Sail from Drive

You tin can create new Google Sheets from your Drive folder by clicking on the blue NEW push:

When you create a new Google Canvass, it'll be created in your principal Drive folder (your root folder):

(Note: Don't panic if you don't see the Sheet yet, it may not bear witness up until you've renamed information technology. See next pace on how to do this.)

Hither you tin elevate it to a dissimilar folder if you wish (to proceed things organized). Practise this past clicking-and-holding the file, and dragging to where you want it to become:

The Google Sheet editing window

This is what your bare Google Sheet will look like:

Y'all can rename your Sheet in the top left corner. Click on where it says Untitled spreadsheet and type in whatever name you desire to give your Sail, in this case "New Sheet".

And then let'south introduce some fundamental terminology and the primal concept upon which spreadsheets work:

There are two menu rows in a higher place your Canvass, of which we'll see more than farther on in this tutorial.

The chief window consists of a grid of cells. An individual cell is a single rectangle, at the intersection of one column and 1 row, and it'll hold a single piece of information.

The columns are vertical ranges of cells, labeled past messages running across the summit of the Sheet.

Rows are horizontal ranges of cells, labeled past numbers running downward the left side of your Canvass.

In the example in a higher place, I highlighted column East and row 10.

** The fundamental concept of spreadsheets: **

Column East and row 10 intersect at one cell, and one jail cell only. Thus we can combine the column letter of the alphabet and row number to create a unique reference to this cell, E10. At present when nosotros desire to refer to this cell, for example to access data in this jail cell, we employ the address E10 to practice that.

Understand this and you empathise spreadsheets. The residuum is merely details!

Entering, selecting, deleting and moving data

At present the fun actually starts! Let's start using this new blank sheet nosotros've created.

Click cell A1 (that's the intersection of column A with row 1, the prison cell in the top left corner of the Sheet) and you'll see a blue box around the cell, to signal information technology's highlighted:

Then you tin can simply start typing and you'll run across the data beingness entered into that cell:

Hit enter when you've finished entering data and you'll move downwardly to the adjacent cell, having completed your information entry. If you hit the Tab key instead, y'all'll movement across 1 cell to the correct!

It's worth pointing out an important nuance here:

Clicking ONCE on the cell highlights the whole cell. Clicking TWICE enters into the cell, so you tin select or work with the data only.

If you find yourself stuck inside a cell, you can press the ESCAPE cardinal to deselect the contents and become up a level, to merely having the cell selected.

Try information technology for yourself and run into how the cursor shows up inside the cell when yous double-click, assuasive you lot to edit the data.

To delete the data we merely entered, either click the cell once and striking the delete primal, or, click the cell twice and so press the delete central until all your data is cleared out.

💡 Larn more
Check out my make new beginner's course: Google Sheets Essentials. This is a 100% online, on-demand video training grade designed specifically to help you boost your spreadsheet skills.

Aid! I made a mistake

First of all, don't panic!

Google Sheets saves every step of your work so yous can always go back a footstep (or 2) if needed.

Press Cmd + Z if y'all're on a Mac, or Ctrl + Z if you're on a PC and you lot'll undo your previous step. Keep pressing and y'all'll simply become farther back through your changes. (Pressing Cmd + Y on a Mac, or Ctrl + Y on a PC moves your frontward, to redo your final step.)

You tin can also disengage using the Undo arrow on the menu:

Creating a basic table

Right, with all that in mind, it'south time for a quick exercise.

See if y'all can create the following table for our fictitious gym membership site, by inbound the data into the correct cells (there is no formatting or other tricks used at this phase):

Feel free to employ your own data if you wish. Also note that the dates entered above are in US format, with the Month kickoff, so don't worry if your tabular array has the Solar day outset.

ii. How to use Google Sheets: The working surround

Changing the size, inserting, deleting, hiding/unhiding of columns and rows

To select a row or column, click on the number (rows) or letter of the alphabet (columns) of the row or column you want to select. This will highlight the whole row or column blue, to point you take information technology selected.

To change the width of a column, or top of a row, hover your cursor over the grey line denoting the edge of the cavalcade or row, until your cursor changes to look like this:

Then click and elevate the cursor left or right to change the width of this column. It's the same procedure to modify the tiptop of rows.

Pro-tip: To quickly alter the cavalcade width to fit your jail cell contents, double-click when you've hovered over the greyness line.

How to add columns in Google Sheets: To insert additional columns or rows, click on the existing column or row next to where y'all'd like to insert a new column or row. With the column or row selected (highlighted blue), right-click to bring upwards the options menu, then select Insert Earlier (or Insert Later) for Columns, or Insert Above (or Insert Below) for Rows:

Adding actress rows and columns at stop

If yous reach the outer edges of a Google Sail, y'all'll notice the rows and/or columns terminate. Only don't worry, you can add more.

If you've scrolled all the fashion to the bottom of your Canvass (or added that much data), yous'll notice that you're given 1,000 rows by default. In that location's a push button to add more rows if you need, either 1,000 as shown, or whatever number you wish (up to a limit, more than on that below).

If y'all reach the right edge of the Sheet, i.east. the final column, so you add more columns in the standard way. Right-click a jail cell in the last column to bring upwards the menu and then choose to add together a column to the right.

Pro-tip: If you want to add together more than than 1 cavalcade, there'south a trick to do it in 1 get. Every bit an example, say you wanted to add three new columns to the correct side of your Sheet, begin by highlighting the last three columns that are there already, then right-clicking and choosing to insert new columns. It'll then insert three new columns for you!

Data Limit: Finally, continue in heed that each Google Sheet is limited to 10 million cells, which sounds like a lot but soon fills up. Anyway, you'll find Sheets slows downward considerably before reaching that limit. Almost people study a slight slow down with tens of thousands of rows of information and complex formulas and models.

Adding/removing multiple sheets, renaming them

Super easy!

Click the large plus button in the lesser left of your Google Canvas to add a new Canvass (also called a Tab).

Why utilize multiple tabs within your Google Sheet?

Well, like a book with capacity on different topics, it can assist split up unlike data and go on your Sheet organized.

For instance, you might accept a Sheet solely to record your global settings (any variables similar proper name, e-mail, tax rate, headcount…) and another for transactional data, and all the same some other for the analysis and charts.

The push with the iii bars, side by side to the plus, is your alphabetize button, listing all of the tabs in your Google Canvas. This is super useful when you commencement having a lot of different tabs to manage.

To rename a canvas, or delete a sheet, click the minor arrow adjacent to the name (eastward.g. Sheet1) to bring up the carte. Here yous'll see the option to rename, to delete, or fifty-fifty hide (and unhide) Sheets.

For naming, I endeavour to indicate what'southward in that tab, then use names like Settings, Dashboard, Charts, Raw Data.

Formatting

Y'all'll find all of the formatting options on the tiptop toolbar, then you tin center your headings, make them assuming, format numbers as currency etc. You may find them all on one single row, or you may discover some under the More than button, as shown in this prototype:

They're similar to a word processor and pretty cocky-explanatory. You can always hit undo if you make a mistake (Cmd + Z on Mac, or Ctrl + Z on PC).

Try the following to format our basic table:

> Brand the heading bold and size 14px
> Centre the column headings and brand them bold
> Center the tier cavalcade
> Change the date format to 01-Jan-2018 (Hint: the date format is found under the button that says 123.)
> Add a dollar sign, $, to the fee column
> Add a border around the whole table.

Hither's a GIF to guide you:

(Note, y'all can also find the formatting options under the Format menu, between the Insert and Information menu options.)

Alternating colors

Allow me show yous the choice to add alternate row colors (banding) to your tables.

Let's utilize it to our basic table, by highlighting the tabular array and then from the menu:

Format > Alternating colors

equally shown hither:

Remember, a little bit of formatting goes a long way. If y'all Sheet is more readable and tidy, people volition exist more probable to understand it and absorb the information.

Removing formatting

This is my number ane productivity tip in Google Sheets.

To remove all formatting from a cell (or range of cells), striking Cmd + \ on a Mac or Ctrl + \ on a PC.

This volition save y'all so much time when y'all're wanting to remove formatting that isn't yours or that y'all no longer want or need.

Advanced Resource: Read more on formatting

3. How to utilize Google Sheets: Data and basic formulas

Unlike types of data

You've already seen dissimilar data types in Google Sheets in our basic table.

The key betoken to understand with spreadsheet data is that each cell contains the information itself, and a format applied to that data.

For case, suppose a cell contained:

2, or
2.00, or
$two, or
$2.00

In each example the underlying information is the number 2, only with a different format applied each time. If nosotros add 2 to each of these cells we get back the number 4 in every case (with formatting applied).

Basic data types in Google Sheets

You'll notice that currency information, percentage data and even dates are really just numbers under the hood (dates? Really? Yep, they are, but that's a discussion for some other day). They're all right-aligned, hanging out on the right edge of their jail cell.

Text is left-aligned by default.

If you desire to forcefulness something to be stored as text, yous can prepend a single quote, ' before the cell contents. So typing in '0123 will evidence every bit 0123 in your cell and be left-aligned. If you omit the single quote mark, and then it'll be stored as a number and show upward as 123 without the 0.

Doing math on numbers

Easy-peasy, merely similar yous practice on a calculator.

You click the cell you lot want to do your calculation in, type an equals sign (=) to indicate you lot're performing a calculation then type in your formula, e.g.

Notice how adding volition bear witness in the formula bar (1) equally well every bit in the cell (ii).

You'll notice that yous go a preview of the answer (in this instance, 25) above the formula.

Starting with functions: COUNT, SUM, Boilerplate

Technically you've already written your first formula in the section above on math calculations, but actually, your formula career begins when you start using the congenital-in functions (of which there are hundreds!).

Returning to our basic table, let'southward count how many members we take, what the total monthly fees are and what the boilerplate monthly fees are.

COUNT

Click on prison cell B8, type an equals (=) then get-go typing the word COUNT. You'll discover an auto-complete menu comes upwardly showing all of the functions start with C, like this:

You lot can either keep typing COUNT in total, or observe information technology and select in from the listing (hover over it and click on information technology OR hit enter OR hit tab).

Next yous'll see the formula helper window evidence upwards, which tells y'all about the formula syntax and how to fill up information technology in correctly:

In this case, the COUNT part is expecting a listing of numeric values.

You accept to select the range of cells you lot want to count. Then click on B4, concur you mouse downward and elevate downwards to B7, so that the four cells are highlighted in orange and B4:B7 is showing upwardly in your function:

And then, close the function with a closing bracket ")":

=COUNT(B4:B7)

Note: COUNT is used to count numbers. If you want to count text (for example the names) then COUNT won't piece of work (information technology'll give you a 0). Instead apply COUNTA (with an A at the end), otherwise the method is the same.

SUM

Your turn! Attempt creating a full for the membership fees in prison cell D8. Follow the same process as the count role, except use SUM and highlight the values in cavalcade D.

=SUM(D4:D7)

Average

You're on a gyre, so go ahead and calculate the average of the membership fees. Employ the Average role in cell D9.

=Boilerplate(D4:D7)

Psst, you'll detect that Google even helps yous out sometimes and suggests the verbal formula you lot were after:

Here you go:

If you make a mistake with your formula, you'll run into an errors message, probably something like #Due north/A, #REF!, #DIV/0 etc.

You'll demand to re-enter your formula and correct it before proceeding. These error messages do give a lot of context though, so they're worth understanding.

Advanced Resource: Learn more than virtually formula errors

What's the difference betwixt a function and a formula?

Well, both are used interchangeably and rather loosely and so I wouldn't get hung upwards on it.

For the pedantic, a part refers to the single method give-and-take (e.thou. SUM) whereas a formula refers to the whole operation afterwards the equals sign, ofttimes consisting of multiple functions.

Separating data with the Text to columns characteristic

Let'south suppose you wanted First Proper noun and Last Name, rather than just simply Name as we have in our expressionless famous authors membership table. How do we go about doing that?

Well of class, as with everything in spreadsheets, at that place are lots of means, only let me show yous the easiest, using the Text to Columns characteristic.

Back to our basic tabular array, create a new column to the right of Proper noun earlier the Tier column, i.e. create a new, blank column B.

Highlight the iv names and click:

Information > Split text to columns…

On the sub-menu that shows up choose Space and marvel at how Google Sheets separates the full name into a first and last name. Feel free to rename the columns Showtime Name and Concluding Name too.

Combining cells

Oh, blast I hear yous say! You meant to keep hold of that full Name column likewise.

No trouble, permit's learn how to combine text so we tin rebuild information technology.

Insert a new bare column between B and C (betwixt Last proper noun and Tier) and telephone call it Full Name, in cell C3.

Add this formula in cell C4:

= A4 & B4

That's A4, Ampersand, B4.

What it does is combine the information in cell A4 with the data in prison cell B4 and output information technology in jail cell C4.

Hmm, merely this gives an output similar this:

CharlesDickens

That's plainly not adept plenty! We need a space between the names!

Alter the formula to this, by clicking right later on the ampersand and adding double quote, space, double quote, ampersand:

= A4 & " " & B4

Here nosotros've told Google Sheets to add a space into the mix, and the output at present will be:

Charles Dickens

Voilà, that'south amend!

Your formula is sitting pretty in jail cell C4, merely how exercise you go it to work for the other rows?

Copy information technology!

You tin can either:
i) right-click, copy, move downwardly to select the adjacent cell, correct click again, click paste, or
ii) Cmd + C (on Mac) or Ctrl + C (on PC), move down to select next cell, then Cmd + V (on Mac) or Ctrl + Five (on PC), or
three) drag the formula down by holding the little blue box at the bottom correct corner of the blue highlighting effectually the original prison cell.

The neat affair is that every bit you copy this formula down, the cell references will change from row 4 to row 5, row five to row 6, etc., automatically! How cool is that!

(This is what'southward known as relative references. More on that in department 5 below.)

Hither'south a GIF to show this technique in action:

four. How to use Google Sheets: Killer features

Let's come across some of the unique, powerful features that Google Sheets has, as a cloud-based slice of software.

Comments (and Notes)

Desire to add some context to numbers in the cells of your Sheets, without having to add extra columns or mess upwards your formatting?

Add a annotate to a cell!

You can tag people (via their email address) who you want to see the comment also. They tin can reply and marker it resolved once it'due south been acted upon.

You can as well add uncomplicated notes to cells as well if y'all wish.

Comments and Notes can also be deleted when not required anymore.

To add together a comment to a cell, commencement select the cell, and so right click to bring up the menu of options. Select "Insert comment" and so simply type in your comment.

To tag somebody in your annotate, type the plus sign (+) and their name or email address (yous'll see machine-complete options from your contacts, so y'all shouldn't have to type in the whole electronic mail address).

You'll notice a pocket-sized orange triangle in the top right corner of the jail cell to signal the comment. The comment will show up when you hover over this cell. If you click on the jail cell, it'll also add orange shading to the cell background.

Comments can be edited, deleted, linked to, replied to and resolved (comment disappears from Canvass and is archived).

You can attain and control all the Comments in your Sheet from the big Comments button in the top right of the screen, next to the blue Share button.

(The first fourth dimension you tag someone in a comment, you'll exist asked to share the Canvass with them. See more on this below.)

Yous can also add together a note to cells in the same way (look for information technology in the menu side by side to Insert Comment). It'due south like a pared downward version of a comment, intended for your own reference.

Share your sheets

(If you just added a comment and tagged someone else, as shown higher up, then yous may accept already done this step!)

Yous can share your Google Sheets with other people. Since information technology's on the deject, they can access your Sheet and see the same, alive Sheet that you lot're in.

In other words if you make changes, they volition show up automatically and in well-nigh existent-time for everybody viewing the Sail.

You lot tin have multiple people viewing and working on the same Sheet.

Substantially you take three options to share you lot Sheet with:

• View-only access, then that person tin can non modify or comment on any data
• Annotate-merely access, then that person can add comments just still non brand whatever changes to the data in the Canvas
• Editing admission, so that person can make changes to the sail (including comments)

The Sharing options are constitute by clicking on the large blueish button in the elevation right corner, which will open up the Sharing settings:

You can grab the link (the URL) to the Canvas, cull the share setting (view/comment/edit) and then share that link with people y'all want to see the Sheet. (1)

Or, you can enter someone'southward email address directly, choose the share setting (view/comment/edit) and then share the Sail directly with the person. (2)

If you want to review the sharing settings or take even more control, click the Avant-garde options buttons. (iii)

The Advanced sharing settings window:

Here you can:

> Grab the sharing link (1)
> Review who has access (2)
> Change the admission rights of anyone listed (3)
> Invite new people to access the Canvas (4)
> Change the avant-garde possessor settings, to restrict who can control the sharing settings and specific view/comment rights (five)
> Confirm when you're finished (half dozen)

I've used the link from the sharing settings to share the template for this tutorial with you!

Real-time Collaboration

Ok, so yous've shared your Sheet with someone. If they open information technology whilst you're still working in the Sheet you lot'll see their cursor show upwards on whatever cell (or range) they've selected. It'll be a dissimilar colour, for example green to your blue.

If they enter data or delete data yous'll run into it happening in existent-fourth dimension!

In this example my agile cell is the blue-outlined cell . I come across somebody else, denoted by the green-outlined cell , show up in this Sheet and enter data into a few cells before deleting information technology.

Magic!

5. How to use Google Sheets: Intermediate techniques

Freeze panes

This is 1 of the near useful tricks you can learn in Google Sheets, which is why I'm recommending you acquire it today.

Sooner or later you'll work with a table of data that continues beyond the expanse you see on the screen (right now for case, I tin can see as far as row 26, only it depends on your screen size and other factors).

When you scroll down to look at information farther down in your table, you lose the column headings off the top of your screen, and therefore can't see the context of your columns.

Of course, scrolling upward and downwards to run into what the column headings are makes no sense. It'due south a sure path to spreadsheet errors and insanity!

Accept a look at this data table showing the tallest buildings in the world, which extends below the bottom of what you can see on a unmarried screen in Sheets. Scrolling results in the heading row disappearing, and then you lot no longer know which columns are which:

What y'all need to do is freeze the heading rows.

Thankfully it's super like shooting fish in a barrel.

Click on the row number of the row with your cavalcade headings in (e.g. row iii), then from the bill of fare choose:

View > Freeze > Up to current row (3)

Now your headings will stay in place. Ah, that's much better!

You tin can do the same with columns, if yous wished to freeze names for example, so you can scroll horizontally beyond lots of columns of data.

Relative/Absolute references

This is arguably the hardest concept to grasp in this tutorial. If you understand it and tin can apply it, then y'all have a actually skilful understanding of how spreadsheets work and yous're well on your way to being a skilled user.

Suppose y'all have some data in cell A1 and you enter the following formula into jail cell B1:

= A1

This formula will remember whatever information is in cell A1 and show it in prison cell B1.

Now re-create the formula (Cmd + C on a Mac, or Ctrl + C on a PC) and paste information technology (Cmd + 5 on a Mac, or Ctrl + V on a PC) somewhere else on your Sheet, for example cell D5.

Nothing volition show upwards in D5. In fact you lot may exist wondering whether your copy-paste worked. Have a look in the formula bar and yous should now encounter this yet:

= C5

The formula is there, but it points to a different cell, not A1, so does not evidence the information from A1.

But information technology still points to the prison cell that is ONE TO THE LEFT AND ON THE SAME ROW equally the original formula.

Ah ha! Eureka!

The formula copied perfectly, keeping the same structure, pointing to the cell on the left.

This amazing belongings is called a relative reference, pregnant information technology'southward in relation to the cell where the formula is (e.g. ane to the left).

That's why you can drag formulas downwards columns and they'll modify automatically to calculate with data from their row.

Got that?

Now so, if yous desire to fix your formula (for example and so information technology e'er point to cell A1) then you'll want to utilize what's called Absolute Referencing.

We lock the cell reference in the formula, and then Google Sheets knows to not move the reference when the formula is moved.

The syntax uses a dollar sign, $, in front of the cavalcade reference and in front end of the row reference to lock them each respectively, like so:

= $A$1

At present, wherever you copy this formula, the output will e'er bespeak to cell A1 and return you the data from jail cell A1.

Note, yous can just lock the cavalcade or merely lock the row reference, and leave the other part as a relative reference, merely that is beyond the telescopic of this tutorial.

Working with formulas beyond sheets

Sticking with the topic of referencing other cells for the moment, how does one go about linking to data on a different Canvas?

Returning in one case over again to our basic gym membership table for dead famous authors, in Sheet ane, permit's retrieve the table heading and print it out in Sheet ii with this formula, entered into prison cell A1 on Canvas 2:

= Sheet1!A1

Note the exclamation indicate at the cease of the reference to Sheet1, i.e. Sheet1!

Now let's exercise a simple sum of information on Sheet 1, but show our answer on Sheet two.

In cell A3 in Sheet 2, enter the following formula:

= SUM( Sheet1!F4:F7 )

This will return the sum of the range of cells F4 to F7 in Sheet 1 and impress out the reply in Sheet 2.

Basic conditional formatting

Conditional formatting is a powerful technique to apply different formats (for example background shading) to cells based on some weather condition.

Let'due south meet an instance of conditional formatting, that is, formatting based on variable conditions.

For example, in a fiscal model, y'all might testify positive nugget growth with a green font color and a light green background, whilst negative growth might be shown with red lettering on a calorie-free red background. This gives extra context to your numbers, and pre-circumspect attributes (the colors) help to convey the bulletin more efficiently.

Taking the obviously copy of the membership table (striking Cmd + Z on a Mac, or Ctrl + Z on a PC to become dorsum if you lot need), highlight the final column of the membership fee, then from the menu:

Format > Conditional formatting

Here you can choose a rule, for instance, values less than $100 and highlight them blood-red:

Notice the range is shown (1), and then a drop-downward menu to choose a rule (2) and and then the formatting option (3), which is default red in this example, although you can become completely custom if you lot cull.

The power of conditional formatting is to highlight data dynamically. The formatting is based on a rule, so if another value should drop beneath the threshold ($100 in this example), it volition trigger the formatting rule and be highlighted ruby.

Avant-garde Resource: Conditional formatting to bear witness % change

Sorting data

Sorting your information is a common request, for instance to testify transactions from highest revenue to lowest revenue, or customers with the greatest number to least number of purchases. Or to show suppliers in alphabetical society. You lot go the idea.

And so let's sort the expressionless famous authors gym membership table, from earliest members to nigh recent members, i.e. nosotros'll sort our table based on the date cavalcade.

Highlight the whole tabular array, including the header row. And then from the carte du jour:

Data > Sort range…

and exist certain to bank check the "Data has header row" selection. So you lot tin select the column yous desire to sort by, and sort selection from A to Z, or Z to A.

This re-sorts the table, showing the earliest members start:

Filtering information

The adjacent step afterward sorting your data is to filter it to hide the stuff yous don't want to come across. And then yous can just look at the information that is relevant to the problem at mitt.

Taking the globe'due south tallest buildings information again, let's utilise a filter to only evidence skyscrapers built before the 2000s.

Click somewhere inside the data table (click on a cell containing information in the tabular array), and so add together the filters from the menu:

Information > Filter

or by clicking this icon on the carte du jour bar to a higher place your Sail:

You'll notice a low-cal green shading applied to row and column headings of your filtered table, and as well a green border around your table. Virtually importantly though, you'll at present have little green filter buttons in each of your heading cells.

To filter out all the buildings built in the year 2000 or after, click the piffling green triangle next to the column heading Built, to bring upwards the filter carte.

You'll notice you can manually select or de-select items to show. Let's create a rule this time though.

Under the "Filter by condition" section, choose "Less than" and enter 2000 into the value box:

Striking OK.

Ta-da!

You'll come across a reduced table with just 9 results. The 9 skyscrapers congenital before the yr 2000:

To remove the filter, click the green triangle button over again (at present solid greenish) and under the "Filter by conditions" gear up the rule to "None".

There's also a native FILTER function, by which yous tin can formulaically filter your data.

Advanced Resource: How to use the FILTER function

Adding Charts

Equally a final exercise with the tallest edifice information, allow's draw a nautical chart to show the buildings built by twelvemonth, so we can run into the trend graphically.

First, let's sort the table past the year Built cavalcade, from oldest to newest. We tin can practise this using the sort function that is provided in the menu when you click on the green triangle (from the filter).

Then highlight this single cavalcade called Built and from the card:

Insert > Chart

This creates a default chart in your window and opens the chart editing tool in a sidebar.

Here you can change your information range and chart type, as well as a multitude of nautical chart custom formatting options (which are also mostly accessible by clicking on the elements directly in the chart).

A nautical chart is an object in your Sheet now. Click it to select it, so it has a blueish border around it. You can resize information technology and drag it to motility it, just as you lot would with an epitome.

Using the Explore feature

The robots are coming!

Before long we won't have to create circuitous formulas or charts ourselves. Nosotros'll merely ask our Sheet to do it for us. Sound far-fetched?

Well, yous can practice that now! The future is here.

(Soon we won't have to open Google Sheets at all, we'll simply type, or more likely speak, our data questions into a dashboard console, and out volition pop the answers, but I digress.)

Google Sheets has a feature called Explore, powered by Automobile Learning/AI/Deep Learning/Neural Network sorcery, that will ingest your data, analyze it and show you some common answers (like the SUM, COUNTs etc. we've seen so far) and basic charts:

Let's run across an example, using the data about the world's tallest buildings. Highlighting the column showing the years when each skyscraper was congenital and then clicking on the Explore button (lesser correct corner) leads to these insights:

52 buildings in my gear up. The earliest tower on the list was built in 1931 (the venerable Empire Land Building!) and the most recent was built in 2017. The average of all the years built is 2007.

Some interesting and useful data points, without having to practice a lick of work!

I mentioned charts, and so allow'southward see an example of that with the Top (ft) column. Google Sheets Explore creates a Histogram for united states of america, showing the distribution of belfry heights:

Nosotros also get this interesting and insightful summary:

"Ranges from 1,148 to 2,717, but 80% of values are less than or equal to 1,480."

Wow! At a glance, we accept the min and max heights, simply more impressive, we know that lxxx% are nether 1,480ft tall.

Not all of the "insights" are useful, and I don't use this characteristic much myself however, only information technology shows a glimpse of how we'll all work with Sheets in the hereafter.

If yous're notwithstanding not worried about AI taking over your job (whether that's data assay work or something else) in the adjacent 10 to 50 years, so take a read of this article.

BONUS: VLOOKUP

No "How to use Google Sheets" article would be complete without at least a quick look at the VLOOKUP role.

Why?

Because it's the most famous function. Tell anyone you lot work with data and spreadsheets and they'll immediately enquire, "yes, but do yous know how to do a VLOOKUP?"

It'due south a good bellwether for spreadsheet competency, even though there are ultimately improve ways to work with data. It'southward likewise a relatively advanced formula compared to what we've seen and so far, then if you can understand information technology, information technology bodes well.

What does it practise and then?

It'due south used to search for a term and render information about that term from a dissimilar tabular array.

Mostly, it's used when you lot have ii tables that share some common attribute (e.g. a proper noun, ID number, or electronic mail), but otherwise, store different information. Suppose you desire to bring this information together though. Well, y'all tin can, and you lot link the information via that common aspect.

1 table might have details an employee'due south name and accost, and the other table might have their proper name and work details like championship and salary. You can use the VLOOKUP function to bring these bits of information together in a unmarried tabular array.

The syntax is as follows:

= VLOOKUP( search_term, table_to_search, column_number, FALSE )

In words: yous select a search term that you search for in the first column of the search table. If you detect it, you render a piece of data from the search tabular array that relates to the search term (because it's on the same row). The cavalcade number refers to which column of the search table you return the data from (1 beingness the cavalcade you searched in, so typically this number is 2 or greater).

Let's see an instance, by adding addresses to the expressionless famous authors' gym membership tabular array.

I've added a second table to Sail 1, showing the addresses of our dead famous authors. What I'd like to practice is add that data to the original gym table.

I want to exercise it efficiently with the VLOOKUP formula, rather than calculation them manually. Not only is it much, much faster with larger tables (imagine x chiliad rows of data), but it'southward also less mistake-prone.

The address table is in columns I and J, with the names in cavalcade I and addresses in column J.

So I'll search for my author name in column I and return the address from column J, and print the output into whichever cell I created my formula in.

The formula is:

= VLOOKUP( C4, $I$3:$J$vii, 2, FALSE )

And this is what'southward happening:

I search for the name (1) in the search table (2) and render the data from cavalcade ii of the search table (three).

I don't expect y'all to understand all of this immediately (and at that place's a lot more to this formula than what I've shown here), but if you try it out and persevere, y'all'll get in that location and realize information technology's actually non also difficult.

(The FALSE argument, the final piece of data in the VLOOKUP formula ways yous want to do an exact lucifer. 99.nine% of the fourth dimension you use a VLOOKUP, you'll want to use False.)

Advanced Resource: Dynamic charts with VLOOKUP

vi. Adjacent steps

💡 Learn more
Check out my brand new beginner'southward course: Google Sheets Essentials. This is a 100% online, on-demand video training course designed specifically to aid you boost your spreadsheet skills.

The one-time adage "Do makes perfect" is as true for Google Sheets as annihilation else, so hop to information technology!

Keep upwards-to-date with new articles, course launches and exclusive offers, by signing upward for my Google Sheets newsletter, and get my free Google Sheets ebook with 100 tips.

When yous're fix, bank check out the intermediate and advanced Google Sheets tutorials on this site.

Check out my Google Sheets Essentials form for beginners.

Bank check out my gratis Advanced Formulas 30 Day Challenge course.

If all else fails, ask for assist on the Google Sheets forum.

Wow, that'southward it for this Google Sheets tutorial! Happy spreadsheeting!

---

Looking for a Google Sheets expert to help with your next project? Schedule a consult today with a Ben-approved Google Sheets skillful.

saxtonconquar.blogspot.com

Source: https://www.benlcollins.com/spreadsheets/how-to-use-google-sheets/

Share this post