Today I’m going to show how to make an nice map visualization of the Facebook Penetration numbers per country using Google Spreadsheets.

For this I am going to explain how to use the ImportHTML function of Google Docs and show you a quick, but very useful, way to create beautiful maps.
Don’t worry, we only need to perform 4 simple steps to get a nicely looking, informative result like shown here and in this Google Spreadsheet.

Using ImportHTML – Facebook Penetration per Country

Step 1: Create a new Google Spreadsheet

Go to Google Drive and create a new spreadsheet. Pretty easy, right?

Step 2: Find some Facebook Usage numbers

Next, we need some data on the number of users on Facebook for each country. Luckily, socialbakers.com is keeping track of these numbers by looking at the official Facebook Ad Tool and calculating the penetration percentage per country. Take a look for yourself and you will see that there are five pages with almost all the countries in the world. Let’s use the importHTML function to import these numbers into our spreadsheet.

Step 3: Use ImportHTML to collect Facebook Usage numbers

ImportHTML() is a nifty function you can use to import certain parts of a website. Basically it loads a webpage and lets you specify which <table> or list element (<ul>, <ol>) you want to import. For this to work properly, you need to provide the function with three arguments.

1. Specify the URL that you want to scrape – in our case socialbakers.com/facebook-statistics/.
2. Specify if you want to import a table or a list element – in our case the data we want is formatted in a “table” HTML element
3. Specify the index number of the element you need – in our case there is only one table so we specify “1”

TIP: To know in what type of HTML tag the data is formatted, you can use Firebug. If you are in Chrome you can right click on a number in the table and select “inspect element”.

Now that we know how the ImportHTML function works, let’s add it to our spreadsheet. Select Cell A1 on your sheet and input the following formula: =importHTML("www.socialbakers.com/facebook-statistics/";"table";"1").

Using ImportHTML() in Google Spreadsheets

After waiting for a couple of seconds look what happens:

Facebook Usage numbers scraped using ImportHTML()

Isn’t that cool?
However, we now only have the first 50 countries of SocialBakers. To get the data for the remaining countries, we can perform the same trick with importHTML – we just need to use the URL’s of the other pages. If you look closely, going to page two changes the url to www.socialbakers.com/facebook-statistics/page-2.
Again, the data on this page is in the first table tag. Knowing this, we can now add the importHTML() function for each page under the result of the other.
So go to row 51 in your sheet and add =importHTML("www.socialbakers.com/facebook-statistics/page-2/";"table";"1") to Cell A51.
Repeat this for page three, four and five as well and we have a spreadsheet with Facebook usage numbers for 221 countries!

Step 4: Make a Google Spreadsheet Map chart to visualize the numbers per country

Having done all the heavy lifting in the previous step, we now only need to visualize our numbers into a nice world map. To do this, select a cell somewhere on the right side of the data and press the “create graph” icon.
For our purposes, lets graph the penetration percentage per country as that will make is easy to compare amongst countries.

• Press “select range” and click on the column country to select all countries. Then press “Add another range” and click on the penetration column to select all penetration values.
• Now go to the next tab Charts and select the graph type Map > Geo chart regions.
• Now you can go to the third tab to select the color scheme you want to use. Play around with it a bit for yourself to find the colors you like.

When you’re done, press the blue Insert button and the map will be added to your spreadsheet!

How does your map look like? Let me know in the comments!

That’s it – I hope you liked this tutorial!
If you want to see the example in action, check out this Google Spreadsheet.
As you understand, you can use this technique to scrape and visualize tons of other information as well. Let me know in the comments what you came up with!

Rate this post

Update Dec 2013: In the new version of Google Spreadsheets SUMIFS, COUNTIFS, and AVERAGEIFS are already built in. If you didn’t activate the new version yet: read on below!

If you’re used to working in Excel, you’re probably using the SUMIFS, COUNTIFS and AVERAGEIFS functions all the time. When switching to Google Spreadsheet I ran into the problem that I couldn’t use those functions as Google didn’t include them. They only offer the single criterion SUMIF and COUNTIF  and don’t support multiple criterion functions. Luckily, they do offer the FILTER function, which we can use to solve this problem. In this post I’ll show you how you can increase your Google Spreadsheet productivity by replicating the behavior of aforementioned Excel functions.

Explaining the FILTER Function

With the FILTER function you can select a range based on one or multiple criteria (up to 30). FILTER(Range; criteria1; criteria2; ...; criteria30) So when you have the following sheet and you want to make a range that selects the price of the Cars that are Blue and that have more than 20.000 mileage you can make the following function: =FILTER(B:B;C:C="Blue";D:D>20000)

Using the Filter function to select a range based on multiple criteria

This will return a range with prices of cars that meet your criteria. Understanding this, it’s easy to replicate the SUMIFS, COUNTIFS, and AVERAGIFS functions.

The resulting range of the Filter function’s criteria

SUMIFS in Google Spreadsheet

Using the Filter function we now have the prices of all cars that meet our criteria. To sum those prices we can simply use the SUM function on our FILTER Result: =SUM(FILTER(B:B;C:C="Blue";D:D>20000))

SUMIFS in Google Spreadsheet

And more general: =SUM(FILTER(Range; criteria1; criteria2; ...; criteria30))

COUNTIFS in Google Spreadsheet

To get the count of cars that meet our criteria, we can simply use the COUNTA function on our filter result: =COUNTA(FILTER(B:B;C:C="Blue";D:D>20000))

COUNTIFS in Google Spreadsheet

And more general: =COUNTA(FILTER(Range; criteria1; criteria2; ...; criteria30))

AVERAGEIFS in Google Spreadsheet

And lastly, to get the average price of the selected cars, we can use the AVERAGE function like this: =AVERAGE(FILTER(B:B;C:C="Blue";D:D>20000))

AVERAGEIFS in Google Spreadsheet

And more general: =AVERAGE(FILTER(Range; criteria1; criteria2; ...; criteria30))

Note: Google Spreadsheet also doesn’t have the single criterion AVERAGEIF function. You can apply the same method of using the FILTER with only one criterion to simulate the AVERAGEIF function yourself.

Almost everybody that owns a website uses Google Analytics to analyze their visitor’s behaviour. While the Google Analytics web interface is useful, sometimes you just need a little more flexibility with your data by exporting it from Google Analytics into your spreadsheets.

In this post I will show you how you can set up a spreadsheet that allows you to import Google Analytics data with the push of a button and make custom graphs about your visitors like this:

Import Google Analytics data and make custom dashboards in Google Spreadsheets

1. Add the Analytics Google Apps Script

Go to Google Docs and open a new spreadsheet by pressing Create > Spreadsheet.
Don’t worry, you don’t need to be able to program for this to work, just follow these simple steps:

• In the new spreadsheet, click on Tools and then Script Gallery
• Search for “Google Analytics Report”
• Locate the script named “Google Analytics Report Automation (magic)” and press Install
• In the popup that follows press Authorize

2. Activate access to Google Analytics

You just installed a Google Apps Script that is able to import your Google Analytics data into your spreadsheet. For it to work, you need to enable access to your Google Analytics first. For this you need to add a API key that tells the script where it can get your data. Simply do this:

• In the top menu press Tools > Script editor
• Click Resources > Use Google APIs in the new window that has opened
• In the popup that opens, toggle the button after Analytics API to On

Authorizing the Goole Apps Script with you API key

• Press the Google APIs Console link at the bottom
• If you haven’t done already press “Create new project” in the next window
• Click Services on the left and toggle the button behind Google Analytics to On

You have now succesfully authorized the script to access you Google Analytics data!

3. Make a Google Analytics query configuration

Now go back to the spreadsheet and refresh your browser. Wait for a couple of seconds after the page has loaded and notice that a sheet named gaconfig has been added and an extra option in the top menu has been created called Google Analytics.
To get data from Google Analytics, you have to tell the script what data it should import. The gaconfig sheet is the place to do this.

• To add a query configuration press Google Analytics > Create Core Report in the top menu.

An empty configuration has now been added to the gaconfig sheet – don’t worry we only need to fill in a couple of fields. In this case, let’s make a query for the amount of visits and pageviews from Google Analytics for the last 30 days:

• In the first field ids, you specify the Google Analytics property ID you want to query. To get your ID, press Google Anaytics > Get Profile IDs in the top menu
• Press Grant Access to allow the script to run (you only have to do this once). Now press Get Profile IDs again
• Now select the Account, Web Property, and Profile you want to query and copy the id that starts with “ga:<some_number>”

Find you Google Analytics Profile ID

• Now paste that ID into the cell to the right of the ids cell
• Type 30 in the cell to the right of “last-n-days”
• Type ga:visits,ga:pageviews in the cell to the right of “metrics”
• Type ga:date in the cell to the right of “dimensions”
• Give the sheet on which the data will be imported a name in the cell next to “sheet-name”

We’re almost there! Your query configuration should look something like this now:

query1	value1
type
ids	ga:98456354
start-date
end-date
last-n-days	30
metrics	ga:visits,ga:pageviews
dimensions	ga:date
sort
filters
segment
start-index
max-result
sheet-name	Google Analytics Data

Tip: There are a lot of other metrics and dimensions you can make your query with. Also you can segment and filter your data just like you can in Google Analytics. For a full overview of functionality check the site from Google Analytics

Finally, press Google Analytics > Get Data in the top menu and watch the script import Google Analytics data into your spreadsheet!
Once you have closed the popup, you will have a new sheet with the amount of visits and pageviews to your site for the last 30 days. Additionally some extra information is included that tells you when the query was made, how many results there are and whether sampling was applied.

Your imported data from Google Analytics in Google Spreadsheets

That’s it! You have imported data from Google Analytics to Google Spreadsheets. You can now easily create graphs like this:

Import Google Analytics data and make custom dashboards in Google Spreadsheets

Think if all the cool stuff you can do with having your analytics data in Google Docs. As always, I’d love to hear what you have come up with in the comments.

Bonus Step: Automate the Google Analytics import

Using triggers to automate the script

Even though it’s pretty cool that you have set up your spreadsheet to import data by pressing Get Data, it can be very valuable to let your script automatically import data on specific times, or when you open the spreadsheet for example.
Luckily this is very easily done by creating trigger that can run the Get Data command:

• Go to Tools > Script editor again
• Press Resources > Current project's triggers

You can create triggers based on events in your spreadsheet or based on time.
To automatically import data when you open the spreadsheet:

• Press Add a new trigger and select the getData function under Run
• Make sure the two other fields shows from spreadsheet and On open

To automatically import data each day:

• Press Add a new trigger and select the getData function under Run
• Make sure the two other fields shows time-driven and Day timer

An event-driven and a time-driven trigger

Saving these trigger, the spreadsheet will now automatically import your Analytics data every day around midnight and when you open the spreadsheet.
Using these triggers, you can create cool dashboards that are always up to date

Sometimes you want to make a reference to certain worksheets dynamically using the Excel indirect function. For example if you have data in the same format split over multiple worksheets and you want to select data from different sheets dynamically.

In this case, you can use the INDIRECT() function, which is available in both Excel and Google Spreadsheets. I’ll show you how to use it by running through a quick example.

INDIRECT():Returns the reference specified by a text string. References are immediately evaluated to display their contents.

Using Indirect() to dynamically refer to a worksheet

In the following example we want to look at the revenues of a specific product by dynamically referring to the data sheet of each product.
We have a sheet Revenues in which we want to see the revenue for a specific product. Next, we have two sheets with sales and revenue data for two product: Product1 and Product2.

Normally you can only reference to one worksheet

We want sheet Revenues to show the revenues of one specific product and to be able to update the numbers dynamically when we change cell D1.
Unfortunately we can’t use a reference to D1 to specify the sheet we want in our VLOOKUP() function like this:

We can’t use a variable reference to D1 in our VLOOKUP()

For this to work, we have to use the INDIRECT() function. It allows us to use the value of cell D1 for creating a dynamic VLOOKUP referring to ranges on multiple sheets.

Using sheet names as variables with Indirect()

Now you can change cell D1 to “Product2″ and the revenue numbers will dynamically update and get the numbers from the second worksheet.

Indirect() in Excel

So to recap, you can use INDIRECT() to refer to multiple worksheets variably like this:
=INDIRECT("'"&D1&"'!"&"A3:D6")

Indirect() in Google Spreadsheets

You can use INDIRECT() in Google Spreadsheets in a similar way:
=INDIRECT(CONCATENATE(D1,"!A3:D6")

Sometimes you want to remove some number of last letters from a string in your spreadsheet. For this you can use a very handy function: LEFT()

LEFT() returns the first X characters in a string, based on the amount of characters you specify. Like: LEFT(text,num_chars)

Say you want to remove the last 3 characters from a string in cell A1 you can use:
=LEFT(A1,LEN(A1)-3)
Or more generally:
=LEFT(A1,LEN(A1)-[number of characters to remove])

How does this LEFT() function work?

Say for example that in cell A1 you have a string “IncomeMAX” and you want to remove the last three characters (“MAX”):

• First, LEN(A1) returns how long the string in cell A1 is: 8 characters
• Then it subtracts 3, in order to leave out the last 3 characters: 8-3=5
• Then LEFT() makes sure that only the first 5 characters from the left are returned: Income

Suppose you have a number between 1 and 26 in cell A1, you can get the corresponding letter in the alphabet using the following trick:

=mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ";A1;1)
Of course, you can extend and modify this with your own letters to suit your particular needs.

Sometimes you want to have the week number of a certain date in your spreadsheet. In Excel you can use the WEEKNUM() function for this purpose, however in Google Spreadsheets there is no such function built in.

If you want to get a function that does the same as WEEKNUM() in Google Spreadsheets, use the following trick (assuming cell A1 holds a valid date):

WeekNum in Google Spreadsheets

=MID(TEXT(A1,"yyww"), 3, 2)
That’s it! This will output the weeknumber of the date in cell A1.
If you want to learn more about the MID() you can check this article which explains

Occasionally you need to convert a specific string to remove some x number of characters at the beginning of the string. There is a very nifty function you can use for this: RIGHT()

RIGHT() returns the last X characters of a certain string, based on a number of characters you specify. Like: RIGHT(text,num_chars)

Say you want to remove the first 2 characters from a string in cell B1 you can use:
=RIGHT(B1,LEN(B1)-2)
Or in more general terms:
=RIGHT(B1,LEN(B1)-[number of characters to remove])

How does this RIGHT() function work?

Say for example that in cell B1 you have a string “USmarketsize” and you want to remove the first two letters (“US”):

• LEN(B1) returns the length of the string in cell B1: 12 letters
• It then subtracts 2, in order to leave out the first 2 characters: 12-2=10
• Then RIGHT() takes the last 10 letters from the string and returns: marketsize
• In effect, this has removed the first 2 characters of the string

Normally just getting the amount of days between to dates in Excel isn’t enough. At times, you need to get the number of weekdays specifically. For this, there’s a very useful function in both Excel and Google Spreadsheets: =NETWORKDAYS().

Days between two dates

Say you have two dates, cell A1 = 2014/01/14 and cell A2 = 2014/01/30. When you just want the days between two dates, you can easily subtract the earlier day from the later day to get the number of days in between with A2-A1 = 16.

Weekdays between two dates

To get the difference in weekdays only, just use =NETWORKDAYS(A1, A2) = 13. Make sure to put the start date as the first argument, and the end date as the second argument.

Using NETWORKDAYS()

In general, you can use it like:
=NETWORKDAYS(start_date, end_date)

A veces quieres hacer referencia a ciertas hojas de trabajo (worksheets) dinámicamente. Por ejemplo si tienes datos en el mismo formato dividido o repartido en multiples worksheets y quieres seleccionar datos desde diferentes hojas de manera dinámica.
En este caso, puedes usar la función de INDIRECTO(), que está disponible tanto en Excel como Google Spreadsheets. Les mostraré como utilizarlo con un pequeño ejemplo.

INDIRECTO(): Devuelve la referencia especificada en una cadena de texto (text string). Las referencias son evaluadas inmediatamente para mostrar su contenido.

Usando Indirecto() para dinamicamente referirse a una hoja de trabajo (worksheet)

En el siguiente ejemplo queremos buscar el ingreso de un producto especifico, dinámicamente refiriéndonos a la hoja de información de cada uno de los productos.
Tenemos una hoja llamada Ingresos en la que queremos ver el ingreso de un producto en particular. Luego, tenemos dos hojas con información de ventas e ingresos para dos productos: Producto1 y Producto2.

Normalmente solo podrías referirte a una de estas hojas de trabajo.

Queremos que la hoja Ingresos muestre los ingresos de un producto especifico y sea capaz de actualizar los números dinámicamente cuando cambiemos la celda D1.
Desafortunadamente no podemos hacer referencia a la celda D1 para especificar que hoja queremos usar en nuestra función BUSCARV() de esta manera:

No podemos usar una referencia variable para D1 en BUCARV()

Para este trabajo tenemos que utilizar la función INDIRECTO(). Nos permite utilizar el valor que tiene la celda D1 para crear una función BUSCARV() dinámica que refiera a números o rangos que se encuentren en multiples hojas de trabajo.

Utilizando el nombre de hojas de trabajo como variables para la función INDIRECTO()

Ahora puedes cambiar la celda D1 a “Producto2″ y los números de ingreso se mostrarán y actualizarán de manera dinámica y recabaran los números de la hoja de trabajo numero 2.

Indirecto() en Excel

Para recapitular, puedes usar INDIRECTO() para referirte a multiples hojas de trabajo de forma variable de esta manera:
=INDIRECTO("'"&D1&"'!"&"A3:D6")

Indirecto() en Google Spreadsheets

Puedes utilizar INDIRECTO() en Google Spreadsheets de una manera muy similar:
=INDIRECTO(CONCATENAR(D1,"!A3:D6")
Como siempre, puedes dejarme saber si este sistema te funcionó, y hacerme las preguntas que necesites en los comentarios.

When people say Excel workbook they basically just mean to say Excel file. When you open Excel, automatically it will open up an empty workbook for you. A workbook consists of multiple worksheets that can be accessed at the bottom part of your screen.

How to save a workbook

At times you want to save your files, or workbooks, to make sure all your work is safe. To do this there are multiple ways, but the easiest way is to click the blue floppy drive symbol in the top bar.

Saving a workbook in Excel

Alternatively, you can use the keyboard shortcut Ctrl+S to quickly save your file.
If you are working in an existing workbook and you want to save it under a different name, you can use the Save as function. To do this, click File in the top left menu and select Save as.

Saving a workbook under a different name

Now you get to choose between two ways of saving your workbook:

• You can save it on OneDrive which will store your file in the cloud
• Or you can save it on your own harddrive by choosing the Computer option.

Choosing between OneDrive and Computer to save a file

How to open a saved Excel workbook

As said, upon opening Excel an empty workbook will open. If instead on working on a new workbook, you want to continue working in one you saved earlier, you can open your saved workbook by click File and then Open.

Opening a saved workbook

After clicking you’ll get three options to open a file:

• Recent Workbooks: Here you can choose between files you’ve recently made or used.
• OneDrive: Here you can open files that you saves in OneDrive.
• Computer: Here you can browse your computer for a file.

Choosing between different options to open an Excel file

Share a workbook

If you’re like me, you sometimes want other people to help with you on a certain Excel analysis. With Excel 2013 a very convenient share function is built in. Again there are four different ways to share.
Please note that your file has to be saves on OneDrive for this sharing to work properly.

Four ways to share an Excel file

1) Invite people to view or edit your workbook:
Just fill in the email address of the person you want to share with, write a short message and press the Share button. They then will be able to acces your file and work in the same file as you are in the web version of Excel.
After you have shared your document you can see a list of people that have acces to it. If you right-click a person, you can change his permissions or remove him entirely.

Inviting people to your workbook

2) Get a sharing link:
If you want to share your file with more people that you might not have the email address from, you can also create a link where people can access your workbook.
To do this, simply press Get a Sharing Link and press Create Link. You can make a separate link with viewing permissions or editing permissions.
Now you can send this link to anyone you want on Facebook, Twitter, or via email.

Get a link to your file that you can share with other

3) Share on social networks
Excel 2013 even allows you to share your files on your social networks. To do this you first need to make sure you connect your social networks to your Microsoft account.
If you haven’t yet, Click Post to Social Network and then Click here to connect social networks and follow further instructions to set it up.

Setting up a social network with your Microsoft account

Once set up, you might want to press the Refresh button for your linked social accounts to show up.
When done, simply press Post to share your workbook.

Sharing a workbook on Twitter

4) Email:
There are different ways to share a file via email too:

• Send as Attachment: The classical way to share your file is to send it attached in an email.
• Send a Link: If you just share the link everyone will work in the same file online and will always have the latest version.
• Send as PDF/XPS: This will make sure your sheet looks the same on all devices, but it makes it hard for people to make edits.

Sharing a workbook via email

That’s it, now you know everything there is to know about workbooks in Excel!

An Excel worksheet is the sheet with cells on which you make all your work. Using different worksheets you can easily separate different types of analyses in different places so you can easily keep oversight of what you’re doing.

In this tutorial I’ll teach you all there is to know about Excel worksheets using lots of images to make things clear. I also made a video tutorial walking you through all the options.

Switching between worksheets

At the bottom of your workbook you can navigate to different worksheets by clicking the different tabs.

Use the tabs to switch between worksheets

How to add and delete worksheets

While it used to be three, new workbooks in Excel 2013 have only one worksheet included by default. To add extra worksheets simply press the button with the + symbol next to the tabs in the bottom part of your Excel window.

Adding a new worksheet

To delete a worksheet you made, you simply right click the tab and select Delete.

Deleting a worksheet

Renaming worksheets

The default names for worksheets are Sheet1, Sheet2, etc counting upwards. It’s a good habit to change the names of your worksheet to something that expresses what the sheet contains.
So if you have one sheet with Sales data and another one with graphs based on that data, you could call the sheets: data and graphs for example.
To rename a worksheet just right-click on a tab and press Rename.

Renaming worksheets

Moving or copying a worksheet

You can change the order of tabs in two ways. One way is to simply drag a tab to another location.
The other way is to right-click and press Move or Copy. A small screen will popup with some options:

• First of all, you can select the workbook you want to move the sheet to. This can be your current workbook or another one.
• Secondly, you select before which sheet you want to move it.
• Lastly, instead of moving the sheet you can also make a copy of it by selecting the Create a copy checkbox.

Moving a sheet to the end

Hiding and unhiding worksheets

Sometimes you have so many sheets that you loose oversight. In this case you might want to hide some sheets from showing up in the bar with tabs. This can also be a good option when you’re sharing your Excel file with colleagues and you only want to show some specific worksheet.
To hide a worksheet simply right-click on it’s tab and select Hide. To unhide your hidden sheet later, press right-click on any tab and select Unhide. A list with all hidden sheets will pop up. Choose the one you want to unhide and press OK.

How to unhide a hidden worksheet

Changing the color of a worksheet tab

To draw some extra attention to certain tabs, Excel has a neat functionality to give a tab a different color. To do this, simply right-click, go to Tab Color and select your preferred color.

Changing the color of your worksheets tabs

Now you know all there is to know about Excel worksheets!

Cells are the basic building blocks of your Excel work – everything you do in Excel you do in your cells.

In this tutorial I’ll teach you all the basics about cells and ranges in Excel.

Selecting a cell

To select a cell simply left-click it.
Note that cells have names that identify them. If you look at your worksheet, you see that the rows have numbers and the columns have letters assigned to them.
Because of this, each cell can be described by combining the number of the row and letter of the column. For example in the image below, I selected cell B2.

Selecting a cell in Excel

Entering data in a cell

To insert data into a cell, simply double-click it and type the data you want to input. You can input different types of data: numbers, text, and dates.
To insert a date, simply write numbers and separate them with a / or - character (like 03/20/2015).

You can insert numbers, text, and dates into cells

Editing cells

To edit a cell that already contains data you just double-click it again and edit the data inline.
Alternatively, click it and change the value in the Formula bar just above your worksheet followed by pressing Enter.

Editing a cells content in the formula bar

Selecting a range

A range in Excel is a group of more than one cell. There are different ways to select multiple cells.
1) Click a cell and drag your mouse
The easiest way to select a range is to simply click you mouse and drag it. As you drag the mouse pointer, you’ll see your selection increasing.

Selecting range C2 to D5

2) Hold the CTRL key while selecting multiple nonadjacent cells
Dragging your mouse only works for selecting adjacent cells. To select cells that are not next to each other, select a cell and then hold the CTRL key before clicking other cells.

Selecting nonadjacent cells by pressing the CRTL key

Filling a range

A very cool feature in Excel is that it’s easy to fill ranges with data fast without having to enter data in each individual cell. This is called filling a range.
To fill a range, select and hold a cell and move your mouse to a direction. When you release your mouse click, Excel will fill the selected range with the same value that your single cell contained.

Filling a range in Excel

Now your Excel range is filled

Now comes the interesting part: Excel also can fill a range with a pattern. Let’s say you want to make a range with numbers counting upwards from 1.

• Fill three adjacent cells with 1, 2, and 3.
• Select the range and drag your mouse down
• Release your mouse click and watch magic happen. The range will be filled automatically with the pattern you had in your initial cells.

Excel will recognize the pattern in your selection when filling the range

Range is filled with numbers counting upwards

This not only works for numbers but also for dates. Additionally, Excel doesn’t just fill patterns for immediately following numbers and dates, but will pick up on other patterns too.
For example, if you want to fill a range skipping a day, simply enter two dates with a day in between and fill the range. Excel automatically recognizes the date pattern.

Filling a range with dates works as well

Excel picked up the pattern (skipping a day) when filling the range of dates

Moving a range

To move a range just select it and then click the border of the cells. Then drag it to where you want it and release your mouse click.

Click the border of a range to move it

Drag the range to the location you want to move it

Copying and Pasting a range

To copy a range, simply press right-click and then Copy. You can also press CTRL+C on your keyboard.

Right-click and press Copy

Then go to the cell you want to paste your range and right-click and select Paste. Or press CTRL+V on your keyboard.

Click Paste to paste your copied range