I hope everything with you is going fine and you’re staying safe, away from crowded places, and wearing a mask.
We will move on with another part of Excel training. Let’s see where it will take us from here.
To practice Excel you need a data sample. Use this website to generate fake data of any fields and length you wish. https://mockaroo.com/
In the last three parts, we covered all the important and related functions seen on the Home tab of the menu bar. Today we’ll start the Insert Tab. We will go from simple to complicated and not in the order of what is appeared on the menu.
Inserting a Symbol
In Excel, just like Word, you can insert a symbol, which is usually not found anywhere else. For this, you have to click on Insert and click the symbol
You will see this dialogue box. Scroll through and find the symbol you require then click insert. A symbol will be inserted in the selected cell. It’s that easy.
Inserting an Equation
Sometimes we have to write an equation in its actual form. It will be much easy if we use this function and insert an equation. We can find this command here:
You can see a lot of different types of equation formats. Select a format that is close to what you want to insert. For example, I have selected this format:
Now you can click anywhere within the equation and go on changing as you wish. Please remember, the equation will not be inserted within the cell as a value. It will only be inserted as an object, like a picture. You can increase or decrease its size and move it, using your mouse, anywhere on the sheet.
Inserting Word Arts, Header & Footer, and Textbox
By clicking Word Art, you can insert some fancy text onto your sheet, which will be inserted as a picture.
The header and footer portions are the same as we have done in Part-1 of this series. Using this function, you can insert either a header, footer, or both onto your document, which will be repeated on every page of the document. For details on how to do this, please refer to my Excel-Part-1 of this series.
Inserting a textbox is very similar to inserting a word art, but this time, you will be putting all the text within a textbox. This will also be inserted as a picture and you can move it on the sheet.
This is very similar to the Word documents. If you want to link any of the value in a cell to a hyperlink, you can use this command.
Just select a cell, where you want to place a hyperlink. Then click on Insert à Hyperlink. You will see this dialogue box.
Put a web address in the field and click OK. The text or value in that particular cell will be linked to the web address you entered.
Inserting Picture, Clip Art, and Shape
It is relatively easy to manage:
Clicking on each one of them and you can see what they can do.
Inserting Smart Art
This is the one which needs further explanation:
When you click on Smart Art, you will see this dialogue box:
You can browse through them and can see that these are all fancy arts, which you may need to show graphical presentation of any data etc. You can select any one of them and try to play with it to learn. For example, I have picked this one:
It has shown me this:
If you have noticed that the menu bar has also changed according to your selection:
You can insert any text into it and using the menu bar, you can change the design, i.e. color, font style, etc. along with clicking on the Format to change the style of the textboxes.
After doing the preliminary Insert functionalities we come back to the data handling capabilities of Excel within the Insert Tab of the bar.
There are these two commands over here, viz. Pivot Table and Table:
Pivot table we will discuss after we have done the Tables. So hold down to your seats.
You will be astonished to see the “Table” command in Excel, as the whole of the Excel looks like a table, one wonders what's the use of this, or maybe it is like Word. Believe me, it is a very powerful command when used in data handling and it makes the work very easy when you are working on a large amount of data.
When you are working on a huge data, it is advisable to convert the whole data into a table. I will show you how beneficial this is. First, we should learn how to convert our data into a Table.
Select the whole data, by clicking within the data and pressing Ctrl+A.
Then click Table on Insert Menu. You will see a dialogue appearing like this:
You can see here (red arrow) that this is the range of all of your data. Leave the check “My table has a header”. We have discussed in earlier parts that how important it is to have a header row in any of your data. Excel will take the first row as a header row.
Just click OK, and all of your data is now converted to a table, it will look like this now:
You can click here (red arrow) and change the name of this table, or in other words, give a name to your data, which is now arranged as a table. I will change it to “batch2018”.
You can click on the design and select any preformed design or can make one of your own. This is not much important and is just for viewing aesthetics.
The “Resize Table” tab you are seeing here doesn’t mean to increase or decrease the width or height of the table. It is for to include more columns or rows or exclude them from your table.
Now your data has a name in a table. What are the benefits of converting your data to a table:
Drop-down filters put on every column
Column Headings are Always Visible
Automatic Totals – Even When You Filter!
Banded Rows – to Make Your Data Easier to Read
Dynamic Named Range is created whenever you create a Table (more on this later). Remember as before, we said now your data has a name. This name you can use to refer to your table or any filed of your table.
Using formulas become easy (more on this in detail later)
Tables automatically expand when you enter data in the next row/column (more in next paragraphs)
Use a Form to speed up data entry in Excel (wait for more)
Graphs/charts will automatically update to include all of your data
Formatting will be consistent for all records within a Table
You can revert a Table to a normal data range (and still keep your formatting and layout!)
There may be more benefits, suffice here is to say that data handling becomes way easier when you are using a table rather than plain ranges. Let me explain certain things.
1. Drop-down filters put on every column
As you can see in this image that the filter list, we have talked about in the previous part, has already been added to each column.
If you want to filter data, you just select one of these small arrows and filter according to your needs.
2. Column Headings are Always Visible
If you scroll down in a table, you will see that the headings will not disappear with the scroll and will always be visible, even if you have not enabled freeze panes (see part 1 for this).
3. Automatic Totals – Even When You Filter!
If you wish, you can enable an extra row of “Total” at the end of the table.
Just right click on any cell of the table, select Table, and select Total.
This total consists of Subtotal, which you can see by selecting the total cell and pressing F2, which will show you the formula there.
If you want to display this total row, just repeat the process.
4. Banded Rows – to Make Your Data Easier to Read
As you can see, from the above images that the table is shown in banded rows with alternate colors to make it easy to read the data, you can always change the style and color of the bands.
5. Dynamic Named Range & Using formulas become easy
First of all your data has a name now. Remember I named my data as “batch2018”. Now I can refer to my data anywhere in my Excel sheets in the same file with just this name. This is called Dynamic Named Range. This is not only for the table but for every field of the data. For example, if I want to refer Email column of my data, I can use the heading name and that will be sufficient.
Suppose you want to refer your formula to your data’s email column, you have to start with the “-“ sign and then write batch2018 and it will show you the data name:
If you enter “[“ (bracket without quotes), you can see all the available fields in your data:
You can select any one which you want and calculate with that field or whatever else you want to do with that field.
Although we will come to it later, you can see the name of your data in Dynamic Named Range here:
As we will see later, how feasible this Name Range thing is when handling huge data:
6. Tables automatically expand
Yes, it’s true! Try putting a new column at the end of your Table columns:
As you can see that we are entering a new column by the name of City. It looks like it is not included within the Table, but wait. Just press Enter and see the magic.
Not only this, but this range will be automatically entered into the Table range. To make sure it does, you can go to Design and click on Resize Table. It will show you the new range of the Table:
It doesn’t end on the columns, Table automatically expands as you enter new rows. You will not be bothered that as your data increases, you have to get that new data into other formulas you already have elsewhere on any of the sheets. It doesn’t sound much this time, but believe me when you are dealing with complex data and you have a lot of formulas connecting to your data, you are always worried whenever your data changes about how will you reflect this change everywhere on other sheets. If you are using Table, you don’t have to worry about this. For me, this is the single most important benefit of the table for which it is worth it.
I am putting another piece of data, and as you can see I am writing out of the table:
But as soon as I finished entering the first cell of this row and press enter, this row will automatically be included within my Table.
7. Use a Form to speed up data entry in Excel
Now, this is what I called technology at its best. We will cover this topic along with the Graphs and charts in the next part of this series.
You can always convert your data back to Normal Ranges as before, but that will not be a good idea.
Under the Design tab, click on Convert to Range and your Table will disappear and all of your data will be normal values as you have entered before. If you want to convert it to the Table, just repeat the process as I have discussed above.
In the next part, we will deal with how to make use of a form for data entry, which will make your work so easy. I will also discuss the making of Graphs and charts and the most important part of data processing the “Pivot Table”. Do not forget to visit us again.
Questions? Go ahead and ask.