If you want to be a better Excel user, start by taking it one step at a time. This will help you move from a novice to an expert. At your own pace, you can improve your skills.
In this preview article, we will be discussing how to approach worksheets and workbooks. We will not be covering every possible function that can be used in Excel but we will cover enough that you can start using them in your own work.
Basic Definitions
Excel is a software program that helps people manage their data. It has many features, including the ability to create charts and graphs. ..
Workbooks vs. Worksheets
When you first create an Excel document, many versions of Excel will automatically include three tabs, each with its own blank worksheet. If your version of Excel doesn’t do that, don’t worry; we will learn how to create them. The first tab is called the Workbook Tab and it contains all the data for your workbook. The second tab is called the Data Tab and it contains all the formulas used in your workbook. The last tab is called the References Tab and it contains all the references to other worksheets in your workbook.
The Worksheets are the actual parts where you enter the data. If it is easier to think of it visually, think of the worksheets as those tabs. You can add tabs or delete tabs by right-clicking and choosing the delete option. The worksheets are the actual spreadsheets with which we work and they are housed in the workbook file.
The Ribbon
The Ribbon is a row of icons that are represented visually on the Excel application. This makes it easy to find what you’re looking for and can help you get things done quickly. ..
There are different ribbon buttons depending on which section/group you choose from the top menu options (i.e. Home, Insert, Data, Review, etc.) and the visual options presented will relate to those groupings.
Excel Shortcuts
Some Excel shortcuts are learned by seeing the shortcut menus in older versions of the Excel application and then trying them out for yourself.
The website of the Excel developers has a list of shortcuts that you can use if you don’t have access to the shortcuts in your version of Excel.
Formulas vs. Functions
The SUM() function is a built-in Excel function that calculates the sum of numbers in different cells of a spreadsheet.
In this article, we will explore some of the more important functions of the Google Chrome browser. These include features like bookmarks, passwords, and history. We will also look at how to use these functions in order to protect yourself from online threats and protect your privacy.
Formula Bar
The formula bar is an area that appears below the Ribbon. You can enter formulas and data in the cell, and it will also appear in the formula bar if you have your mouse on that cell. ..
When we reference the formula bar, we are indicating that we should type the formula in that spot while having the appropriate cell selected and then press enter.
Creating & Formatting a Worksheet Example
In this article, we will show you how to use Excel Worksheet to do some basic math. We will start with simple calculations and work our way up.
The First Workbook
In the New Workbook window, you will be asked to provide a name for your new workbook. This is important, as the name will be used to identify your workbook in future. Enter “Workbook1” into the text field and click on OK. Now that your new workbook has been created, it is time to start working on its contents. To do this, you will need to open it up and select the Tables tab. There you will find a list of all of the tables in your workbook. You can drag and drop any of these tables into your workbook, or simply select them and click on Add Table. Once you have added a table, click on OK to close the Workbook window and return to Excel’s main screen.
Open new Excel workbook from any location. ..
When you click on New, you are more likely to get some example templates. The templates themselves may vary between versions of Excel, but you should get some sort of selection.
One way to learn how to use Excel is by playing with templates and formulas. For our article, we are starting with a blank document and playing around with data and formulas.
So, if you want to use a blank document, select the “Blank Document” option on the interface. This will vary depending on the version of Excel you are using, but it should be similar enough to understand. Later we will also download another sample Excel sheet. ..
Inserting the Data
There are many different ways to get data into your spreadsheet. One way is to simply type what you want where you want it. Choose the particular cell and just start typing.
Another way to do this is to copy the data and then paste it into a spreadsheet. Granted, if you are copying data that is not in a table format it can get a little interesting as to where it lands in your document. But fortunately we can always edit the document and recopy and paste elsewhere, as needed.
The copy/paste method can be used to paste a portion of this article into your spreadsheet.
After selecting the desired portion of the article and copying it, go to your spreadsheet and click on the desired cell where you want to start the paste. The method shown above is using the right-click menu and then selecting “Paste” in the form of the icon.
When pasting data into an Excel sheet, it is possible to get an error if the data is not formatted correctly. The error warning (above) helps to point you in the right direction to fix the problem. ..
When pasting data into Excel, it can be difficult to ensure that the data is properly interpreted. In our example, I copied the first two paragraphs of this section and Excel presented it in two rows. However, since there was a space between the paragraphs, Excel also reproduced that space (with a blank row). If you are copying a table, Excel does an even better job of reproducing it in the sheet. ..
You can paste by pressing the Paste button in the Ribbon. This is helpful for visual people. It is shown in the image below. ..
Some versions of Excel (especially the older versions) allow you to import data (which works best with similar files or CSV – comma-separated values – files). Some newer versions of Excel do not have that option but you can still open the other file (the one that you want to import), use a select all and then copy and paste it into your Excel spreadsheet.
When importing data into Excel, you may find that the File menu is more accessible. Simply click the arrow in the top left to return back to your worksheet.
Hyperlinking
Hyperlinking is a simple process that can be done in most versions of Excel. The hyperlink button is located under the Insert menu in newer versions, or can be accessed via a shortcut. ..
Formatting Data (Example: Numbers and Dates)
Numbers can be helpful in a variety of ways, but they can also be formatted in a way that makes them more like text. For example, numbers can automatically fall into a general text format, but we want them to behave as numbers.
Date formats: We may want to format our dates to ensure that they all appear consistent. For example, 20200101 or 01/01/20.
You can choose to have your data formatted in a table, graph, or text file. ..
When you access a number format, you have several options. These options are available when you use the right-click method. When you use the Ribbon, your options are right there in the Ribbon. It all depends on which is easier for you. ..
If you are a new user of Excel, the right-click method may be easier to understand. If you are more visual, the Ribbon method may make more sense. Both methods provide you with number formatting options.
If you type anything that resembles a date, the newer versions of Excel are nice enough to reflect that in the Ribbon as shown in the image below. ..
You can choose from a variety of date formats when creating a Ribbon report. For example, you can choose a short date or a long date. Try it out and see what results you get. ..
Presentation Formatting (Example: Aligning Text)
The Ribbon is a tool that can be accessed to align your data.
The alignment of the text in Ribbon options is on the second row. You can choose other alignment options in the Ribbon.
In a word processing application, alignment of text is similar to aligning numbers. When text is aligned left, the text looks better because it is on the left side of the screen. When text is aligned right, the text looks better because it is on the right side of the screen.
Columns & Rows
Columns and rows can be helpful in organizing content on a page, but it can be difficult to get the hang of it. Fortunately, once you understand how to work with them, it is fairly easy to do. ..
To add or delete rows or columns in a table, you first need to select the table. Then, use the selection tools on the toolbar to make your selection. Finally, click the right mouse button and choose “insert” or “delete.” ..
Deleting rows in a spreadsheet can be a helpful way to clean up your data. ..
We need to select the rows. Click on the row number (to the left of the top left cell) and drag downward with your mouse to the bottom row that you want to delete. In this case, we are selecting three rows.
Excel deletes the rows in a worksheet by clicking on Delete Rows. ..
To insert a row in Excel, follow these steps:
- Click on the Insert tab.
- On the Insert tab, click on the Row button.
- Type the name of the column you want to insert into and click on the OK button.
- Excel will insert the row into that column automatically.
To start the process, click on the row number that you want to be below the new row. This tells Excel to select the entire row for you. From the spot where you are, Excel will insert the row above that. You do so by right-clicking and choosing Insert Rows.
As you can see, we inserted 10 into row 10. Then, after selecting 10 (row 10), right-clicking, and choosing Insert Rows, the number 10 went down one row.
This demonstrates how the inserted row was placed above the selected row. To try it for yourself, go ahead and try it for yourself.
If you need more than one row, you can select more than one row and this tells Excel how many you want and that quantity will be inserted above the row number selected.
This is a visual representation of how the ten went down three rows. The number of rows inserted is shown in parentheses.
To insert a column, select the row you want to insert it into and then use the arrow keys to move the cursor to the column you want to insert it into. Then press Enter. To delete a column, select the column you want to delete and then use the arrow keys to move the cursor to the row below it. Then press Enter. ..
Filters & Duplicates
When working with large amounts of data, it can be helpful to have a few tricks up our sleeves in order to make the data more easily accessible. ..
To filter data in Excel, you can use the “Filter” button.
Microsoft has a downloadable Excel spreadsheet that can be used to test the proposed algorithm. The spreadsheet includes data from a real-world experiment. ..
The document contains a lot of data, isn’t it? Let’s take a look at the volume of data. Quite a bit, I think.
You can filter the data by country, by type of data (included in the ribbon), or by date.
Option 1: Choose Germany as the selected country. Option 2: Choose any other country.
When you select the text filters option, you have several options, including the “Text Filters” option that we will be using. You can sort ascending or descending.
It makes sense why Excel combines these in the Ribbon since all of these options appear in the pull-down list. We will be selecting the “Equals…” under the “Text Filters.”
After selecting what we want to do (in this case Filter), let’s provide the information/criteria. We would like to see all the data from Germany so that is what we type in the box. Then, click “OK.” ..
You will notice that now we only see data from Germany. The data has been filtered. The other data is still there. It is just hidden from view. There will come a time when you want to discontinue the filter and see all of the data. Simply return to the pull-down and choose to clear the filter, as shown in the below image.
Duplicate data can be a problem when you’re trying to analyze or compare data sets. For example, if you have financial data that includes duplicate records, it can be difficult to understand what’s happening in the data set. ..
This data set has some data that is repeated (shown in yellow). The highlighted data is the most important information.
Once you have selected the row that represents the duplicate data, click on the “Duplicate” button. This will show you a list of all of the duplicates that are in this row. To remove a duplicate, simply select it and click on the “Delete” button. ..
The “Data” tab or section on the Ribbon can be accessed by clicking the button on the Ribbon that says “Remove Duplicates.” Clicking this will remove any duplicates from your data.
The dialog box in the image asks you which column to look at when identifying duplicate data.
If you have a table with duplicate rows, you can remove the duplicates by selecting the columns that contain the duplicate data. ..
If you want to select all columns in a worksheet, you can use the “all columns” option in the selection dialog box. ..
After you click “OK” on the dialog box, you will see the result and in this case, three rows were identified as matching and two of them were removed.
The data in the table matches the data we started with before we went through the addition and removal of duplicates.
You can use the Ribbon to access different tools and buttons on your computer. You can also duplicate the Excel file if you want to keep the original form.
I duplicated the tab with all of the financial data, and then I had two versions to play with at will. You can try this by using the right-click on the tab and choosing “Duplicate.” ..
Conditional Formatting
Creating a Workbook is an important part of any business. It can help you track your progress and keep track of your work. You can also use it to study for exams or to improve your skills.
If you want to highlight data that is over $25,000 in Gross Sales, conditional formatting can be a helpful way to do that. ..
In order to evaluate the cells, we first have to highlight the group of cells that we want evaluated. Next, keep in mind that we do not want to highlight the entire column or row. We only want to highlight just the cells that we want evaluated. Otherwise, the other cells (like headings) will also be evaluated and you would be surprised what Excel does with those headings (as an example).
We can now conditional format cells to show different colors based on whether or not they are in a certain section or group.
To highlight cells that are greater than $25,000 in a spreadsheet, you can use conditional formatting. When you click on “Conditional Formatting” in the Ribbon, you have some options. In this case we want to highlight the cells that are greater than $25,000 so we make our selection with the “Highlight Cells” button as shown in the image below. ..
Now we will see a dialog box and you can type the value in the box. You type 25000. You don’t have to worry about commas or anything and in fact, it works better if you just type in the raw number. ..
After we click “OK,” the fields will be automatically colored based on our choice (to the right) in our “Greater Than” above dialog box. In this case, “Light Red Fill with Dark Red Text).” We could have chosen a different display option as well. ..
This conditional formatting is a great way to see, at a glance, data that is essential for one project or another. In this case, we could see the “Segments” (as they are referred to in the Excel Example file) that have been able to exceed $25,000 in Gross Sales.
Working With Formulas and Functions
The basic functions of Excel are listed on the “Function/Functions” menu. This menu is located at the far left of the Excel window. If you want to see a listing of all the functions, click on the “Insert” menu and then select “Function/Functions.”
You can use the “Insert Function” button on the Excel Ribbon to insert a function into your spreadsheet. This button is also available by typing in the formula bar, starting with an equals sign and then starting to type the desired function. You can scroll through the functions to get a sort of idea of what you can use in your formulas. ..
If you’re looking for a way to organize your life, try out a personal assistant service. These services offer a variety of features, like scheduling appointments and reminders, sending emails and texts, and keeping track of your to-do list. You can also find assistants that specialize in certain areas of your life, like finances or cooking. ..
In this article, we will be discussing the use of formulas in Excel. We will be using some of these functions in the examples of the formulas we discuss. ..
The Equals = Sign
The equals sign ( = ) is very important in Excel. It plays an essential role in the cases of formulas. Basically, you don’t have a formula without preceding it with an equals sign. And without the formula, it is simply the data (or text) you have entered in that cell.
Excel is a powerful tool, but before you ask it to do something for you, make sure you type an equals sign (=) in the cell. ..
If you include a $ sign, that tells Excel not to move the formula. Normally, the auto adjustment of formulas (using what is called relative cell references), to changes in the worksheet, is a helpful thing but sometimes you may not want it and with that $ sign, you are able to tell Excel that. You simply insert the $ in front of the letter and number of the cell reference. ..
If you want to know the main cell reference for D25 in a table, you can use the following formula: D25 = (Cells(1, 1), Cells(2, 2))
The Awesome Ampersand » &
To create a column for the full name, you can use the & symbol to combine the first and last names in a column.
To combine your first and last names in Excel, you can use the & symbol. First, type your first name in A1 and your last name in B1. Then, to combine them, click your mouse on the C1 cell and type this formula: =A1 & " " & B1. Please only use the part in italics and not any of the rest of it (like not using the period). ..
What do you see in C1? You should see your full name complete with a space between your first and last names, as would be normal in typing your full name. The & “ “ & portion of the formula is what produced that space. If you had not included “ “ you would have had your first name and last name without a space between them (go ahead and try it if you want to see the result).
The & symbol can be used to connect two words together, as in “and then,” “and then,” and so on.
SUM() Function
The SUM() function is a handy function that can be used to add up the numbers you tell Excel to include. You can do this in a couple of different ways by using the SUM() function and its various arguments.
We started by typing in some numbers so we had some data to work with in the use of the function. We simply used 1, 2, 3, 4, 5 and started in A1 and typed in each cell going downward toward A5. ..
Now, to use the SUM() function, start by clicking in the desired cell, in this case we used A6, and typing =SUM(in the formula bar. In this example, stop when you get to the first “(.” Now, click in A1 (the top-most cell) and drag your mouse to A5 (or the bottom-most cell you want to include) and then return to the formula bar and type the closing “).”
If you need to enter a number manually, you can do so by using the “=” sign in the formula bar. This is especially helpful if you have a lot of numbers and scrolling to grab them is difficult. To start this method, type “=SUM(.” ..
In this example, the journalist is reporting on a study that found that the average person’s brain size is smaller than it was 20 years ago. They use the terms “average” and “smaller” to describe how people’s brains have changed over time.
AVERAGE() Function
The average of a group of numbers is simply the sum of the individual numbers in that group divided by the number of numbers in that group. ..
We start by selecting the cell we want to use for the result (in this case A6) and then start typing with an equals sign ( = ) and the word AVERAGE.
Ensure that you have an opening parenthesis in your formula before we add our cell range.Otherwise, you will receive an error.
To find the average of a range of cells, you can use the “=AVERAGE(…” command. To select the cells in the range you want to use, use the “Select” button on the toolbar. ..
To keep your data organized and easy to access, try using a keyboard instead of a mouse.
The average of the five numbers is the result of the SUM() function.
COUNTIF() Function
In order to count how many times a certain number appears in a data set, we first need to prepare our file for this function. Remove any formulas that you may have in A6. Next, either copy A1 through A5 and paste starting in A6 or simply type the same numbers in the cells going downward starting with A6 and the value of 1 and then A7 with 2, etc.
In A11, we are going to start by typing the =COUNTIF(.”) function. This will return a list of cells that have a certain value. We will then select these cells using the =COUNTIF(.) function.
To get the same result as the built-in function COUNTIF, you must type or select “COUNTIF.” ..
Before we do anything with our other functions, we need to answer the question of criteria. We will type “)” and after that, “,” to create a comma.
The “criteria” is what tells Excel what to count. In this case, we typed a comma and then a “5” and then the closing parenthesis to obtain the count of the number of fives (5) that appear in the list of numbers. That result would be two (2) as there are two occurrences. ..
CONCAT or CONCANTENATE() Function
Concatenating cells in a table can be used to combine data from different cells in the table. To do this, use the CONCAT() function. Try this example: SELECT COUNT(*) FROM Customers;
John Smith and Jane Doe are both John Smith.
You can use the ampersand (&) to represent a logical connection between two items.
To concatenate two cells, use the CONCAT function.
Remember to type “CONCA” (or shorter) when you want to enter the correct function for CONCANTENATE.
Don’t be surprised if you prefer to use the merge method with the ampersand (&) instead of CONCAT(). That is normal.
If/Then Formulas
Next, we need to add the following code to our if statement: If Discount > 0 Then Discount = Discount + 1 Else Discount = 0
Now, we type in the formula. In this case, we type it in F2 and it is “=IF(E2>25000, “DISCOUNT 2”). This fulfills what the formula is looking for with a test (E2 greater than 25k) and then a result if the number in E2 passes that test (“DISCOUNT 2”).
F2: Copy and paste the following into the F column. The F-word is trending on social media. Now, copy and paste in the cells that follow it in the F column. F2: Copy and paste the following into the F column. The F-word is trending on social media. ..
The formula will automatically adjust for each cell (relative cell referencing), with a reference to the appropriate cell. Remember that if you do not want it to automatically adjust, you can precede the cell alpha with a $ sign as well as the number, like A1 is $A$1.
You can see in the image above that “DISCOUNT 2” appears in all of the cells in the F2 column. This is because the formula tells it to look at the E2 cell (represented by $E$2) and no relative cells. So, when the formula is copied to the next cell (i.e. F3) it is still looking at the E2 cell because of the dollar signs.
FALSE, “text should be in quotes”
Managing Your Excel Projects
You can use Excel to create different versions of your sheet, so that you have a variety of content in one file.
You can rename your tabs and use date codes to let you know which versions are the newest (or oldest). This is just one example of how you can use those tabs to your advantage in managing your Excel projects.
Renaming your tabs in Excel:
- Click on the tab you want to rename.
- In the resulting dialog, select the new name for the tab.
- Click on OK to rename the tab.
If you do not receive that response, please let me know and I will try to help you out. In Excel, there are tabs that you can rename. You can right-click on the tab and get an option to rename it in the earlier versions of Excel, as well, and sometimes simply type right in the tab.
Excel provides you with so many opportunities in your journey in learning how to use Excel. Now it is time to go out and use it! Have fun!
