Tag Archives: Excel

How to Hide Columns in Excel

How to Hide Columns in Excel

Did you know that there are several ways you can learn how to hide columns in Excel? While most people know about this Microsoft software feature, there are a couple of things that you might not be aware of. For example, you can hide or unhide more than one column at a time. If the columns or rows are contiguous, you can also take advantage of the grouping tool within Excel. To save you some time, we have outlined several ways that you can hide and unhide your Excel columns and rows. Read on to learn more.

Columns, rows, and cells are the backbones of Excel spreadsheets. As the main locations where you enter all data, they can hold countless amounts of information. Sometimes though, it’s not necessary to see everything all at once.  We will show you a few ways to hide and then unhide columns on your Excel spreadsheet. Remember that everything that follows works for rows too.

How to Hide Columns in Excel

Simple Hiding of Columns and Rows

The most common way on how to hide columns in excel is to highlight the column you want to hide and right-click with your mouse.

The pop-up menu will have an option to Hide near the bottom. Simply click it, and your column disappears.

To get the column back, you will need to highlight the columns on either side of the one you hid. For example, if you hid column D, then select columns C and E, right-click and choose Unhide from the bottom of the pop-up.

Column D reappears.

The keyboard shortcuts for the above are just as simple, and we will include the ones in rows too.

Hide Column – Control + 0

Unhide Column – Shift + Control + 0

Hide Row – Control + 9

Unhide Row – Shift + Control + 9

Using Go To

If you have several columns and rows hidden in a spreadsheet but want to be selective about revealing one of those, the Go To command may be your best option.

Assume you have columns C, D, F, H, and K hidden along with rows 3, 5, and 9.

Select Control + G from the keyboard.

The Go To window will pop-up. In the box below Reference, type in one of the destination cells from the column you want to unhide. For our example, type in D11 and click OK.

D11 is now the active cell. You will not see the highlight, but you’ll know you’re there from the cell identification box just to the left of the formula bar.

Select Shift + Control + 0.

Column D should now appear while keeping the other columns and rows hidden.

Group and Ungroup Columns and Rows

So far, we’ve only addressed hiding and unhiding single columns. What if you need to group columns?

Excel has a group feature to help pull together columns (and rows).

First, select the group of columns you want to group and hide. For our example highlight columns B, C, and D.

From there, go to your Data tab and from the Group and Outline box, choose Group. To simplify, your steps should be:

Data > Group & Outline > Group

Once you’ve finished, a line will show up over the B, C, and D columns, indicating that these three columns are grouped.

To collapse this group of columns, click the box to the right of the line. This will hide the three grouped columns.

To unhide, click the box with the plus sign. This is above the column after the last one hidden, in our example column E.

To ungroup the cells, retrace your steps and follow the below:

Data > Group & Outline > Ungroup

Width Adjustment Method

The final way to hide a column or row in Excel is by adjusting the column width using the headings.

In each new spreadsheet, columns start at a fixed width; rows a fixed height. There are two ways to change these.

First, highlight a column and then right click with your mouse. In the pop-up, choose column width and manually enter 0. For rows, you would select row height.

The column is hidden.

You can perform this task with multiple columns or rows. For example, while holding down Control, highlight columns C, D, and F and right-click one of the highlighted cells. Choose column width, type in 0 and click OK.

All three columns are now hidden.

To unhide, simply follow one of the methods outlined above.

The other variation of this technique is to move your mouse between the column or row headings until the cursor changes to a two-sided arrow. From there, hold down with your mouse, and you can slide the width larger or smaller for the column to the left of the cursor.

For rows, you can increase or decrease the height of the row above the cursor.

Troubleshooting

In all instances above, you are only changing the appearance or size of the column (or row) and not the actual values or formulas in a column’s cells. As such, you will run into very few issues when hiding or un-hiding columns.

One thing of note, you can copy a hidden column and paste or insert it elsewhere, even as it remains hidden. This can be confusing if you have a large number of columns or rows hidden in your spreadsheet.

For example, if columns B and D are visible and column C is hidden, if you select B and D to copy and paste, you are also highlighting column C.

When you paste the columns, column C will paste as well, while remaining hidden. This will also work with the cut and insert commands and if columns are grouped.

Finally, always remember that there are different versions of Excel and with each one, a slight variation in the way the information appears or functions.  This is also true with Excel on a PC and a Mac.

How to Hide Columns in Excel: Conclusion

The ability to control how you work and view your data within a spreadsheet is one of the key functions in Excel. When the need presents itself to hide columns or rows, the multiple ways to accomplish the task is further proof of Excel’s flexibility.

Whether it’s one column or ten, Excel allows you to choose the best way to present your data.

How to Delete Duplicates in Excel

How to Delete Duplicates in Excel: An Easy Guide

How to Delete Duplicates in Excel

One of the main functions of a Microsoft Excel spreadsheet is that it organizes any data set into manageable rows and columns which can be easily viewed, searched, and arranged. Here we will show you how to delete duplicates in excel the easy way.

Because of the way Excel organizes this data, it should be easy to manipulate, whether this means changing your ordering principles (alphabetical, according to date, according to amount), searching for particular entries, and adding or deleting information.

This tutorial will focus on how to identify and delete duplicate entries which might exist within an Excel spreadsheet that you’ve created.

Reasons For Deleting Duplicates

Anyone familiar with Microsoft Excel knows that there are a number of reasons you might want to search for and delete duplicates. Perhaps you’ve accidentally copied a row when working within an Excel spreadsheet. It’s also possible that you or someone else may have accidentally entered the same information twice on the same spreadsheet.

In any case, where you suspect there may be duplicate information, there is a better option than searching through the entire document using only your eyes and trusting you’ll find your duplicates. Microsoft Excel has made it easy to press a series of buttons which will tell the program to automatically search for, identify, and delete any duplicates you may have added by mistake.

In this tutorial, we’ll cover:

  • How to check for duplicates
  • How to automatically delete duplicates
  • Advanced filtering
  • An alternate method for older versions of Excel

Checking for Duplicates

check duplicates how to delete duplicates in excel

The first step to deleting duplicates is to identify them. Microsoft Excel can do this relatively easily. To identify duplicates, follow the steps listed below:

  1. Once you’re in the folder which contains your file, double-click on your Excel document file. If you’re looking for a document you’ve used recently, you can also open the document from the “Recent” section of the Open tab.
  2. Once your file is open, select the range of cells you wish to test for duplicates. You can do this easily by clicking on any cell and then pressing Ctrl-A (Select All).
  3. Once you’ve selected the range, click on the Home tab. Within the Home tab, select Conditional Formatting. Once you’ve done this, highlight Cells Rules, and then within that, select Duplicate Values.
  4. Once you’ve selected Duplicate Values, a dialog box should open in the middle of your screen. Within this dialog box, click OK. This box will also inform you as to what color the program will use to identify your duplicate values (eg. Light red fill with dark red text).
  5. The duplicate values within your list should now be identified in the color stated.

How to Delete Duplicates in Excel

data tools how to delete duplicates in excel

  1. Open your Excel document file by double-clicking on the file name. Alternately, if you’ve used the document recently, you can open an existing document from the “Recent” section of the Open tab.
  2. Once your file is open, select the range that you want to delete duplicates from. You can do this by clicking the entry that is in the top left corner of your chosen range. Once you’ve clicked on this, hold down the up arrow and Shift button. While you’re holding these buttons, click on the entry that is in the bottom right corner of your chosen range.
  3. Your chosen range should now be highlighted.
  4. Click the Data tab, which is a tab on the left side of the green toolbar at the top of the Excel window.
  5. Within the Data tab, choose Data Tools, and then Remove Duplicates. Once you’ve chosen Remove Duplicates, a dialog box will appear.
  6. Within this dialog box, leave all check-boxes checked and click OK. If you do not want to remove duplicates from all of your columns, deselect the columns you’d like to the program to leave alone before clicking OK.
  7. All of your duplicates should now be deleted from your table.

Note: The Remove Duplicates function will remove every instance of the information starting with the 2nd. Excel will automatically remove all identical rows (blue) except for the first identical row found (in yellow).

Another Option: Advanced Filtering

sort filter how to delete duplicates in excel

Another option for filtering out duplicates in Microsoft Excel is using the Advanced Filter option.

  1. Once again, you’ll begin this process by opening your Excel file.
  2. 2. Once the file has been opened, you can select all of the cells in the table by pressing Ctrl + A (Select All).
  3. Once your entire table has been selected, click the Data tab.
  4. From the Data tab, choose Sort and Filter, then click on the Advanced button.
  5. Once you’ve entered the Advanced Filter dialog box, check the box that says “Unique Records Only.”
  6. Once you’ve clicked “OK,” all duplicates except for the original should have been removed.

Deleting Duplicates in Microsoft Excel 2003 or earlier


If you’re using a version of Microsoft Excel from 2003 or earlier, the method for deleting duplicates will be a little different than those we’ve mentioned above.

  1. Click on cell A1, selecting it.
  2. Choose Data, Filter, and then AutoFilter.
  3. Click the Filter arrow in cell C1 and then choose Custom.
  4. Where it says Equals, change this to Greater Than. Enter 1 and then click OK.
  5. Once the duplicate values have been identified, you can delete them individually.
  6. Once a duplicate value has been deleted, its partner value will lose its highlight.

Conclusion

Microsoft Excel is an incredibly useful tool for anyone who has a set of data which needs to be organized. Once you’ve input your data into your Excel spreadsheet, you’ll want to check it for errors. One of the most common errors will be duplicate values that have been entered.

If you’ve followed our instructions, duplicate errors should be easy to identify and delete, regardless of which version of Microsoft Excel you’re using.

how to make a scatter plot in excel

Ready, Set, Scatter: How to Make a Scatter Plot in Excel

This article consists of all the basics of how to make a scatter plot in Excel. By using this guide, you will be able to generate your own plots as well as format them and add design features.

Scatter plots, also known as scatter charts or XY scatter plots, are a powerful visualization tool for your data. They are used by engineers, statisticians, and other scientists to demonstrate visually a relationship between two variables using an XY axis chart.

What is a Scatter Plot?

Before we dive into how to make a scatter plot in Excel, we must first answer the question ‘what is a scatter plot?’ While they may sound complicated to make and use, they are similar to line graphs in many ways, particularly in that they use horizontal and vertical axis to plot out data points. Scatter plots indicate how a variable is affected by another. We call the relationship between the variables the correlation.

Generally speaking, scatter plots contain a wide variety of data. When the two variables are listed close together, this indicates the strength of their relationship. When the variables make a straight line out from the point of origin to high x and y values, they have a positive correlation. The inverse is also true. Variables that go down from a high value on the y-axis to a high value on the x-axis have a negative correlation. A perfect positive correlation has a value of 1. Similarly, a perfect negative correlation has a value of -1.

how to make a scatter plot in excel

Relationship Between Variables

A classic example of the relationship between two variables is the one between height and weight. By collecting height and weight data on 100 high school boys, for example, you can then use a scatter plot to demonstrate a relationship between a boy’s height and weight.

The taller the boy, the more likely he’ll weigh a little bit more. Of course, there are some outliers. We all have that really tall, skinny friend, but he will also be taken into account in the scatter plot.

Of course, if you’re looking at this article, chances are you already know a little bit about statistical relationships and scatter plots. What you want to know is how to go about visualizing the data you already have in a neat-looking and comprehensible chart in Excel.

Not all data can be visualized well using a scatter plot, especially if you want a plot that utilizes a great deal of text. You will have to play around with the data you have to figure out whether a scatter plot is appropriate.

Nevertheless, in this article, we will discuss exactly how to make a scatter plot in excel, so you can determine if it shows the information you want to show.

The Data

The first thing you will need is to compile your data. For the purposes of this article, we will use the height/weight example to demonstrate how to make a scatter plot in Excel. As you can see from the screenshot, we have three columns: person, height, and weight.

name, height, weight, how to make a scatter plot in excel

Insert Your Chart

Now that we have the data we need, we will go ahead and insert a chart. To do so, go to the “INSERT” tab and look for the “Charts” menu. There are many different charts you can create using Excel, including a line chart, pie chart, and a column chart.

For now, though, we will stick to the scatter chart, although it is good to know where the other charts are. That way, if a scatter chart doesn’t work for your data, you can easily return to the INSERT tab and try another one.

When you click on the scatter chart button, a drop-down menu will appear. The menu presents five different types of scatter chart you can use.

The five different types of scatter chart are:

  • 1st Scatter
  • 2nd Scatter with smooth lines and markers
  • 3rd Scatter with smooth lines
  • 4th Scatter with straight lines and markers
  • 5th Scatter with straight lines

Each of these charts will present the same relationships, but they will look different. The most recent versions of Excel will show you previews of the charts when you hover the mouse over a specific selection. For example:

how to make a scatter plot in excel

As you can see here, our data has been separated into two lines: the blue for height, and the orange for weight. However, there are a few things we might want to change.

For example, at the moment, the X-axis shows the number of students. This kind of thing is fine, especially if you are showing how a dataset can change over time.

But we want to show a relationship between height and weight instead. To do that we simply select the data differently. Now our chart shows height on the X-axis and weight on the Y-axis.

how to make a scatter plot in excel

You can see here in that now we have only blue dots, but they represent the relationship between height, which appears on the X-axis, and height, which appears on the Y-axis.

Adding Design Elements to Your Scatter Plot

Now that you have a scatter plot, you can play around with it. There are many different flourishes that you can add.

One easy thing you could do to this example is to add titles to your axes. As you can see above, none of our axes are named. You have the data right in front of you, so you know what is on the two axes, but other people who are only able to see your scatter plot might not.

By selecting the chart, buttons will appear on the right-hand side, which will allow you to adjust or change design elements. The specific buttons are chart elements, style, and values. To change the axis titles just click the elements button:

how to make a scatter plot in excel

As you can see, there are many different elements you can add to your scatter plot. You can change the title, add a legend, and include a trend line. This will make it easier for your viewers to follow the relationship you are trying to prove.

The Design and Format Tabs

If you click on the “DESIGN” tab you will find even more features. They can help you create the exact design you want. You can change the color of your chart, the scale, whether or not the chart is gridded. You can play around with any design elements you want to see what fits for your specific presentation.

“FORMAT” on the other hand allows you to change things like font style, shape style, and other elements.

How to Make a Scatter Plot in Excel: Final Review

Microsoft Excel has some powerful features that allow its users to present data in many different ways. When you are trying to present relationships between variables, a scatter plot can be a very useful tool. You can choose between numerous kinds of scatter charts to best fit. In addition, once you have chosen and created a chart to present your data. You can then manipulate your plot in any number of ways to better show off that data. You can change myriad design elements to fit the particular needs you or a client has.

Image that shows how to freeze cells in Excel.

How to Freeze Cells in Excel So Rows and Columns Stay Visible

Image that shows how to freeze cells in Excel.

Have you ever worked in an unorganized spreadsheet? We have to admit, there is nothing more frustrating. When you scrolled down the endless rows, chances are, you couldn’t see your headers anymore. How are you supposed to keep track of where you are plotting data? This is where knowing how to freeze cells in Excel comes in handy.

If you have spent time working in a large worksheet, you may have wondered if there is a way to keep your rows and columns visible. This way, you can keep specific information visible when scrolling down or across. It is time-consuming and cumbersome to navigate back and forth to compare the top of your worksheet to the bottom. Luckily, Excel has a few built-in features to maximize your workflow efficiency.

Sometimes you may want to keep specific information visible while scrolling through your spreadsheet. In this case, Excel’s “Freeze Panes” feature is useful. In this article, we will show you all the ways to freeze the cells and how to do so where rows and columns stay visible. Here are a few tips to keep in mind when learning how to freeze cells in Excel.

  • Excel can only freeze panes from the top down and left to right.
  • Panes cannot be frozen while in “Edit” mode.
  • Frozen Panes can easily be unfrozen.

When and Why You Should Learn How to Freeze Cells in Excel

As mentioned earlier, unnecessary scrolling in Excel is a waste of time. It takes longer than you might think just to scroll up and peek at your category labels. It’s essential to minimize wasted time so you can get your work done efficiently.

In many cases, your spreadsheet will fill up more than what is visible on your screen. Any given worksheet contains a maximum of 1,048,576 rows and 16,384 columns. Thankfully, most spreadsheets will not meet their maximum capacity.

If you are working with several categories, it is helpful to keep your headers on screen at all times. Hence the need to learn how to freeze cells in Excel.

How to Freeze Cells in Excel

Although it’s not obvious at first glance, learning how to freeze cells in Excel is a simple process. While this feature is quite useful, it does have its limitations and can be a bit finicky at times. Below you will find out how to freeze panes and some common pitfalls to avoid.

Freezing the Top Row

Let’s start with freezing the top row of a worksheet. Freezing the top row is probably the most common use of the “Freeze Panes” feature. Freezing the top row of a spreadsheet allows you to keep your headings in place so that you can see them while scrolling.

To begin, select the “View” tab on the Ribbon at the top of your page. In this tab, you can check to ensure that your worksheet is in the “Normal” view and also select your desired “Freeze Panes” options. The “Freeze Panes” tool will not be available if your worksheet is not in “Normal” mode.

With your worksheet in “Normal” view, click the drop-down arrow on the “Freeze Panes” icon. You can then select “Freeze Top Row” from here. The top visible row of your worksheet will now be locked in place at the top of your spreadsheet.

Please note that the “Freeze Top Row” option freezes the top visible row of the current spreadsheet. If your worksheet was not scrolled all the way up, you might have frozen your spreadsheet farther down from the actual top. This feature is useful if you want to compare a row in the middle of your spreadsheet to one at the bottom.

If you freeze a “top” row that is not the first row of the worksheet, Excel will hide all rows above the frozen row until you unfreeze your “top” row.

Freezing the Leftmost Column

Let’s say that you’re working in a large worksheet with a list of dates on the leftmost side of the page and multiple categories across the sheet. If you want to keep your dates visible while exploring all of your categories, you will need to freeze the first column of your spreadsheet.

Freezing the first column of your worksheet is precisely like freezing the top row. The only difference is that between the “Freeze Panes” drop-down menu you will select “Freeze First Column.”

Keep in mind Excel defines the “first” column as the leftmost visible column on the screen. If you want that to be your very first column, you must have your worksheet scrolled entirely to the left when selecting this option. Otherwise, you can use this feature to freeze a column from the middle of your spreadsheet to compare it to the right side.

Just like when freezing the “top” row of a worksheet, freezing a column from the middle of the spreadsheet will hide all of the columns to the left until you unfreeze your “first” column.

More Options for How to Freeze Cells in Excel

It is possible to freeze more than one row or column at the same time. Additionally, you can freeze both rows and columns simultaneously. These features can be useful if you need to compare more than one row or column to other rows or columns that are too far separated to see.

To freeze more than one row, select the row below the last row you want to freeze. Navigate to the “Freeze Panes” drop-down menu and select “Freeze Panes.” Excel will freeze all of the rows above your selected row.

Freezing multiple columns is very similar. Highlight the column to the right of the last column you want to freeze. Navigate to and select the “Freeze Panes” option. This freezes all the columns to the left of your selected row.

To freeze rows and columns at the same time, select the cell that is under the last row you want to freeze and to the right of the last column you want to freeze. Navigate to the “Freeze Panes” option and select it by clicking.

To unfreeze rows or columns, select “Unfreeze Panes” from the “Freeze Panes” drop-down menu.

Despite its limited functionality, the “Freeze Panes” feature is handy for working with large worksheets. Knowing how to freeze cells in Excel may take a little practice to master. Have patience while learning the valuable skill and you will reap the benefits of a much more efficient workflow. Excel is a robust program with solutions for nearly every problem you will encounter.

Here’s a Shortcut to Freeze Cells in Excel

Did you know that there are handy keyboard shortcuts for Microsoft Excel programs? Using these shortcuts not only saves you work time but also from pesky, repeated actions. The main reason why people use shortcuts for Excel is that they do not have to take their hands off the keyboard to use a computer mouse and visually search for both menus and buttons. For example, let’s say that you want to add a new workbook. Without using a keyboard shortcut, you would have to click the “office button,” select “new,” and then double click the “blank workbook” icon. Did you know that you can just click Cntrl + N (The control key clicked with the button for n)? The following abbreviations will make your work life a whole lot easier. 

  • Shift + Space: This shortcut will select a row. 
  • Ctrl + C: Use this shortcut to perform the copy action.
  • Shift + Ctrl + Arrow Down: With this combination, you can select all the way down to the bottom of a region within Excel. 

How to Freeze Cells: Final Review

That is it. You see, learning how to freeze cells in Excel is not as daunting as it may have seemed. With these tools and handy shortcuts under your belt, you are well on your way to workplace efficiency. 

how to use goal seek in excel

How to Use Goal Seek in Excel

Excel has proven itself to be very useful in various situations over and over again. The list of Excel’s benefits seems to be never-ending.

It even has a tool for answering questions and forecasting information. The Goal Seek function in Excel is a great tool for those asking “What if” type questions.

Use this guide to learn how to use goal seek in excel as well as how to put it into action.

Why Use Goal Seek?

why_use_goal_seek_

 

The Goal Seek feature in Excel is basically used to create formulas. It provides information on cause and effect situations.

It determines what specific data will impact another set of data. This feature comes in handy in lots of scenarios.

In financial and sales situations, it can be used to determine what must be accomplished in order to reach a certain monetary goal.

It can also be used to calculate how many votes a candidate needs to win an election.

Once you have your Goal Seek formula in place, you can change any part of it to see how the formula as a whole change.

For example, a computer sales company has a profit goal of $3 million for the year.

Each of their computer systems cost $200, while additional accessories like keyboards cost $50 extra.

This company could use how to use goal seek in excel to determine how many of each product they’ll need to sell to reach their goal.

Here’s How To Use Goal Seek In Excel

To use Goal Seek, you will have to open a new sheet and enter the current information you have.

To make learning Goal Seek a little easier, we are going to use a simpler example.

Sample Scenario

how to use goal seek in excel

How To Start

To start, enter your information and functions into a spreadsheet in an organized table.

For our data, we’ll have the headings Playground, Movie, and Total in the first column.

Across the top, we’ll have the headings Votes and %. We have set up our table to calculate percentages base on the votes.

The goal will be to determine how many votes will it take to choose the Playground option.

Next Move

The next thing we need to do is select the cell we want to change. We want to make the Playground vote equal 66%, or 2/3, so we will select that cell.

Under the Data tab, locate the “What If Analysis” button. Click it to reveal the drop-down menu.

On this menu, you will see “Goal Seek.” Select this option, and a dialogue box will appear.

The first option you will see will ask you to set your cell. In our case, the percentage of Playground votes cell is D2, so we will enter that into the box.

Next, you will see “To value.” This is where you enter the desired goal. Our goal is 66%, so we will enter that into this box.

Final Steps

Finally, the last box will say “By changing cell.” This will be whichever cell you want to change in order to get your result.

We want to know how many votes will get us to 66%, so we want to change the number of playground votes. In our case, that is cell C2.

After you input all of your information, hit OK. Your information on your table will change to accommodate the goal number you have set.

In our case, we change the playground percentage to 66% based on the number of votes it needs.

When we hit OK, the numbers on our chart revealed that 24 out of the class’s 37 students would have to vote for the playground.

That is for them to reach 66% and establish a fair vote.

The Goal Seek function also shows us that for this to be true, 13 students will have voted for the movie.

Conclusion

 

how to use goal seek in excel

The Goal Seek function can be a very useful tool when trying to forecast sales, finances, votes, etc.

Numbers can be tricky to determine, and in important situations, it can be crucial to making decisions based off of accurate information.

The Goal Seek serves to perform exactly that.

Upon first use, the function can prove to be a little tricky. An important thing to remember is to make sure all of the functions in your table make sense.

The Goal Seek feature will not work properly if your functions don’t add up.

You may also get some funky numbers that don’t make sense if your functions don’t make sense.

It is a good idea to practice using this method a few times before you start using it for important projects.

Once you have the feature mastered, Goal Seek is an excellent tool for both professional and educational use.

Read through this guide on how to use goal seek in excel and follow the steps a few times to really nail down the skill.

This may come as a surprise to how useful it becomes. As a matter of fact, your business or job will be a lot easier.

Soon you’ll be forecasting information for all sorts of projects!

how to freeze a row in excel

How to Freeze A Row in Excel: A Practical Guide

Learn to how to freeze a row in excel (or even more than 2 rows) using this practical how-to guide.

When you are working with lots of data on your laptop or monitor, it is helpful to know how to freeze a row in Excel. After all, it is often difficult to compare one or more rows with others that you are working on at the bottom of the document. Excel’s freeze pane feature solves this problem. How? You can lock specific rows of data so that they are always visible to you as you scroll through the Excel sheet.

You may or may not know this, but a single Excel worksheet can contain as many as 1,048,576 rows. This program is well known for its ability to create vast databases of information, but how much of that information can you see on your computer screen? The answer, of course, depends upon the size of your computer screen. Even massive screens cannot contain every row of a large spreadsheet. Thankfully, Excel has an available feature allowing users the ability to freeze a row. In this article, we will teach you everything you need to know about how to freeze a row (or multiple rows) in Microsoft Excel.

how to freeze a row in excel

What Does It Mean to Know How to Freeze A Row in Excel?

Freezing a row within a worksheet will keep that row present at the top of your page while allowing you to scroll through the rest of the spreadsheet. Freezing a row is particularly helpful when you have several data points to consider.

How to Freeze a Row in Excel

Whether you are brand new to Excel or you have some experience, you will find out how to freeze a row in Excel below. Below, we will discuss different scenarios in which freezing a row could be useful and give examples of how to accomplish your desired results.

A few important things to remember when freezing rows in Excel:

  • Freezing rows is not limited to large spreadsheets
  • It is easy to unfreeze previously frozen rows
  • A row must be on screen to freeze it

Keep Your Headers at the Top of Your Worksheet

It is common practice for the top row in a worksheet to be a header row.

The header row contains information about the data found in the cells below each label. When you are working on a large spreadsheet, it is beneficial to keep your headers at the top of your worksheet while you scroll further down into the data.

To keep your headers at the top of your worksheet, you will need to freeze the top row. Keeping your headers at the top of the page will reduce the amount of time you spend scrolling and maximize your workflow.

To freeze the top row of your worksheet, scroll to the top of your spreadsheet. Scrolling to the top of your spreadsheet ensures that your header row is visible on your screen. In the Ribbon at the top of your screen select the “View” tab. In this tab, there are several available options.

To freeze your headers at the top of your worksheet, first ensure that your spreadsheet is in “Normal” view. You can find this setting in the first section of the “View” tab. If your worksheet is in any view other than simply click on “Normal” to select this view.

After you have made sure that your worksheet is in the “Normal” view, you will find the “Window” section in the “View” tab. In the “Window” group you will see a drop-down arrow labeled as “Freeze Panes.” Click the drop-down arrow to open the menu and select “Freeze Top Row.” You should find this as the second option in the drop-down menu.

With all of these steps completed, you will be able to scroll up and down your worksheet without disrupting the position of your header row.

Freezing More Than One Row in Excel

There are times when you might have more than one row at the top of your worksheet that you want to keep visible while scrolling. Excel makes it very simple to freeze more than one row.

To freeze more than one row you will begin by placing your cursor in the row below the lowest row you want to freeze. For example, if you are trying to freeze the first four rows of your worksheet, place your cursor in the fifth row.

After you have placed your cursor appropriately, click on the “View” tab. Once again you will click on the drop-down arrow beside the “Freeze Panes” icon. To freeze multiple rows, you will select “Freeze Panes.” This option should be the first option in the drop-down menu.

When you freeze a row, a dark line will appear underneath the bottom-most frozen row. This line will show you where your frozen rows end, and your non-frozen rows begin. You can now confidently scroll up and down your worksheet while keeping your first few rows in sight.

Unfreezing Rows

When you are finished scrolling through your worksheet’s data, you may want to unfreeze the rows you have previously frozen. Unfreezing rows is just as simple as freezing them. First, select the “View” tab. Next, click the drop-down arrow on the “Freeze Pane” icon. Click “Unfreeze Panes” to return your worksheet to normal.

Freezing Rows Vs. Splitting Panes

Excel offers a second option for keeping specific rows in view while scrolling through the rest of your worksheet. This option is known as the “Split Pane” feature. When you use the “Split Pane” feature of Excel,  your screen will split into two or four. You can scroll through each of these panes independently from one another.

In contrast to freezing rows, split panes do not hold a particular set of rows at the top of your worksheet. This flexibility can be useful in some situations, but remember, you cannot freeze a row while splitting panes.

Split panes can be useful in some situations, but they will not always keep your headers in place at the top of your page. To ensure you can view your first rows without scrolling back and forth, choose to freeze rows instead of splitting panes.

Although being able to freeze the first row (or first several rows) is quite handy. It is important to realize that you can only freeze rows from the top down. You cannot freeze rows from the bottom up or freeze a row independently in the middle of the worksheet.

Freezing a Row in Excel: Life Hack

Freezing or unfreezing worksheet sheets commonly includes different mouse clicks, however, I’ll demonstrate to you an approach to complete this assignment with a solitary keystroke. For the uninitiated, solidifying sheets enable you to secure sections or lines that you determine along with the left-hand or potentially top of Excel’s worksheet zone. As you look to one side or down, the solidified segments or lines stay set up so you can generally see worksheet headings or the underlying segments. When you never again need the lines or segments secured, you thus can unfreeze them. 

Commercial 

To do this errand in Excel 2007 and later, first snap on the worksheet position you wish to solidify. Next, go to the View tab, click on Freeze Panes, and afterward make a determination from the submenu, for example, Freeze Panes.

To open the lines or segments, explore to the View tab, pick Freeze Panes, and afterward Unfreeze Panes. It’s somewhat more straightforward in Excel 2003: pick Window, and afterward Freeze Panes or Window, and afterward Unfreeze Panes, individually. 

In any case, you streamline this procedure down to a basic keystroke of your decision in Excel 2007 and later. Select Commands Not in the Ribbon, and after that look down the subsequent rundown until you discover Freeze Sheet Panes. Either double tap on this direction or snap once on it and snap Add to add it to your Quick Access Toolbar. On the off chance that you as often as possible stop and unfreeze sheet sheets, utilize the bolts on the right-hand side to move the Freeze Sheet Panes order with the goal that it’s the main direction on the rundown. Snap OK to close the Excel Options exchange box. 

When you’ve put the direction on your Quick Access Toolbar, you would now be able to press the Alt key to uncover the numeric easy route for the Freeze Sheet Panes order. In the event that you made it the primary order on the rundown, you would now be able to squeeze Alt-1 to stop or unfreeze sheet sheets. You should at present position your cursor as before when solidifying sheets, however, you can sidestep exploring through the View tab and the subsequent Freeze Panes submenu. 

How to Freeze a Row in Excel: Final Review

Learning how to freeze a row in Excel develops a useful skill to have. Keeping the first row visible while scrolling through your entire worksheet is practical. Frozen header rows can save you time and frustration, especially when dealing with large spreadsheets. Regardless of the size of your worksheet, the process of freezing rows is simple and will make your work easier.If you want to know how to freeze cells in Excel so rows and columns stay visible, we have a tutorial for that here.

how to alphabetize in excel

How to Alphabetize in Excel: Fast & Simple

how to alphabetize in excel

One of Excel’s most important features is the ability it gives users to quickly and easily sort through data. With this said, learning how to alphabetize in Excel is critical. If you are not familiar with this skill, don’t worry too much. We will go over alphabetizing lists of strings, ordering numerical values, standard alphabetization, reverse alphabetization, and more. Using Excel we can properly organize data for our businesses or personal finances. Learn how to alphabetize in excel so it is easy to read and refer back to whenever necessary.

What is Excel?

If you are a complete newcomer, you can still walk away from reading this article and learn how to navigate Excel. Let’s start with the basics. Before you learn how to alphabetize in Excel, we need to address the question ‘what is Excel?’ The answer is simple.

Microsoft Excel is a software application included within Microsoft Office Suite. You will use this tool to create spreadsheets (aka documents laid out in rows and columns). Due to the program’s versatility, it has become one of the world’s leading platforms in business since its initial launch in 1985.

What is an Excel Spreadsheet?

The next question you should be asking is ‘what is a spreadsheet?’ In short, spreadsheets organize data into easy ways to read and manipulate (rows and columns). An Excel spreadsheet is composed of columns (vertical boxes labeled ‘A, B, C, D…’ at the top of the screen) and rows (horizontal boxes labeled ‘1, 2, 3, …)’) at the left side of the screen. The intersection of each column and row holds cells where a user can enter either numbers or text. When one refers to the address of a cell, they mean the letter of the cell’s column combined with the row (e.g. A4).

How To Alphabetize In Excel

The main reason people use Excel is to organize data into an easy to read list. You want to be able to refer back to information from years ago, and quickly find something you need.

Excel is so useful because it gives you the ability to do just that. You can organize information alphabetically from A-Z or Z-A. This method allows you to create highly functioning spreadsheets that will keep all your data in order.

We’re going to break down:

  • How to alphabetize in Excel
  • Organizing columns that contain multiple strings of information
  • How to sort spreadsheets that have a combination of letters and numbers

Let’s dive right in!

The Reasons for Alphabetizing

For demonstration purposes, let’s say you are the owner of a sporting goods wholesaler and this is your spreadsheet for the month. If you look at the picture below, there is no structure to the data.

products how to alphabetize in excel

If you had a list built this way for ten years and you needed to look back during an audit. Then it would be incredibly difficult and time-consuming to find the information you need.

If we alphabetize the data, we could:

  • Scan through to find the name of a customer or product
  • Choose a specific department to document revenue
  • Find sales information on a particular product
  • Keep data organized for accounting and tax purposes

Additionally, if you have employees, having organized spreadsheets is even more critical. If you can’t make sense of your data, your employees will never understand it.

Basic Alphabetization

There are a variety of ways to organize information in Excel. However, we will start with the simplest method and work our way up.

Firstly, highlight the columns or rows you would like to sort by clicking and dragging your mouse across the cells.

Click the Sort & Filter button on the top right of your dashboard, and you will be able to sort Ascending from A-Z, or descending from Z-A.

customers table how to alphabetize in excel

Accordingly, doing this will organize the columns you have selected alphabetically. As you see above, I arranged the Customer column from A-Z.

One thing to remember:

It’s important to make sure you do not have empty spaces or special characters in front of your data.

Advanced Alphabetization

Alphabetizing in Excel is easy when you are sorting one column. Things get interesting when you have multiple columns, and you need to keep them in order as well.

Let’s say you want to organize alphabetically by the customer. But you also want to keep those customers in groups based on departments they purchase from. You can do that using Excel’s custom sort feature.

Firstly, select all your data by dragging your mouse over everything or clicking the arrow pointing diagonally towards your spreadsheet. Then make sure you highlight the entire spreadsheet – if you are missing cells you won’t have everything sorted.

Next, go back into the Sort & Filter drop-down box and select Custom Sort. Your screen may vary slightly depending on which version of Excel you are using.

sort how to alphabetize in excel

In the box above, you will see that there are a few options for sorting your data. Under column, you can choose what header you are sorting. For this example, we will sort by “Product.”

The next option you see is “Sort On” – this is what your cell is based on. For example, we will always leave it as “values.” You can sort based on cell color, font color, and more.

Lastly, you can choose the order for which you are sorting. You can sort ascending from A-Z or descending from Z-A when learning how to alphabetize in Excel.

If we do this, we will have our product list sorted from A to Z similar to the first demonstration, but this time we are going to get more advanced and add another level to the sorting.

In the custom sort dialogue box, click add level, and you will see another row appear.

sort 2 how to alphabetize in excel

The second line is labeled “Then by” – this level of sorting will follow whatever is in the first line. Whatever column you include here will sort after your first line of data.

For our example, we will use Revenue as our column to sort after we have alphabetized our products.

We will leave “values” as what we are sorting based on and this time we are dealing with numbers instead of letters – we were given a different option for “order.” We want to sort our revenue from Largest to Smallest to find out what products are our best sellers.

Once you have everything set up and you’re ready to organize your data, press OK, and you now have a spreadsheet that is organized by product first and revenue second.

Looking at this organized spreadsheet, you can quickly identify what your best-selling product in the football department is footballs in packs of 5.

products table 3 how to alphabetize in excel

Alphabetizing in a Custom Order

Up until this point, we have been organizing data ascending or descending.

You are probably asking – How do I arrange chronologically by month?

If you organize months alphabetically, February will come first so that would not work. We are going to show you how to get even more technical and organize chronologically.

First, go back to Sort & Filter and open up Custom Sort.

products table 4 how to alphabetize in excel

We have added a column for the month now.

Under “order” in the custom sort box, you will choose “custom list,” and it will bring up this dialogue box:

custom lists how to alphabetize in excel

Here you will manually enter the order you want the entries to appear. In this example, we want to order the data – January, February, and March. Click OK to confirm then hit OK again to sort.

products table how to alphabetize in excel

Finally, you have a completed list that is organized by Product, Revenue, and Month.

Why Isn’t My Spreadsheet Sorting?

It could be the wrong selection. On the off chance that you chose the off-base lines and sections or not exactly the full cell extend that contains the data you need to sort, Microsoft Excel can’t organize your information the manner in which you need to see it. With a fractional scope of cells chose, just the determination sorts. With void cells chose, nothing occurs. To sort every one of your information without making a choice first, click in one cell inside your information extend. When you open the Sort discourse box, you can see a choice zone encase the information that Excel will arrange. 

Effectively Sorted 

Issuing a sort direction on an informational collection that you’ve effectively arranged, or that you entered utilizing prearranged information, creates no unmistakable outcomes. Excel reacted to the direction you mentioned, but since you effectively composed your data, you can’t sort similar information twice into a similar request. To re-try your sort and really make a redesigned outcome, you’ll have to adjust your sort parameters. 

Off-base or Mixed Data Type 

On the off chance that you key in a segment of dates into cells that you’ve set up in a blend of content and date designs, your information won’t sort effectively. By blending your cell groups, you’ve set up a line or segment that contains what could be compared to apples and oranges rather than only either. How you show your information additionally can impact how you translate the aftereffects of a sorting task. Dates that show with just their month and day indicating may sort in a startling request since they really originate from various years. When you see odd outcomes, for example, these, you may need to check your information and cell types. 

Additional Considerations 

Record or application debasement dependably can create unexpected outcomes even in routine programming activities, for example, a worksheet information sort. On the off chance that checking your information and your worksheet configuration doesn’t resolve your arranging issues, shut down Excel and restart your PC to check whether your outcomes change. You additionally can duplicate your worksheet substance, glue them into another Excel record and attempt your sort task there, viably precluding – or distinguishing – your document as the wellspring of your concern. 

What is the Difference Between a Workbook, Worksheet, and a Spreadsheet on Excel?

When you open Microsoft Excel (a spreadsheet program), you’re opening an exercise manual. An exercise manual can contain at least one distinct worksheets that can be gotten to through the tabs at the base of the worksheet your at present survey. Often most confounding that a worksheet is synonymous with a spreadsheet. At the end of the day, a spreadsheet and worksheet mean something very similar. Be that as it may, a great many people just allude to the program like a spreadsheet program and the records it makes as spreadsheet documents. 

Spreadsheet Programs 

Today, Microsoft Excel is the most prevalent and generally utilized spreadsheet program, however, there are additionally numerous choices. Despite the fact that spreadsheets are regularly utilized with anything containing numbers, the employment of a spreadsheet is practically interminable. The following are some other prominent employments of spreadsheets. 

Account 

Spreadsheets are perfect for money related information, for example, your financial records data, spending plans, charges, exchanges, charging, solicitations, receipts, conjectures, and any installment framework. 

Structures 

Structure layouts can be made to deal with stock, assessments, execution audits, tests, time sheets, persistent data, and reviews. 

School and Grades 

Instructors can utilize spreadsheets to follow understudies, figure reviews, and recognize pertinent information, for example, high and low scores, missing tests, and understudies who are battling. 

Records 

Dealing with a rundown in a spreadsheet is an extraordinary case of information that does not contain numbers, yet at the same time can be utilized in a spreadsheet. Incredible instances of spreadsheet records incorporate phone, to-do, and basic food item records. 

Sports 

Spreadsheets can monitor your preferred player details or details in the general group. With the gathered information, you can likewise discover midpoints, high scores, and factual information. Spreadsheets can even be utilized to make competition sections. 

What is a Functioning Worksheet? 

A functioning worksheet is a worksheet that is as of now open. For instance, in the Excel picture over, the sheet tabs at the base of the window show “Sheet1,” “Sheet2,” and “Sheet3,” with Sheet1 being the dynamic worksheet. The dynamic tab ordinarily has a white foundation behind the tab name. 

Why Not Use a Word Processor Instead of a Spreadsheet? 

While the facts may prove that a portion of the things referenced above should be possible in a word processor, spreadsheets have a tremendous preferred position over word processors with regards to numbers. It is difficult to compute various numbers in a word processor and have the estimation of the computation quickly show up. Spreadsheets are likewise significantly more unique with the information and can hide, show, and sort data to make handling loads of data simpler. 

How to Alphabetize in Excel: Final Review

Finally, Excel is an incredibly useful tool for business owners, entrepreneurs, and those who are financially savvy. This covers everything you will need to know about how to alphabetize in Excel. Follow this article step by step, and you will have no problem finding anything and everything you need to know for years to come! If you want to learn how to make a line graph, hiding columns or freezing rows in excel, then we have them for you too.

How to Make a Line Graph in Excel

How to Make a Line Graph in Excel

Organize data by learning how to make a line graph in Excel. Present information over time with this simple guide to creating a line graph.

As you may have seen throughout your various uses of Excel, the program is a great way to store and organize information. Your worksheet is meant to lay out all the information you have in a way that is easy to follow.

But did you know that Excel has ways to organize this information even further? With Excel, you can organize your information into line graphs, or line charts, as they are sometimes referred to. This simple guide will show you have to present your information in a more visual manner and with more organization.

What is a Line Graph?

Line graphs are one of the simplest tools to create within Excel. With this said, its simplicity does not outweigh its importance. Even Leonardo Da Vinci said, “simplicity is the greatest form of sophistication.” If you didn’t already know, they are popular in the field of statistics and science, as they show trends and are simple to plot out. So what is a line graph? In short, it is a chart used to demonstrate a change in quantitative data over a given period of time.

Independent values (e.g. time variables) are plotted along the horizontal x-axis. Dependent values (e.g. sales, prices, etc.) run along the vertical y-axis. Any negative values are located below the x-axis. The lines that rise and fall along the graph indicate trends within the data set. Upward slopes show an increase of values whereas downward slopes show a decrease.

When Should You Use a Line Graph?

A question that goes hand in hand with how to make a line graph is when should you use one? Below, we have listed optimal situations for line graphs to work.

    • To give a visual of trends and changes
    • When you need to visualize a large and complex amount of data
  • To show relationships between multiple sets of data

When You Should Not Use a Line Graph

Just as there are optimal times to use a line graph, there are also times when you should avoid using them. Below, we have listed situations where line graphs are not optimal to use.

    • Large stand-alone data sets
    • Continuous data (you should use a bar graph instead)
    • Percentages and proportions (you should use a pie chart instead)
  • Schedules

How to Make a Line Graph in Excel

Why Use Line Graphs?

Line graphs are a great way to show information over time. Information over time can be very crucial regarding businesses. Whether you are trying to present rises and drops in sales or figuring out which month brings in the most customers, line graphs are excellent resources to utilize.

For example, the owner of a zoo may want to know which animals are the most popular and which ones are lacking popularity. Over the timespan of six months, they’ve been tracking how many people visit the giraffes, the lions, the monkeys, and the elephants.  Now they want an easy way to organize this information to present it.

There are several ways you can present information, but lines graphs are easily one of the simplest ways. One of the great things about line graphs is that you can have multiple lines on one graph. Keep reading to learn how to make one yourself.

How To Make A Line Graph In Excel

Let’s continue using our zoo example to demonstrate how to make a line graph in excel. You should start by laying out your information in Excel like you normally would. The zoo information can be categorized by listing the animals across row 1. The six months, let’s say May through August, can be listed down column A. The numbers are filled in accordingly.

Once you have all your information entered in, you are ready to create a graph. Keep in mind that since there are multiple points of data here, you will have multiple lines on your graph. The great thing about line graphs is that you can use as much data over time as you want – just try not to overwhelm the graph, or it may be hard to read.

Beginning with Creating your Line Graph in Excel

To begin your graph, highlight the entirety of the information being represented. Click on the Inset tab and locate the Charts section. Find the icon that says “Inset Line or Area Chart.” Click on that to see a drop-down menu of various charts. Here, you will see a variety of options. Feel free to explore all of these options to see what they look like. For now, we are going to select a chart from the first section labeled 2-D Line.

Click the very first option. This will give you a chart that has multiple, different colored lines intertwined. This option is the easiest way to show each individual’s information in comparison with others. If you find that it is hard to tell where each point of information is, choose the chart that says “Line with Markers.” This graph will add points to each piece of information, which can make it easier to locate them.

As you have already noticed, there are several varieties and options to choose from. You can decide which option is best for your purposes once you have nailed down how to use these graphs.

Editing Line Graphs

You’ve created your first line graph! It looks great, but perhaps you want to change it up a little. Excel offers many ways to customize and edit your line graph once you’ve made it.

For one, you can change the graph’s overall style. Select the graph you have created and click on the Design tab. Under this tab, you will see several design options to choose from.

These options change the color, the fonts, the lines, the background, and can even do things like add numbers right on your graph’s points. Take a minute to scroll through and find a style that suits your taste and your needs.

Additionally, you can add on other individual elements to boost the intelligence of your graph and make it more detailed. Still, under the design tab, locate the “Add Chart Element” button. Click to find a drop-down menu listing items you can add such as Axes, Chart Title, Data Labels, Error Bars, and more.

Rather than adding each element one by one, you can also take a look at the “Quick Layout” menu located right next to the “Add Chart Element” menu. The Quick Layout feature gives you various layouts including different combinations of chart styles and additional elements. You may find a chart you were going to create yourself already exists in this menu!

Changing Colors

The “Change Colors” menu gives you a quick and simple way to change the colors on your graph. From this menu, you can generate your own personal color scheme to your taste – even adding and editing line markers.

Finally, it is important to remember that once you’ve created your line graph, the information on it is not final. If you realize you’ve put in the wrong numbers or something has changed, you can easily update your graph by updating your original information. Simply change the information in your Worksheet, and the graph will automatically update itself.

The Advantages of Using a Line Graph to Demonstrate Data

A line graph gives a few advantages contrasted with other information portrayal strategies, for example, a bar or pie outline. There are three qualities which make it normal in numerous kinds of work, incorporating substantial use in data use or longitudinal studies.

A standout amongst the most significant advantages is the longitudinal fitness of a line chart. The timetable for a followed occasion can without much of a stretch be plotted along with an x/y pivot. This demonstrates a realistic portrayal of the ascent or fall of information focuses. Missing information can be plotted along the line with some level of sureness or mistake likelihood. This is additionally observed with chart assembly, which is regularly observed in monetary information focuses. For instance, the cost a customer is eager to pay for a thing can be crossed with a beneficial offer point for the organization to achieve the perfect market cost. 

Another advantage of the line diagram is the correlation factor as referenced quickly above. At least two things can be contrasted with cross focuses, which takes into account at the base recounted proof of an association. It ought to be noted not all line diagrams will have a similar beginning stage on a straight chart speaking to a longitudinal report. The production of a starting point along the pivot can show the continuation or incorporation of various information in time. 

The last advantage, as odd as it sounds, is the general shading generation of the line diagram. Line diagrams take into account a lot more approaches to speak to the information focuses. A reference chart can utilize shading to the contrast between thought about things, yet hues don’t generally interpret crosswise over various print mediums. Line diagrams can utilize shading or distinctive line styles, for example, dabs or dashes. While it appears to be a basic thing, this bit of leeway permits line diagrams to more readily move to dark/white paper print.

How to Make a Line Graph: Final Review

While there are still many details on how to customize and edit graphs, this guide gives you a great starting point to use this convenient feature. Use line graphs as an excellent way to organize and present data changing over time. As simple and easy as it is to create and use, it can also make a huge difference in any project or presentation you are making!

how to subtract in excel

How to Subtract in Excel

Learn how to subtract in Excel with this valuable how-to guide. This article will walk you through each step of the process from start to finish.

Excel is a powerful program that makes organizing numbers and data easy for anyone. But, learning how to perform even simple functions can be a bit tricky when first starting out. Excel can perform many different functions and one of the most basic is subtraction. Below you will find a complete guide on how to subtract in Excel.

We don’t know why Microsoft didn’t make it but there is no subtract function in Excel. You don’t need to stress though. There are several helpful (and fairly simple) ways to perform this task on your own. Are you ready to improve your Excel skills? Learning new software methods, tips, and tricks is always helpful to have under your toolbelt. In this article, we have important points to remember, various types of Excel subtraction, methods for subtraction with two or more cells in Excel, and more. Read on to learn more.

how to subtract in excel

Important Points to Remember

Different Types of Excel Subtraction

As mentioned above, Excel can subtract numbers in a single cell or within a range of cells. Both operations are simple to perform and only a little different from one another.

Below, we will talk about the different ways to subtract in Excel and give you some examples.

To get the most out of the information below, keep these terms in mind while reading:

  • Worksheet: an electronic document made up of rows and columns that can contain data
  • Cell: the intersection of a row and a column on a worksheet
  • Formula: the instructions entered into a cell to produce a specific result
  • Function: a built-in formula used in Excel

Subtracting with Simple Numbers

For simple math problems, you can use a single cell to calculate subtraction problems. As an example, we’ll use the problem 5 – 4 = 1. This problem is simple, but you can apply the same concept to larger numbers and more complex data.

To begin, use your cursor to select an empty cell on the worksheet. Once you select the cell, begin to type your formula.

In Excel, all formulas start with an equal sign (=). After you’ve typed the equal sign, type the numbers you’re subtracting separated by the minus sign (-). In this case, your cell would contain the characters “=5-4.”

Once you have entered the numbers you’d like to subtract, hit the “Enter” key.

Hitting the “Enter” key tells Excel that you are ready to execute your formula. The data in the cell will transform from the formula you entered to the solution of that formula. The example cell would now read “1” instead of “=5-4.”

Subtraction Using Two or More Unique Cells

In Excel, every cell has a “name” made by combining its column letter with its row number.

This is the cell reference. For example, the cell created where column A intersects row 1 is cell A1. You can use cell references in formulas to execute various operations including subtraction.

Like before, this type of subtraction begins by selecting an empty cell.

Follow the same steps, but, instead of entering numbers, enter specific cell references. For example, if you’d like to subtract the quantity in cell A1 from the quantity in cell B1, your formula would read “=B1-A1.”

Instead of typing in a cell, you can also type formulas into the formula bar found at the top of the worksheet. You can also select cells with your cursor after starting your formula instead of typing them out.

How to Subtract Using the SUM Function

As mentioned earlier, functions are Excel’s built-in formulas. A variety of functions are available in Excel. When subtracting in excel, the SUM function is most useful.

Although addition and subtraction are often thought of as opposites they are, in fact, one and the same.

While we may not think about it, subtracting a number is the same as adding a negative number. Excel does not have a SUBTRACTION function but instead relies upon its built-in SUM function.

Excel’s SUM function can use individual numbers, cell references, or a range of cells.

To subtract numbers using the SUM function, make the number you want to subtract a negative value. For example, we’ll say that cell A1 contains the number 5 and cell A2 contains the number 3. You can use the SUM function in an empty cell to subtract 3 from 5.

First, make the number you want to subtract negative by adding a minus sign (-) to it. In this example, we are subtracting 3 from 5 so we will add the minus sign (-) to the 3 in cell A2 making it -3.

To use the SUM function, enter an equal sign into an empty cell followed by the word SUM.

The equal sign tells Excel that you will be using a formula. The word SUM specifies the function you want to use. In parentheses after the word SUM, press enter for the numbers, cell references, or range of cells in Excel you want to sum.

how to subtract in excel by first using the SUM function

For the example given above, your SUM function would look like one of the following:

  • If you used individual numbers, “=SUM(5,-3)”
  • For using cell references, “=SUM(A1, A2)”
  • If you selected a range of cells, “=SUM(A1: A2)”

In Excel, you can also use the AutoSUM wizard by clicking on the “Formulas” tab and then choosing AutoSUM. Always switch the values you are subtracting to negative when using the SUM function.

How To Subtract In Excel: Final Review

As you can see, there are several different methods for how to subtract in Excel. Depending upon the type of data you are dealing with, some of these methods will work better than others. You can use each method to subtract numbers both large and small and organize large amounts of data. Learning how to subtract in Excel is a quick and simple process that anyone can master.

With a small amount of patience, you can apply these concepts to any worksheet you come across. Subtraction may seem like an insignificant skill to gain, but it is a step towards harnessing the full power of Excel. Do you want to know more about Excel’s Intermediate features? If yes, then click here.

Related Posts Plugin for WordPress, Blogger...