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/Warnings

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.

Conclusion

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.

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.

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: pexels.com

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

Conclusion

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.

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

=COUNTA(A:A)-1

In the section for the score, use this formula:

=COUNTIF(C5:C5000,”=Correct”)

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.

The 20 Most Useful Excel Shortcuts to Utilize

Learning the shortcuts on any program can cut the time it takes to produce a document. With a program like Excel that has so many uses, knowing some of its shortcuts can make navigating, entering formulas, and setting up worksheets quicker to do.

Reasons to Use Shortcuts

Excel has many uses, although creating spreadsheets is probably its most common one. Within a spreadsheet, both written and numerical data is often used, so learning some of the shortcuts for both types of data can help save time when trying to organize information.

keyboard with shortcuts on it

Image via Keyshorts

Using shortcuts takes less time because you won’t need to use the mouse to go to the ribbon to find the function or formula that you need. Also, by memorizing the shortcuts, your work will be more precise. You will be less likely to make mistakes that will need to be corrected by yourself or someone else.

Creating spreadsheets can be a tedious job, especially if there are large amounts of data to organize. However, by learning Excel shortcuts, the task can become easier because it will be quicker to do, and your accuracy will improve as well.

Here are 20 Excel shortcuts for Windows and Mac that can help you work more efficiently.

Workbook Operations

When you’re ready to start a new worksheet or continue working on one, these shortcuts can help open a current workbook or a new one and save it when you’re done finished entering information on it.

Open Workbook: Ctrl + O (Windows and Mac)

This shortcut helps you open a workbook on which you’ve been working. Once you press the keys, the recent workbooks box will appear, and you can choose the one you need.

New Workbook: Ctrl + N (Windows and Mac)

If you need to open a new workbook, this shortcut works for both the Windows and Mac operating systems.

New Worksheet: Shift + F11 (Windows and Mac)

This shortcut adds a new worksheet to a workbook.

Save As: F12 (Windows)  ⌘+ Shift + S (Mac)

The F12 function key helps you save the worksheet or workbook on Windows. The combination of the keys shown above will do that on a Mac computer. After the keys are pressed, the dialog box opens so you can insert the name of the workbook.

Close Excel: ALT + F4 (Windows)   Ctrl + Q (Mac)

When you’re finished with Excel, this shortcut closes the program, not just the workbook.

Ribbon Operations

If you’re new to Excel, you may not be familiar with the ribbon. The ribbon is the box above the worksheet that displays the tabs and buttons for the various commands on the application.

Show or Hide Ribbon: Ctrl + F1 (Windows)   ⌘ + OPT + R (Mac)

Use this shortcut to open or hide the ribbon box.

Show Ribbon Accelerator Keys: Alt (Windows)   n/a (Mac)

The accelerator keys are other shortcuts on Excel. When you select Alt on a Windows computer, a letter or number appears under the tabs on the ribbon. These include the:

  • File
  • Home
  • Insert
  • Page Layout
  • Formulas
  • Data
  • Review
  • View
  • Help
  • Save Icon

When you press the letter or number under the tab, letters or numbers will appear under the commands for that task. You can then use shortcuts to do whatever you need.

For example, when you press Alt and W under the View tab, a Q appears under Zoom. Then, selecting Q allows you to enlarge the information on the worksheet.

Editing Operations

After you use some of the shortcuts, you may want to make corrections by undoing something, or you may want to copy data to paste it into another row or cell. These shortcuts are self-explanatory.

Copy: Ctrl + C (Windows and Mac)

Paste:  Ctrl + V (Windows and Mac)

Undo: Ctrl + Z (Windows and Mac)

Cut: Ctrl + X (Windows and Mac)

Spellcheck: F7 (Windows and Mac)

Formatting Operations

Some formatting functions will be used more than others, so the most common ones are listed here. These shortcuts don’t need any further explanation either.

Bold: Ctrl + B (Windows)   ⌘ + B (Mac)

Italic: Ctrl + I (Windows)   ⌘ + I (Mac)

Underline: Ctrl + U (Windows)   ⌘ + U

Data Editing Operations

If you need to fill the same information in other cells or rows, these shortcuts will help do it.

Fill down from cell above Ctrl + D (Windows and Mac)

This shortcut allows you to fill cells with the same information from the cell above it. So, if you’re tracking inventory and the costs for a group of products are the same, you could use this shortcut to fill in the information on the worksheet.

Fill right from the cell to the left: Ctrl + R (Windows and Mac)

If you need the same information from a cell to the left of the one you’re on, this shortcut allows you to copy the information to the cell.

Find and Replace: Ctrl + F (Windows and Mac)

If you need to find information on a worksheet, this shortcut allows you to find it, and replace it with new data if necessary.

Calculations Operations

Probably the most common calculation on an Excel worksheet is addition, followed by multiplication. This shortcut allows you to add or multiple cells or rows or insert functions.

Insert Autosum Formula: Alt + = (Windows)   ⌘ + Shift + T (Mac)

Insert Function: Shift + F3 (Windows and Mac)

There are 200 Excel shortcuts in Windows and 200 for Mac, but these 20 are probably the most popular and frequently used shortcuts for people who use the application at work.

How to Memorize Excel Shortcuts

excel shortcuts on green background

Image via Udemy

The way to learn anything is by doing it. While some people are visual learners, most people memorize physical tasks better by doing them and using shortcuts on a keyboard is a physical task.

Some of the shortcuts used in Excel are also applicable to other Microsoft applications. For instance, you can use the find and replace shortcut, Ctrl + F, with MS Word. A new word document can also be opened in MS Word by using the shortcut Ctrl + N. Using F7 allows you to spellcheck on Excel and MS Word.

The duplication of some of the shortcuts can make them easier to learn and apply to applications. However, if you need more guidance as you learn shortcuts for Excel, there are other resources.

Laminated Cards

Although many of the shortcuts are easy to learn, some of them may be harder because you don’t use the function or formula very often. Instead of hunting for the correct way to perform an action or looking it up on the Internet, you can purchase laminate shortcut cards and keep them on your computer.

The laminated cards include all the shortcuts for both Windows and Mac computers listed together. Since they are laminated, the cards can last for as long as you need it. The cards are available from online retailers or stores that sell office supplies.

Online Classes

If you want to learn more about Excel, including the shortcuts, several websites offer Excel tutorials or classes. The website corporatefinancialinstitutite.com offers the free Excel Crash Course that includes learning the shortcuts, functions, formulas and other tips.

The site excelexposure.com also offers free Excel lessons and has a free shortcut tip sheet available on their site. There are several other websites that also offer free Excel courses or tutorials that include learning the shortcuts you use every day.

Write Shortcuts Down

Since you may only use a few of the shortcuts, write down those you use the most to help you learn them. Writing things down helps many people remember information better than just reading it. A study published in Psychology Today says that writing down information boosts memory and the ability to retain concepts.

Write the shortcuts you use most horizontally on a piece of paper, cut it out and then tape it to the top of your monitor. It can be used as a reference in case you need it, but you will probably retain many of them after you’ve written them and used them at work every day.

Learning the shortcuts that you don’t use often can be more difficult, but if you print out a tip sheet or buy the laminated sheets, you can keep them nearby for reference when and if they are needed. Some of them you may not ever use, but it is better to have the information nearby in case you need it.

By learning Excel shortcuts, such as the 20 listed above, you will be able to produce spreadsheets faster and more accurately than you did before. Using a mouse and the ribbon to complete actions on a worksheet takes more time, and if you are unsure of how to perform an action on Excel, it can be inaccurate.

However, by taking the time to learn the shortcuts you use the most, you could be confident that you’re performing the right and your work will be more precise.

how to unhide columns in excel

Learn How to Unhide Cells in Excel Using Keyboard Shortcuts or the Home Menu

Learn how to hide and unhide columns in Excel using keyboard shortcuts or the Home Menu methods.

Today’s post will illustrate how unhide columns in Excel, as well as hide them.

how to unhide columns in excel

How to Hide and Unhide Data in an Individual Cell

While Excel does not allow you to Hide and Unhide individual cells using the Hide/Unhide command, here’s a trick showing how to hide just one cell:

  1. Choose the cell or cells you want to hide
  2. Select Cells from the Format menu and the Format Cells dialog box will appear
  3. Select the Number tab
  4. From the list of format categories, select Custom
  5. Enter three semicolons (…) in the Type box

This causes the information in the cell to disappear, and it won’t print. However, you will be able to see the cell information in the Formula Bar. To unhide that individual cell, enter any other type of information.

Hiding Data in Columns and Rows

Hiding data in columns and rows still allows you to reference the data in formulas and charts. Also, hidden formulas that contain cell references will still update if the data in the referenced cells changes.

How to Hide Data in Excel Using Shortcut Keys

First, we’ll discuss how to hide columns and then we will discuss rows.

How to Hide One or More Columns

The shortcut keys for hiding columns is: [Ctrl] + [zero]. Here are the steps:

  1. Choose any cell in the column you want to hide, making it the active cell
  2. Press and hold Ctrl
  3. Press 0 [zero] while holding Ctrl
  4. The entire column with the active cell and any data it contained, will be hidden

Note: Hide multiple columns with this shortcut by highlighting at least one cell in each column you wish to hide, then repeat steps 2 and 3 above.

How to Hide One or More Rows

The shortcut keys for hiding rows is: [Ctrl] + [9]. Here are the steps:

  1. Choose any cell in the row you want to hide, making it the active cell
  2. Press and hold Ctrl
  3. Press [9] while holding Ctrl
  4. The entire row with the active cell and any data it contained is hidden

Note: Hide multiple rows with this shortcut by highlighting at least one cell in each row you wish to hide, then repeat steps 2 and 3 above.

How to Hide Columns or Rows Using the Home Menu

This method has three options on how to unhide columns in excel, depending on the object selected when the menu is accessed.

To Hide a Single Column or Row

  1. Click on the header of the column or row that you would like to hide and the column or row will be highlighted
  2. On the Home tab, in the Cells group, select Format
  3. Under Visibility, select Hide & Unhide, and then Hide Columns (or Hide Rows)
  4. Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box
  5. The selected column or row and any data is hidden (the header is also be hidden)

To Hide Adjacent Columns or Rows

To hide two or more side-by-side columns or rows:

  1. In the column or row header, click and drag across all of the columns or rows you want to hide
  2. On the Home tab, in the Cells group, select Format
  3. Under Visibility, select Hide & Unhide, and then Hide Columns (or Hide Rows)
  4. Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box
  5. The selected column or row and any data is hidden (the header is also be hidden)

To Hide Non-Adjacent Columns or Rows

  1. In the column or row header click on the first column or row you want to hide
  2. Press and hold Ctrl while clicking once on each additional column or row you want to hide.
  3. Release Ctrl
  4. On the Home tab, in the Cells group, select Format
  5. Under Visibility, select Hide & Unhide, and then Hide Columns (or Hide Rows)
  6. Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box
  7. The selected column or row and any data is hidden (the header is also be hidden)

How to Unhide Columns in Excel

To Unhide All Hidden Rows and Columns Simultaneously

  1. Select all of the cells by pressing Ctrl+A or the gray Select All button in the upper left corner of the worksheet.

Note that if your worksheet has data and the active cell is above or to the right of the data, Ctrl+A selects the current region. Press Ctrl+A again to select the entire worksheet.

  1. On the Home tab, in the Cells group, select Format
  2. Do one of the following:
    • Under Visibility, select Hide & Unhide, and then Unhide Columns (or Unhide Rows)
    • Under Cell Size, click Column Width or Row Height, then type the value that you want in the Column Width or Row Height box
  3. Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box

To Unhide the First Row or Column of Your Worksheet

If you’ve hidden the first row or column, take the following steps:

  1. Select the first row or column using one of the following:
    • In the Name Box next to the formula bar, type A1
    • On the Home tab, under Editing, click Find & Select > Go To. Type A1 in the Reference box, then click OK. 
  2. On the Home tab, in the Cells group, select Format
  3. Under Visibility, select Hide & Unhide, and then Unhide Columns (or Unhide Rows)
  4. Under Cell Size, click Column Width or Row Height, and then type 0 in the Column Width or Row Height box

If you want to learn how to freeze cells in excel so rows and columns stay visible, click here.

average function in excel

How to Use the Average Function in Excel

Excel makes it easy to figure out the average of a group of numbers, no matter how large or small. It makes it easier for you to analyze important data. You will learn how to use Excel’s “average” function right here.

Most of us are familiar with average values. They offer a great way, to sum up information in a single number. Which gives us an immediate picture of any dataset.

If you have a large set of data, Excel can help you to find statistical values such as the average.

Not only can this help to enhance your understanding of a dataset, but it can also make information easier to present to supervisors, investors, and even loved ones. Using the AVERAGE function in Excel is easy and takes just a few clicks of the button.

In this tutorial, we’ll show you:

  • How to Use Average Values
  • Calculating Averages in Excel
  • Common Examples of the AVERAGE function

Using the average function in excel

What is an Average Function in Excel?

The average of a group of numbers describes the central value of the set.

The first thing to remembers is that using averages can help us to draw generalizations from sets of data.

There are three different ways that are commonly used to measure central tendency:

  • First is the Average: You can calculate the mean of a group of numbers by adding each value together and then dividing by the total count of those numbers. For example, in the group 2,3,5,5,5, the average is (2+3+5+5+5)/5=4. The average of a group of numbers is highly susceptible to outliers.
  • Second is the Median: The median is the number that lies directly in the middle of a set of numbers. For example, in the group 2,3,5,5,5, the median is 5.
  • The third is the Mode: The mode is the number that occurs most frequently in a set of numbers. In the example group of 2,3,5,5,5, the number 5 appears three times, making it the mode.

When Do Averages Come in Handy?

Averages aren’t just a part of your grade school math curriculum.

There are plenty of applications for averages in the real world, both at home and in the office. Notably, you can use averages to draw conclusions about your budget, your grades, yearly earnings, and even your car’s gas mileage too.

With this in mind, there are countless ways that averages can come in handy. However, you may want to look at different values depending on your situation.

The mean is best used with datasets that contain information that’s evenly spread, such as bell curves.

Extreme outliers in either direction can skew results and lead to false conclusions about central values in a dataset. It is important to realize that when outliers are in play, you should stick to using the median to represent the central value.

The mode works well for smaller sample sets. That is where there’s not enough data to draw relevant conclusions by calculating the mean or median.

The Basics of Excel’s Average Function

The AVERAGE function in Excel returns the mean number for any data set as opposed to the median or mode. In addition, it can be used as a worksheet function, making it easy to enter as part of a formula in a cell.

Your average will depend on the cells that you highlight. For this reason, it may be best to omit any statistically insignificant outliers. A value that’s too high or not high enough can skew your results, giving you an inaccurate picture of your dataset as a result.

Using the Average Function in Excel

Using the AVERAGE function is simple enough for even Excel novices to master in a matter of minutes. As a worksheet function, all you need to do is enter the formula correctly into a free cell to get a mean value.

The syntax you should use to find the arithmetic mean of a data set is:

AVERAGE (number1,[number2], …)

There are very few components that you’re required to input into this formula. There’s only one parameter that you really need to fill out in order to yield an accurate result.

  • Number1: This field is required. You can put in a cell value or a range of cells for which you want the average.
  • Number2: This field is optional. If you want to analyze additional cells or ranges, you can add up to 255 more to the AVERAGE formula.

If any of the cells in the range you highlighted contain text, logical values, or are simply empty, the AVERAGE formula will ignore these. Cells that contain a zero, however, are included. If you want to omit any cells within a row or column, you should leave them blank.

If the AVERAGE function isn’t coming up with the results that you’re looking for, then you may want to consider modifying the formula slightly.

The AVERAGEA and AVERAGEIF Function

The AVERAGEA function allows you to include logical values and text representations of numbers in a reference as part of your end result. This also gives you more control over formatting.

Moreover, you can also combine the AVERAGE function with the IF function to limit the range of values used to calculate a result.

Excel’s IF function allows you to see whether a data point meets a condition that you specify. For example, being greater than another number or occurring within a given timeframe.

The AVERAGEIF function gives you the power to calculate a mean using only values that meet certain criteria.

The Average Function in Action

Here are some examples of common formulas used to find the average of various sets of data:

  1. Single column – Average the last few values in a single column: =AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
  2. Different columns – Average the last few values in different columns: =AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
  3. A range of numbers – Average the last few values in a range of numbers:

{=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),{1,2,3…}),ROW(data), data))}

Average the top scores in a data set: =AVERAGE(LARGE(range,{1,2,3…}))

Conclusion

Ultimately, average values are an important part of everyday life. It helps us to quickly and easily understand even large datasets by giving us the central value of a group of numbers. Excel makes it easy to calculate mean values using the AVERAGE function.

Finally, we hope that this tutorial has helped you to better understand how to use the AVERAGE function in Excel.

Indeed the average function in Excel is such a powerful tool when it comes to analyzing data.

Do you need help in freezing columns in Excel? Here is a tutorial on how to do that.

Select Excel’s Used Range on a Mac

I recently read a good blog post over at Contextures about selecting the actual used range on an Excel sheet, both manually and with VBA. However, using Excel on a Mac makes you keenly aware that there’s no Home button.

The used range on a worksheet starts with cell A1 and ends with the last used cell in the worksheet. This “last cell” is not always apparent, but easily found. Just use the keyboard shortcut CONTROL + G to bring up the Go To dialog box.

Go To dialog box
Click Special… which will bring up the Go To Special dialog box.
Go To Special dialog box
Select Last cell and click OK.

The last cell may sometimes surprise, because Excel considers cell formatting as being “used” so you may see blank cells that are way outside your data range. Tip: Sometimes you can delete the seemingly extra rows and columns outside your data range and it will reduce the file size.

Select the Used Range by Navigating Back Home

Once you find the last cell, you can then hold the Shift key down and click cell A1 to select the entire range. Of course if you can’t see cell A1 in the current window there is no Home button on the Mac to help you out. (Major bummer)

The next best thing is to hold the COMMAND + Shift keys down while you tap the left arrow and up arrow keys until you reach cell A1. This can be simple, or time-consuming depending upon size and shape of your worksheet.

Selecting the Used Range

To select the entire used range with VBA is a simple matter. Choose Tools > Macro > Visual Basic Editor, then choose View > Immediate Window, and type activesheet.usedrange.select inside the immediate window and hit enter.

Create a Macro to Select the Used Range

You can also create a macro to select the entire used range by opening the VBA Editor, inserting a Module, and entering the following code.


Sub ActiveSheetUsedRange()
ActiveSheet.UsedRange.Select
End Sub

Or you could get fancy with this code.


Sub SelectUsedRange()
Dim rng As Range
Set rng = Range("A1").SpecialCells(xlCellTypeLastCell)
Range(Cells(1, 1), rng).Select
End Sub

Add a Keyboard Shortcut for the Macro

To make things simpler to run the macro, you can enter a shortcut. Just choose Tools > Macro to bring up a dialog box.

Macro dialog box

To add a shortcut, select a macro and click Options… which will bring up the Macro Options dialog box.

Enter a shortcut key by clicking inside the Shortcut key box and pressing a key on the keyboard. I pressed the “u” key on the keyboard, and consequently the keyboard combination is shown as Option + Cmd + u, as you can see in the screen shot. (Depending on the key, you may also include COMMAND, Control, Shift into your shortcut.)

Macro Options dialog box

Enter a description if you wish, and then click OK. Select the Cancel button on the Macro dialog box to make it disappear.

Now you can run the macro by simply using the keyboard shortcut Option + Cmd + u.

Note: This macro will not work if a Chart sheet is selected.

Used Range verses Actual Used Range

The actual used range might be different than the used range. Meaning that some blank cells that are formatted might be included in the used range. Most likely you will only want to deal with a range that has some actual values. This would be the actual range.

Please refer to the aforementioned blog post over at Contextures to see a couple of different examples of code that you can use to select the actual used range. These examples are short and use the VBA FIND function to get the job done.

Settings and Shortcuts for Excel 2003

I recently found myself with a new, loaner laptop and a brand new copy of Excel 2003 as my default spreadsheet program. This was fine until I realized there were a number of things “missing.” Like shortcuts and settings that I’ve changed over the years to make Excel serve me, instead of the other way around.

So here’s my list of things I do to “normalize” Excel 2003.

Full Menu’s

My number one pet peeve with Excel is they “automatically customize menus and toolbars based on how often you use the commands.” But my question is, “How do new users know what menu commands are available if they’re hidden?”

I like to use “full menus” so you see ALL menu commands each time each time you click a menu. This is a standardized approach; you see the same thing each time you click a menu.

Here’s how it’s done:

    Right click the toolbar
    Click Customize…
    Click the Options tab on the Customize dialog box
    Check Always show full menus
    Click Close

Customize Dialog Box

General Options

I have only two recommendations here: maximizing the recently used file list and minimizing the number of worksheets in a new file.

General Options Settings

Recently used file list (9)

I like to set the recently used file list to the maximum number. In Excel 2003 that number is nine. This saves you time when searching for a recently used file.

Here’s how it’s done:

    Choose Tools > Options then click the General tab on the Options dialog box. Change the Recently used file list to 9 and make sure there’s a check in the check-box.

Sheets in new workbook (1)

When creating a new spreadsheet file, how many sheets do you actually use? How many times have you looked over a spreadsheet file from someone else and clicked on those bank sheets to see if they contained anything?

Remember the days when a new file had 16 worksheets as the default? Excel 2003 has just three. That’s two to many. I like to set the number of worksheets in a new file to one. If I need another, they’re easily created.

Here’s how it’s done:

    Choose Tools > Options then click the General tab on the Options dialog box. Change the Sheets in new workbookto 1.

Shortcuts on the Toolbar

These custom toolbar buttons are necessary when using Excel 2003: Paste Values, Freeze Panes, Current Region, Auto Filter, and Pivot Table. You can easily customize the toolbar to add these and more.

My Custom Toolbar Icons

For each addition to the toolbar you’ll need to access the Customize dialog box. The long way is to choose View > Toolbars > Customize… or the short way is to right-click a toolbar and select Customize… from the pop-up menu.

Once the Customize dialog box is open, select the Commands tab. Now your ready. Here’s my favorite custom toolbar commands.

Paste Values

The very first custom toolbar command icon I put up. Indispensable. Well, almost.

Here’s how it’s done:

    In the Categories pane select Edit. In the Commands pane scroll down and find Paste Values. Click and hold the left-mouse button down while dragging the icon to a toolbar.

Paste Values Command

Freeze Panes

Another indispensable toolbar command button shortcut icon for anyone who works with lots of data in proper tables.

Here’s how it’s done:

    In the Categories pane select Window and Help. Scroll down until you find Freeze Panes. Click with left mouse button, hold and drag to the toolbar.

Select Current Region

Here’s a neat button that allows you to find the shape of a data region by selecting it for you automatically. Better than the keyboard shortcut Ctrl+Shift+asterisk (*). Reminds me of the CurrentRegion property in VBA.

Here’s how it’s done:

    In the Categories pane select Edit. In the Commands pane scroll down to the bottom and find Select Current Region. Click and hold with the right-mouse button while dragging to a toolbar of your choice.

AutoFilter

A great button to save you some time when filtering tables. If the active cell is on the header row it simply turns on the filter. However, if you select a cell in the data that contains something you want to filter, clicking the AutoFilter will turn on AND filter that selection for you. A one step process that saves time.

Here’s how it’s done:

    Select Data from the Categories pane. Click the AutoFilter command with the left mouse button, hold and drag to a toolbar.

PivotTable

This command serves to initiate a PivotTable from a data table. It also brings up the PivotTable and PiotChart Wizard when you are working on an active PivotTable.

Here’s how it’s done:

Select Data from the Categories pane. Scroll down to the bottom of the Commands pane and find PivotTable and PivotChart Report icon. Left-click, hold and drag to a toolbar.

More Stuff

Find your favorite command not shown on a Toolbar and load it up. Pronto!

That Damn Delete Key in Excel for Mac

Where is the delete button on Mac - ExcelI have no earthy idea why it took me so long to figure out how to delete the contents of a cell or range in Excel for Mac. Ever since I bought my MacBook Pro I’ve known the Delete key on a Mac isn’t really a Delete key.

I mean, since my background is with Windows, I have ingrained knowledge on how the Delete Key works on a computer. Ingrained, I tell you.

But all of that knowledge was shattered upon getting a Mac.

Where Is the Excel Delete Button on Mac

After some consternation, I learned where is the delete button on a Mac. To press the delete button on Mac computers you have to hold down the fn key and the Delete key at the same time when you want to delete something on a Mac. (Skip to video)

After a while, you get used to the idea that the Delete key on a Mac is really a backspace key and using fn+Delete gives you the real Delete key action. 🙂

Of course if you’re a long time Mac user you probably think I’m cuckoo. But hey, this is my blog, think what you like. I’m not the only one who’s decided to start using a Mac after a lifetime of Windows abuse use.

Excel for Mac

Anyway, when using Excel on a Mac — I’ve got versions 2008 and 2011 — you run into a learning curve with all the unusual shortcut keys, function keys (1, 2), and menu and ribbon things that are different from the Windows version of Excel. So there’s a tendency to forget about how the Delete key works on a Mac.

I mean, this is Excel we’re talking about here. Hitting the Delete key is supposed to delete the contents of the active cell, for cryin’ out loud.

In Excel for Mac it does that, but the cursor also gets stuck inside the cell in edit mode. You have to hit the enter key to finish deleting the contents, but this act also moves the active cell to the next cell down.

And if you’ve selected a range and hit the Delete key, the active cell contents are deleted and the cursor is stuck inside the cell in edit mode. You have to hit the Enter key, which does nothing but take you to the next cell. The range contents are still there, with the exception of the active cell.

Not the kind of behavior that occurs in Excel for Windows.

How to Delete Cell and Range Contents in Excel for Mac

The trick is to remember that fn+Delete is really a keyboard shortcut to the Delete key on a Mac. Then the world rights itself and the planets align. Frustration abates. You’ve finally found the magic. Your mojo is back!

Watch this 54 second video to see what I’ve been babbling about for the past 454 words.


YouTube link

Related Posts Plugin for WordPress, Blogger...