Autofill, Formatting, and Conditional Formatting in Google Sheets
We often use autofill, formatting, and conditional formatting in Google Sheets. Formatting gives a cell its outlook. Autofill detects patterns in data and projects onward.
We should leave some things to machines. Autofill scans out general rules in your selection. By filling in the nearby cells, it saves our time.
Formatting is not for looks. It makes data not only easy on the eyes but also practical. Border, colour, number formats, etc. make up formatting.
Conditional formatting only affects certain cells. It might change the format of a cell if the cell passes one or more criteria set for it.
Autofill is not a function but a feature of Google Sheets. Autofill is the basic shortcut that all beginning Google Sheets users should know.
Sometimes, manually inputting data is so impractical we need a better way. Autofill follows trends among cells, guesses, and, accordingly, fills in the next cells.
How to Use Autofill in Google Sheets
Autofill is a coal-coated diamond. This just means it lacks the sophisticated implementation it deserves. The processes to use Autofill on computers and phones differ.
How to Use Autofill on Computers
- Select the cell(s) to use as a reference. For instance, to fill in ID numbers from 2000 to 2020, type and select the first two (“2000” and “2001”)—this is all Autofill needs.
- Hover the cursor over the small blue square at the bottom right corner of your selection.
- Press and hold, and drag down the blue square the number of places you want to fill. You will see those places filled with “2002”, “2003”, and so on up to “2020” or more.
How to Use Autofill on Mobile Devices
- Tap and drag from the first cell of your reference set all the way down to the last cell you want to fill. You just need to type the minimal defining terms of any series e.g. “2”, “4”, and “8”. Here, the third term “6” was necessary since 2*2 and 2+2 are both four. Therefore, we must, to be safe, specify if the series is arithmetic or geometric. Now we drag from “2” downwards—or sideways.
- Tap on the selection, and tap “Autofill”. You might need to click the vertical three dots to find the Autofill option. Now you are done. Autofill has filled the cells with “16”, “32”, etc. In this series, the next number is two more than the current one.
Some Things to Know About Autofill
- Autofill works in any direction. However, the reference cell(s) must always stay at the start.
- Autofill works with many formats. It works with numbers—we generated from “2001” to “2020”. Besides, Autofill works with numbers surrounded by text; thus, it can project “Person 1” up to “Person 20”. Moreover, it works with dates such as “11/1/20” (November 1, 2020) to “11/20/20” (November 20, 2020), etc.
Formatting is all about making your sheet readable. Furthermore, it makes your sheet appeal to viewers’ eyes and guides them to where important stuff is. Border; colour; number format; and text face, size, and style comprise formatting in Google Sheets.
Border is the stroke around a cell. The default border is zero. To get a border, follow the following steps:
- Select the cell(s).
- Click on the Border button, a square split into four. On PC, it is at the top. On mobile, it is at the bottom when you select more than one cell, but this is a slight limitation.
- Select the colour and width or style of the border. Pick from the colours Google Sheets gives you or go Custom. Choose between solid and dotted borders of varied lengths.
- Click the button that corresponds to the layout you want. Get creative with the buttons.
Fill Colour and Text Colour
Colour in Google Sheets may refer to fill colour or text colour.
Pick the fill (i.e. cell background) colour with the Fill icon. You may select one or set a custom one using its Hex Code.
Select text colour from the “‘A’ with a bar under it” icon. The text colour should complement the cell colour to look pleasant.
Number format is available directly at the top from PC. By contrast, it lives in the letter-A (Formatting) icon at the top > “Cell” tab > “Number format” on mobile. This can be of help. For instance, to treat “1917” as a film title, select “Plain text”. To show numbers as currency, choose “Currency”.
Text formatting is changing the visible attributes of text. In Google sheets, these are font, size, and style.
Text Face, Font Face, or Font
The text face, font face, or font is the look of your text. You can select various fonts from the drop-down menu at the top that says something like “Arial”.
The font size button is next to the Font drop-down menu. Making your font too little makes it hard on the eyes, but enormous fonts may ruin your document.
Google Sheets lets you style text by making it bold, italic, and strikethrough.
Besides, you may alternate cell colours and discover other fun things on your own.
Conditional formatting is not only one of the most useful but also the most fun part of Google Sheets. To start, on PC: go to Format > Conditional Formatting, and on phone: click Formatting and scroll down.
Conditional Formatting Rules or Types
Conditional Formatting is setting up a system that changes cells that meet some conditions. These conditions can be of four types: string, date, number, and custom.
The first seven or so functions work with strings. Thus, they help find key words or markers within text.
Next, find three functions that check conditions that relate to dates.
Again, the next eight or so functions work with the numerical values of cells.
If you want to have fun, try out these for yourself. For now, we are going to talk about custom functions, the fourth kind we may use to format cells.
The Custom Formula
The custom formula in Conditional Formatting lets you make your own formula. You cannot put any function here like “=SUM(A1:A3)” or “=NOW()”. Your function should output TRUE or FALSE. Here, TRUE means “Yes, conditional formatting applies to this cell.” By contrast, “FALSE” means “No, conditional formatting does not apply to this cell.” Cells that obey the custom formula will evaluate TRUE and gain a new look; the others will not.
Let us set up three scenarios.
First, Column A contains strings, of which each is a SEO-optimised title. We know that such titles are under 90 characters, and we should check if they are. So, we select the range, and go to Conditional Formatting.
We need two formulas: “=LEN(A1)<=90” and “=LEN(A1)>90” (The length function gives the length of text in characters). Importantly, think of just the first row first. Like Autofill, the same formula will apply to A1, A2, and so on, but you need not consider those now. Set a green cell background for the first and red for the second, and thus, find out your SEO eligibility.
Second, Column B contains dates, each a doctor’s appointment. Next to it, Column C contains checkboxes (Checked=“TRUE” and unchecked=“FALSE”) that say if you visited the doctor the corresponding day. You should mark the missed appointments red and make bold one for today and the upcoming ones.
Again, we need two custom formulas: “=AND(B1<TODAY(),C1=FALSE” for red and “=AND(B1>=TODAY(),B1<=TODAY+2)” for bold (The TODAY function returns the date on refresh). Voila! You have done it.
Suppose you are a teacher and want to highlight the names of the toppers. Earned percentages are in Column K, and the names span Column A. The first two rows are header rows. You want a bright yellow to match these students’ bright futures.
You need only one function: Select Column A, and use the custom formula “=K3>=.9” for yellow. What could you do to make the top-scorer’s name green? You Column A again and type “=K3=MAX(K1:K)”; but, wait! Nothing is green yet!
This is your introduction to the hierarchy or order or priority of Conditional Formatting Rules. Rules made first take priority over subsequent ones. To fix this, simply drag the formula up. On mobile, it is fairly easy, but on a computer, the six dots appear at the left of the Rule.
It is fun to make your own formulas. You can check out my Film Ratings spreadsheet for a sneak peek at IF and IFS, INDEX and MATCH, SUBSTITUTE, etc. Without doubt, these are the topics for the next Google Sheets article. Coming up later are IMPORTRANGE, RANDBETWEEN, etc., which import from other spreadsheets and invoke randomness respectively.