Tag Archives: formulas

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 add cells in Excel (screen shots of cell data added up via the SUM formula).

How to Add Cells in Excel to Sum Up Totals Automatically

Excel’s great for displaying data and even better at crunching numbers. Here’s how to add cells in Excel to sum up totals automatically… Even when you change the numbers.

A great feature that Excel has to offer is its use of formulas. Since Excel is often used to organize numerical data for a variety of operations, it can be beneficial to have an ‘addition’ function. In this guide, we are going to discuss the various ways we can add cells in Excel.

Why is the Addition Function Useful When You Need to Add Cells in Excel?

As we already know, in Excel Worksheets we can add columns. However, sometimes it is necessary to add cells that are not in columns or are not even next to each other.

For example, let’s say you are performing a survey to present data. You are interviewing random students to find out the most popular subjects taken in school. Your poll is anonymous, allows students to select only one option, and is organized by gender and grade levels Freshman, Sophomore, Junior, and Senior. The survey offers several subjects.

This survey is set up to give you a wide range of information and a variety of totals for you to examine. You could add these totals yourself, or you could use the many ways to add these numbers to Excel.

How to Add Cells in Excel: Method 1 (via Individual Table Cells)

In addition to adding columns in Excel, you can also add horizontally in rows. Let’s say you want to know how many senior males were surveyed. You can do this by adding up all the numbers in the Senior Male row. Let’s say the Senior Males row is row two in columns B2 through H2. One way to add your numbers is to select cell I2 and type the equals (=) sign, followed by the numbers in each cell in that row. For example, it may look something like this:

=5+6+2+7+0+9+1

Then, hit the enter button and you will get your total. While this method works, you will not be able to change the numbers in your cells without also changing your formula.

Because of this problem, it is better to enter the cells than the information within them. Rather than the above example, your formula will look similar to this:

=B2+C2+D2+E2+F2+G2+H2

When you hit Enter after typing this, you will still get your total. However, this time, you will be able to change the information in these cells, and the change will automatically affect the total.

How to Add Cells in Excel: Method 2 (Sum Function)

There is another method to adding rows. In this method, select your total cell. In our case, it has been cell I2. Type the equal (=) sign. Next, click the first cell in your row, or B2. This cell will become highlighted and appear next to the equal (=) sign. Then, type the plus (+) sign and click the next cells in the row. Continue this until you have clicked every cell in the row. Hit Enter to view your total.

Finally, rows can be added in the same SUM Function that we learned with columns. In your total cell, type =SUM. Then, type or click your beginning cell. Type the colon (:) mark and then type or click on the last cell in your row. It will look something like this:

=SUM(B2:H2)

Hit Enter when you have completed this function, and your total will show up. In this function, you can also change the values in each cell, and the changes will be reflected in the sum.

How to Add Cells in Excel: Method 3 (Adding Up Individual Cells In Different Rows and Columns)

The great thing about the addition function in Excel is that you not only get to add rows or columns. You can also add whatever cells you want to. Let’s stay with our survey example. Perhaps you want to examine precisely how many girls prefer math and science. Excel allows you to do this.

Label your first cell to maintain organization in your document. Click the cell next to it to begin your equation. Type the equal (=) sign, followed by all the cells you would like to add together. For example, your equation may look like this:

=C3+C5+C7+C9+D3+D5+D7+D9

Hit the Enter button when you are finished to get your total.

Of course, as we have already learned, it can be a bit tedious and take a while to type all of that. The alternate method would be to click instead of type. In your total cell, type the equal (=) sign. Then, click your first cell. Type the plus (+) sign and click your next cell. Continue this process until you have clicked all the cells you want to add. When you have selected all your cells, hit Enter to get your total.

Using this method allows you to select any cell that contains a numerical value. The values do not have to be in the same row or column or be next to each other. This method also allows you to change information in cells used in your formula. The modified data will automatically update the total to reflect the new numbers.

Conclusion

It was certainly good news to hear that we can add columns of information within our Excel Worksheets. It made life a lot easier and minimized the work we had to do.

Now, we see that we can also add rows as well as individual cells. This guide has expanded what we can do with the addition function. This function could be vital information when it comes to surveys, projects, and organizing business or personal information.

Follow this step by step guide to add cells in Excel using the addition function to its fullest, incorporating rows and cells into your skill set.

excel sumif

How to Use the SUMIF and SUMIFS Functions in Excel

SUMIF and SUMIFS help Excel users to save time and frustration by making it easy to glean valuable information from complex datasets. You can total and analyze everything from grade values to quarterly earnings without giving yourself a massive headache.

In this tutorial, we’re going to cover:

The difference between SUMIF and SUMIFS functions.

How to use SUMIF and SUMIFS.

Common examples of formulas.

The Basics of SUMIF Functions

Most people are familiar with Excel’s SUM function, which allows you to add together highlighted data values in a row or column. The IF function is another favorite tool that lets you see whether or not a particular data point meets a specified condition, including dates, numbers, and text.

SUMIF combines both the SUM and the IF functions to bring you a tool that not only allows you to single out information that meets essential criteria but also to find the sum of qualifying cells. You can choose a single parameter to help you isolate relevant cells, combine the data, and pull totals to gain invaluable insights into the information that you’ve collected.

Using SUMIF Functions

SUMIF functions allow you to sort information based on one criterion, making it easy to pinpoint the data points you need. Like all Excel functions, though, you have to do a little bit of the work as well. To use SUMIF, you need to know how to express just what it is you’re looking for.

It is a worksheet function that requires a specific syntax to yield the best results. The basic syntax for the SUMIF function in Excel is:

SUMIF(range, criteria, [sum_range])

This formula may look a little complicated, especially for those just starting out with Excel, but once you get the hang of it, the function is relatively simple. There are only three different parameters that you have to keep in mind when using the SUMIF function:

Range: The cells that you wish to evaluate. It can be a column, a row, or randomly selected data points.

Criteria: The condition that must be met. Depending on your needs, you may wish to make this either text, a numeric value, or a wildcard value. It can even be another function. Just remember that all text or criteria including math symbols need to be enclosed in double quotation marks (“).

Sum_Range: This parameter is optional, but it can help when dealing with particularly large data sets. It allows you to specify the range of cells you want to sum together. If you leave this element out of the SUMIF function, as many people do, the value will default to your (Range) parameter.

Examples of SUMIF Functions

sumif example

People use SUMIF functions every day in business, education, and in their personal lives. Here are some common formula examples that you might find yourself running across:

Subtotalling by a descriptor such as color: =SUMIF(descriptor_range,criteria,number_range)

Sum of cells that contain specific text: =SUMIF(range,”*text*”,sum_range)

The sum is “less than”: =SUMIF(range,”<1000″) The sum if “greater than”: =SUMIF(range,”>1000″)

The sum if cells are not equal to a specific value: =SUMIF(range,”<>value”,sum_range)

Subtotalling invoices by age: =SUMIF(age,criteria,amount)

Subtotalling by invoice number: =IF(COUNTIF(range,criteria)=1,SUMIF(range,criteria,sum_range,””)

Summing up by group or label: =IF(A2=A1,””,SUMIF(A:A,A2,B:B))

Comparing SUMIF and SUMIFS

While the SUMIF formula allows you to differentiate between data based on a single criterion, SUMIFS will enable you to specify multiple parameters. This tool is a relatively recent development from Microsoft that only hit the markets in 2007, meaning that it’s only available in Excel 2007 or higher. With SUMIFS, you can evaluate up to 127 criteria at once instead of just one. The function offers a much more powerful tool to categorize and analyze data.

When to Use SUMIFS Functions

SUMIF functions let you break down information based on a single differentiating factor, but that isn’t always enough. Sometimes you need to narrow data down further to analyze it, and that’s where SUMIFS comes in. You can use comparison operators like equals, less than, greater than, less than or equal to in relation to other values to gain practical insights into the real world. The basic syntax for any SUMIFS function is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Unlike the SUMIF function, you’re required to input “[sum_range]” for SUMIFS. There are also other important parameters to define, including:

Sum_Range: The range of cells you wish to input and analyze. This field replaces the Range field used in SUMIF functions.

Criteria_Range1: This value is paired with Criteria1 to specify the range of cells to be searched.

Criteria 1: This defines the criteria that will be applied to cells in Criteria_range1. You can use text, numeric values, other cells, and even qualifying commands such as greater or less than. Any non-numeric criteria need to be enclosed in double quotes.

Criteria_range(#), Criteria(#): You can add up to 127 different criteria and range pairings onto the formula as needed.

Examples of SUMIFS Functions

using sumifs excel

Although SUMIFS is relatively new, it’s become common to see on both business and personal spreadsheets. SUMIFS formulas give users more precision and power when analyzing data sets. Here are some of the more common formulas that you might encounter:

Sum by week number: =SUMIFS(sum_range,weekrange,week)

The subtotal falling between two dates: =SUMIFS(amount,start_date,”>”&A1,end_date,”<“&B1) The sum of sales based on ID and time frame: =SUMIFS(amounts,dates,”>=”&TODAY()-30,ids,id)

Conclusion

Excel is an invaluable tool when it comes to data analysis. Functions such as SUMIF and SUMIFS make it easy to break down information by allowing you to subtotal data based on essential criteria.

We hope that this tutorial has helped you further down the path towards becoming an Excel master. The next time that you have to subtotal data don’t forget to take advantage of the SUMIF and SUMIFS functions.

Related Posts Plugin for WordPress, Blogger...