Learn how to make a spreadsheet in Excel and make your work routine easier!

Usually, the most used resources for everyday office tasks are the solutions proposed by Microsoft as an organizational standard.

Among its portfolio of tools, the traditional Office package offers MSO Excel, the most used spreadsheet management and manipulation software in the world.

More than a common software, Excel brings a built-in programming language (Visual Basic - VBA), in addition to several other utilities (such as Macros, for example).

In this article we will cover the basic functionality of this software in the following topics:

• What is a spreadsheet?
• How to create a spreadsheet in Excel?
• How to associate spreadsheets in Excel?

So let's start our learning!

What is a spreadsheet?

Following a table structure, a spreadsheet is a set of elements ordered by rows and columns.

They can also be extremely useful and practical to perform mathematical operations of your daily life, as using their internal elements they can perform numerous matrix calculations with important results.

In Excel, spreadsheets are not necessarily files, but rather tabs for each file executed in the program.

Are you tired of missing out on incredible career opportunities because you don't know about the main Excel tools?

So you are in the right place! In the Excel for Beginners course, you will learn to work with data in an agile way.

In addition, the course is available for FREE, just click on the banner button below and embark on this journey of knowledge!

How to create a spreadsheet in Excel?

To create new spreadsheets in Excel, just insert a new tab. You can choose the option to use the shortcut Shift + F11 or click on the “+” icon next to the nomenclature of the sheets, as can be seen in the image.

As mentioned before, confusion regarding terminology is very common. The Excel file is called Workbook. The working region in matrix format which is actually called a tab or spreadsheet.

How to navigate between sheets?

We usually have several databases or different information, which should be placed in different tabs or spreadsheets to facilitate the organization.

To navigate between tabs or sheets, you can left-click on each one of them or use the shortcut CTRL + PgDn to go forward or CTRL + PgUp to go back.

This type of tool is very useful for people who work with periodic controls (daily, weekly, monthly). Let's take a step-by-step look at how to work with this type of situation in Excel through an example.

Example of using an Excel spreadsheet

Let's imagine that a metals company manufactures inputs for small-scale constructions and has three work shifts.

In this context, the general manager intends to gather production data for all lines and shifts. Let's then create spreadsheets in Excel to monitor this fact.

Let's create and rename 6 sheets to start the process.

To rename spreadsheets in Excel, just click with the right mouse button (or double-click with the left button) on the title of each one, with the names: "Panel", "Production Summary", "Shift 1", "Shift 2", "Shift 3" and "Graphics", respectively, as shown below:

When working with multiple tabs/sheets, one option to separate related areas is to place different colors on the tabs by right-clicking on the tab, then clicking Tab Color and choosing different colors for each of them.

Another important tip when it comes to data is the use of fluid software, so in Excel it is possible to use graphical interactions to perform complex tasks in the background.

This allows the platform, in general, to be more intuitive to the employee who is using it.

For this, we will use the internal Hyperlinks function in the spreadsheet. In this way, we are going to apply a more agile and efficient way of navigating between our spreadsheets.

The following layout was created to facilitate navigation in this workbook. Look:

Hyperlinks are most often assigned to three types of references: selecting cells, shapes and images. Let's now work on these three reference types.

First, let's insert a shape and an image into our spreadsheet. We'll follow the procedure: Insert > Illustrations > Shapes > Rounded corner rectangle.

Place the shape in the field delimited by dashed lines and adjust it accordingly.

The shape can be changed using the Drawing Tools Tab > Format > Shape Styles. In this tab we can use predefined shapes or create a shape yourself by selecting the fill, outline and shape effect.

After applying a formatting, just add the name “Production Summary” to the form. This can be done by right clicking > edit text or just left clicking on the shape and typing in the name. Other text placement adjustments can be made on the home page.

Lastly, let's insert an image into the delimited field. To do this, just click on Insert > Image > and select the image. As the shape adjustment was made, the image will be adjusted to the outline delimited by the dashed lines.

To insert hyperlinks for ("Shift 1", "Shift 2", "Shift 3", "Prod. Summary" and "Graphics"), we can:

- Select desired item and use shortcut Ctrl+K;

- Select desired item > Insert tab > Link.

We must then right-click on the desired item and select Link:

After opening the window, select “Place in this document” > assign reference to the respective names.

Now, just repeat this procedure for all references.

In order to have a complete navigation through our spreadsheets, we must create hyperlinks in the other tabs that return to the Panel, following the same pattern.

How to associate spreadsheets in Excel?

After inserting your database in the shift spreadsheets, it is interesting to carry out the operations of total produced, usable percentage and useful quantity for each cell in order to fill your table.

Once this is done, we must also go to the production summary sheet and create formulas from the shift sheets, in order to actually summarize the total production in a single sheet.

For example, the value of nails produced in row 1 of the production summary spreadsheet will be the sum of the value of nails produced in row 1 of each of the shift spreadsheets, and so on for the information relevant to each row and column of your control sheet.

The purpose of the “Graphics” spreadsheet, as the name implies, is to bring a presentation of management graphs and dashboards for better visualization of the results of this company.

One of Excel's most used and useful functions is pivot tables. They allow you to manage, filter and organize data quickly and efficiently, so we created our Pivot Table Ebook to teach you a little more about this function.

Learn how to create, update and edit pivot tables in Excel. By downloading our eBook, you will learn the step by step of this function to use in various everyday situations

Download it now!