Category Archives: Beginner

Beginner level information

making graphs in excel

How to Add a Row in Excel: Step by Step Process

Microsoft Excel is a powerful spreadsheet software used throughout the world. One of the features within the software is the ability to easily add rows and columns to the spreadsheet. It is a simple feature that is easy to take advantage of and, once you know how to implement it, you'll be able to implement it whenever needed. So if you're a user of the program it is important for you to know how to add a row in Excel.

How to Add a Row In Excel

excel spreadsheet

Image via flickr

If you are interested in how to add a row to Excel, you'll want to click on the row heading right below where you want the new row to appear (so if you typed in row 7 and row 9 and discovered you forgot to type in your row 8 information, you'll want to select row 9. This way, when you add the new row it will insert between 7 and 9).

With the row selected, click on the "Insert" button at the top of the Home tab. The new row will now appear above the row you currently have selected.

Formatting the Row

highlighting an excel row

Image via flickr

When you add in a new row, Excel will automatically format the row to look like the rest of the rows you already have. However, when you choose the "Insert" option there will be an "Insert Options" feature that appears on the row (the icon looks like a brush). If you want to change the look or format of the inserted row click on this brush icon, then choose either "Format Same As Left," "Format Same As Right," or "Clear Formatting."

Insert a Column

There might be times where you need to insert a new column of data instead of a new row. The process is similar, but it is important to go over. To begin, you need to select the column heading to the right of where you want the new column to appear (for example, if you want a column to appear between columns C and D, you will click on column D).

Now, select the "Insert" button on the top of the Home tab. The new column will appear.

Deleting a Row

There may be times when using Microsoft Excel where you want to delete a row. The steps for how to delete a row are similar to that of how to add a row in Excel.

Click on the row you want to delete. It is important to click on the header of the row and not the cell. If you click on the cell and choose the "Delete" feature you'll end up removing only the cell which may throw off your calculations and the alignment of the other cells in your spreadsheet.

So click on the number of the row you want to remove. You can click and drag over multiple row headers if you want to remove several rows from your spreadsheet at the same time.

With the row(s) selected, click on the "Delete" button found on the Home tab. This will delete the selected rows and all the rows underneath those removed will move up (so if you deleted row 5 and 6 your former row 7 will now be identified as row 5).

Delete Columns

If you need to remove columns instead of a row, the process is similar. You need to make sure and click on the header of the column. This selects the entire column. You can click and drag over multiple column headers if you want to remove multiple columns at the same time.

Once you have selected the column(s) you want to remove, choose the "Delete" button from the Home tab and the columns will be removed. Once again all the columns to the right will shift over to the left. So if you deleted columns D and E, the former column F will now be identified as column D.

If you renamed the column names, the names you created will remain the same.


Tips and tricks

Image via flickr

When it comes to how to add a row in Excel, you will always want to follow through with a few tips and tricks. Following these tips and tricks will make it easier for you to implement the new row without causing problems with the overall file. The last thing you want to do is cause problems with your files and have to start over completely on a new spreadsheet document.

When looking at how to add a row in Excel, you need to make sure you select the heading of the row and not a cell. If you click on a cell and then choose "Insert," Microsoft Excel will add a new cell and not a new row (or a new column, if you're adding a column). So, if you're running into the problem of only adding a new cell, this is what is going on.

Clearing the Content

There is a difference between deleting an entire row or column and clearing out the content found within the row. You may want to remove all the current information currently found in the cells of a row or column, but if you still need the rows/columns present, you don't want to just delete everything. Instead, you need to follow a "Clear Contents" option.

In order to use the "Clear Contents" option, you will need to click and drag over the rows and columns you wish to clear out. You can either click on the row/column header to select the entire row/column, or you can click and drag over specific cells.

Once you have selected the regions you want to click out, you'll need to right-click within the area you selected (or Control-click if you're using a Mac), then choose "Clear Contents" from the pull-down menu. This will clear out the cells but leave the cells open.

Move a Row/Column

Perhaps you don't need to delete, clear out, or add a row/column. Maybe instead you need to move it. You find information is in the wrong order or you think it would be easier to work if the row is found in a different area of the spreadsheet. You will not need to follow the how to add a row in Excel instructions as these do differ some.

First, you'll want to click on the header of the row you want to move. Once you do this select the "Cut" command found on the Home tab (it is a pair of scissors icon). If you're using a Windows computer, you can use the keyboard shortcut of Ctrl+X (or Command+C if you're using a Mac).

Now, click on the row under where you want the copied row to go. Once you have it selected click on the "Paste" tool found on the Home tab. if you're using a Windows computer, you can use the keyboard shortcut of Ctrl+V (or Command+V on a Mac computer).

Hide/Unhide Row/Column

There might be times where you want to compare two rows together but there are other rows in between that blocks your view. In this case, you will want to hide rows. By hiding rows, you can press two rows together temporarily.

These instructions are similar to how to add a row in Excel. Click on the row header of the rows you want to hide. Chances are there are multiple rows you want to hide so click on all the rows you want to hide for the time being.

Now, right-click on the area you selected and choose "Hide" from the pull-down menu. A green line will remain between the newly created rows. When you want to bring the rows back, you'll want to choose the rows above and below those that are hiding, then right-click in the selected area. Choose "Unhide" from the pull-down menu and the hidden information will appear back onto your screen.


Microsoft Excel is one of those programs you either will never use or you will use on a daily basis. If you are a user of the software, there are a few specifics you must master. One of these features is how to add a row in Excel. When you're able to add and remove rows in Excel, you will be taking the first major step in utilizing the power of the software and how it can make your professional life easier.

The NOW Function in Excel: What It is and How to Use It

What is the Now Function in Excel?

For those new to all things Microsoft software, the now function in Excel continuously updates the date and time whenever there is a change within your document. You can either format the value by now as a date or opt to apply it as a date and time with a numerical format. The purpose of the function is to set (and keep track of) the date and time.

Notes on Use

While the Now function in Excel does not have parameters, it does require empty parenthesis. The value returned by the Now function automatically updates after each refresh. If you need to, you can use “F9” to force the worksheet to refresh, recalculate, and update the value. For those of you who need a static time (e.g. one that won’t change), you can use the keyboard shortcut “Ctrl + Shift” to enter the current time.

The NOW Function in Excel returns the current date and time − formatted as date and time − as shown below in cell B1. If the cell format was General before using the function, Excel will change the cell format to match your regional settings for date and time.

NOW Function in Action

The NOW Function has no arguments, but the empty parentheses () is required, as seen in the formula bar above.

NOW Function in Excel

The NOW Function in Excel is Volatile, meaning every time Excel calculates the worksheet the function result changes. And you can’t always tell when this will happen. To see what I mean, enter a NOW Function in a worksheet cell then play around with the worksheet.

I’m not a big fan of Volatile functions. Used sparingly they can serve a specific purpose, but left unchecked in a workbook with a large amount of data they can drastically slow things down.

The cell formatting in cell B2 is General and you can see the serial number. The integer to the left of the decimal point represents the date, and the numbers to the right of the decimal point represent time.

The B3 cell format is Date and you see only the date portion. The B4 cell format is Time and you see only the time portion. Both cells have the same underlying value, 40409.47966.

Tip for Static Date or Time in Now Function in Excel

To enter the current date into a worksheet use the keyboard shortcut Ctrl+; (semicolon). To enter the current time use Ctrl+Shift+: (colon). Both are static entries that won’t change.

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.


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.

Person using excel on a laptop

How to Randomize a List in Excel: Our Complete Guide

There are times where you will want to adjust how Microsoft Excel displays the information. From time to time you might want to randomize the order of a list. Whether you want to take an email list and select a random winner for a contest or you have another reason for randomizing the list, there are several ways you can go about doing this. You need to know how to randomize a list in Excel.

How to Randomize a List in Excel Using a Formula

When it comes to using Microsoft Excel, there is a formula for just about everything in the program. And best of all, if there isn't currently a formula for your particular needs you can always create one. Thankfully, you won't need to create a formula to learn how to randomize a list in Excel. So whether you have entered in formulas into Excel in the past or this is your first time, all you need to do is follow these "how to randomize a list in Excel" instructions.

Creating the Randomized List

Open up the spreadsheet you want to randomize. Now, create a new column next to the names or any other column or row you want to randomize. If you have just a single column or row in the spreadsheet, then you don't need to perform this step.

In the first cell of the new column, type in the RAND formula of "=RAND()" (without the quotation marks).

You now will need to copy the code and paste it down along the area you want to randomize. You don't need to type the same information throughout (which can be a major hassle if you have hundreds of cells you need to randomize). Instead, double-click the fill handle.

Now, you can sort the column to randomize the information displayed. You can do this by clicking on the "Home" tab and then choosing "Editing Group." From there, you'll want to choose "Sort & Filter." A series of options will appear through a pop-up menu. This includes "Sort Smallest to Largest," "Sort Largest to Smallest," and "Custom Sort..." which allows you to adjust how you sort the information.

You can also sort by clicking the "Data" tab, then choose "Sort & Filter," and select the ZA button (or click it back to go AZ).

One of the benefits of using the RAND function is that every single time you enter the information you will be given a different result. RAND will set different number functions to each of the current cells containing information. As each cell has randomly generated numbers (which Excel is then able to sort through based on largest or smallest), you can return to the RAND formula and continually click the "Sort" button and you will receive new results every time.

Shuffling Data with Ultimate Suite

You may not have the time or the patience to continually sift through formulas and other content within Excel. This is especially the case if you have hundreds of entries you need to randomize. When this is the case and you want to know how to randomize a list in Excel, you can shuffle the data within the application using what is known as the Ultimate Suite. This form of sorting doesn't take much time to implement

Initiating the Shuffle

In order to set up the shuffle click the "Ablebits Tool" tab, and then select "Utilities." From there you'll want to select the "Randomize" button. This will bring up two options. The first is "Shuffle Cells," and the second is "Select Randomly." For this purpose you'll want to choose the "Shuffle Cells."

The shuffle information will load on the left side of your application screen. You will be given a handful of options for how you want to shuffle the information. The methods for shuffling are as follows:

  • Cells in each row (this shuffles the cells in each row individually)
  • Cells in each column (this randomly sorts out cells in each column)
  • Entire rows (this will shuffle rows in a range you select)
  • Entire column (this shuffles columns in a range you select)
  • All cells in the range

After selecting the shuffle method you are interested in, click the "Shuffle" button and the new output will be displayed for you.

Save Ahead of Time

It is highly recommended to save your spreadsheet before you continue with this. This is because when you begin to randomize some information, it may only randomize certain rows or certain columns. When this happens, it is breaking up sections of information you might want to keep together, such as a full row.

So, save your work and then, when you randomize the information, save it as a different file. This way, you always have a fallback document should you find some information is no longer in the correct locations.

Tips and Other Useful Information

Before you go about following these steps for how to randomize a list in Excel, you will want to make sure to read through these tips and suggestions. This will help ensure you always have the highest quality experience whenever using the shuffle features within Excel (or using the application).

Man using excel his laptop

image via:

Check after First Shuffle

It is easy to go kind of crazy with the shuffle feature. You might select the shuffle feature once and then click it again and again and again. You will receive different results every time, but you might also be throwing the rest of your spreadsheet out of whack. The RAND shuffle option is the best at avoiding this problem; but even then you want to shuffle information and then check the details after the first shuffle.

By looking at the information after the first shuffle you can see whether all the information you need to remain together stayed together. After all, if you are shuffling student names as a teacher, you don't want to accidently connect the grades of a different child, or combine last names. Attempting to undo this is a bit of a nightmare.

Instead, you want to save before you perform the shuffle, then check to see what happened with the information following the shuffle. As long as everything remained locked together as it should, you can continue with future shuffles; but again, save the file as a different file just in case you need to go back to the original document.

Selecting Entire Rows and Columns

There might be times where you are using the shuffle features and you just want to shuffle an individual row or column. Attempting to click and drag over the desired areas can get messy (and you may accidently move one number over to another cell without even knowing what you did). In order to make sure you have a nice and neat selection, it is better to use a keyboard shortcut.

First, click on the first set of a sequences you want to select. Now, hold down the CTRL and the SHIFT key. From there, use the arrow keypads in order to select the area you want have highlighted. This way you won't be clicking around areas and risk moving data.

Another way to perform this is to select the cell you want to begin with, then hold down the CTRL and the SHIFT button, then push the "*" button. This will bring up the ability to type in the data set points for what you want to select.

Display Formulas

If you want to view the formulas you have entered into Excel (including the RAND formula), there are a few ways to do this. First, you can click the "Formulas" tab at the top of the screen. This will provide you with all of the information you need.

From there you want to choose "Show Formulas" button. Of course, you
are always able to remove the RAND formula if you want it out. This way, by removing the RAND formula you won't be assigning randomized numbers to different cells.

​Freeze Rows and Columns

There might be a time where you want to avoid moving certain rows and columns within your Excel spreadsheet. You can do this by clicking the "View" tab at the top of the screen. From there, choose "Freeze Panes."

You have three options for what to freeze. You can choose "Freeze Pans" where you will select the panes you want to lock in position. Then there is the "Freeze Top Row" option and the "Freeze First Column" option.


There may be times where you want to view a list randomly in Microsoft Excel. Whatever the reason may be, entering a formula is simple, and it doesn't take long to do. By following these "how to randomize a list in Excel" instructions, you have the ability to view the spreadsheet in random ways. So the next time you are in need of randomizing your list, you'll have all the information you need right here.

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.


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 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. 


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 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.

5 New and Creative Ways To Utilize Excel

Excel may not always be the most exciting program to utilize at home or at the office, but it’s an essential one that keeps over millions of users organized and able to do their tasks with relative ease.

Whether you are relatively new to the world of Excel or you’ve been using it on a daily basis for over a decade, learning how to be creative and have a little fun will keep things a little more exciting and even improve your skills.

While many fun and creative projects in Excel are designed for school-aged users, who says that kids are the only ones who can have the fun?

Although we can’t begin to explain (or even understand) the creative process of Tatsuo Horiuchi’s Excel masterpieces, here are five things to try out the next time you use Excel (but you might want to make sure you try it out on your break if you’re at work).

Create Patterns For Your Hobbies

create hobby lists

Image via Tech Advisor

Do you enjoy quilting or knitting? Have you been thinking about creating a mosaic out of tile for your outdoor patio? While the design might be a little different, depending on what your pattern or project is, an Excel spreadsheet can make the whole process easier.

While the steps for this design chart are geared towards knitters who come up with their own patterns, you could use it for various projects that require making a small scale pattern or just staying organized.

The steps are simple and are as follows:

  • Open your workbook and create a copy of the “master” tab
  • Continue by right-clicking on the tab and select “move or copy” on the menu
  • The next step is to “make a copy” and then click Ok

After you add letters, colors, and symbols to the cells (to represent each stitch/element of the pattern), save the file. Like other spreadsheets, you can edit easily until you create the perfect design.

Make A Calendar

These days there are plenty of programs and templates that allow you to customize and print off a calendar, but if you don’t know how to make a calendar using Excel now is the time to learn. Despite living in a world where you can sync calendars with someone else, by just using your phones, a calendar can still come in handy.

Follow these steps to make a calendar that you can use every month and edit as needed.

Step 1

Using the merge-and-fit feature and setting the width of your columns at the same time, you will create the weekday header and a title for each month. You can enter the weekdays by entering in one day, such as Sunday, and then drag the cell to the right using the bottom right corner.

Tip: For an easy-to-read text, make the days of the week a 12-point font and bold. If your weekdays extend beyond the size of the column all you need to set the width to 15 or 20 (Home to Cells to Format to Column Width).

Step 2

After your weekday headers are set up, you can add the month to the top of the spreadsheet. Many people spend a lot of unnecessary time manually entering information into Excel. Since Excel is all about being efficient, you can use the formula “=TODAY()“, this tells Excel to use the current date.

Step 3

The next step is to format the title with a bold font of 20 or 22 and then (Home to Format to Format Cells to Date and then select the correct month title).

If your spreadsheet looks unbalanced, you can easily fix the problem by highlighting all the cells above the weekday header and use your Merge & Center button; everything should look centered now.

Step 4

Now that you have your weekday and month headers taken care of you can make the calendar template. Use the Merge & Center feature again but use some extra empty cells (about five or six) to create a large cell for a calendar square.

When your large cell is highlighted, you’ll copy and paste into the other days. This step duplicates a box for every day of the week, and the result ends up looking like a rough draft of a calendar grid.

Step 5

Ready to add grid lines? Highlight the entire calendar, click on the grid tool in your home tab and then select “All Borders,” this should create a more polished look. Now you can manually add dates, notes, and other information. Your calendar is ready to share or print.

Create Your Own Crossword Puzzle

creating a crossword puzzle on excel

Image via YouTube

How many times have you done the daily crossword puzzle and thought you could easily make on yourself? Whether you want to make a special crossword puzzle for your kids or you just want to be like your crossword creator idol, Will Shortz, you can create a puzzle using Excel.

Before you decide to create a puzzle in Excel, it’s best to create one on paper just to get a general idea of what you want.

Step 1

After you start a new document in Excel, select a number of rows to equal one more than the vertical size of your crossword grid. If your grid is 31 spaces, select 32 rows in Excel.

Step 2

Right-click in A1, then select Row Height. You will enter 20 and then Ok. Follow the first step for the horizontal size of your puzzle. Then right click A1, Column Width, and enter 2.5, then Ok.

Step 3

Starting at B2, select the grid area and then click on the Borders button (which is in the Font box). Select All Borders.

Step 4

Type in your answers to the puzzles and make sure that you only use one letter per cell. Make sure the letters match up, as you sketched on your paper draft.

Step 5

Holding down the Control key, click each of the empty cells. Then click on Fill Color button, choose Black, Text 1.

Step 6

After you fill in all the empty cells, select the grid and delete all of the answers you typed in. Using a small font, type the clue number in the first cell of each answer space. Next, you can type your clues (one per cell) to the right of your grid.

Create A Sudoku Puzzle

There are easily just as many Sudoku puzzle fans than there are crossword puzzle fanatics, and there are probably many who love both. If you love Sudoku, but hate when a co-worker steals that section of the daily paper first, you can create your own to print out for the office (or keep for yourself).

You can take the easy route and download a program specifically for Excel, or you can watch some tutorial videos to learn how to create your own puzzles. Either way, it’s a fun way to use Excel in your downtime.

Make Your Own Trivia

Need to help your kids with their homework and want to make it more interesting or are planning a DIY Trivia Night with some friends? Use your Excel spreadsheet to create your own trivia game. Follow these steps to become the next Trivia Master:

Step 1

Open a new worksheet, naming one sheet, Quiz and the other, Answers.

Step 2

In B1 of your Quiz sheet, type the number of questions you want and in B2, title it Your Score. In Row 4, create Question, Answer, and Result (or something along those lines).

Step 3 

In Column A, type the questions (one per cell) then type the correct answers in the corresponding cells on the Answers worksheet. If you need to, expand column A, so it’s easier to read the trivia questions.

Step 4

To create a formula to check the answers, type =IF(B5””,IF(B5=Answers!A5,”Correct”, “Incorrect”),””) into cell C5. This formula also works for scorekeeping. 

In the section, Number of Questions, use the following formula:


In the section for the score, use this formula:


Step 5

After you’re done entering the formulas, you can format your worksheet by selecting cells A4 to column C and format as a table. To get rid of any unwanted table features, go to the Design Tab and then to Convert to Range.

To keep your answers secure, you can go to column B and right click on Format Cells and then Protection Tab. You can either choose to unlock for editing or lock to keep your answers secure.

For additional tips on creating a trivia game on excel, visit this link.

Related Posts Plugin for WordPress, Blogger...