how to round in excel

How to Round Numbers in Excel: What You Need to Know

Use Excel’s rounding feature to round decimals or large numbers in a spreadsheet. Round with functions, round up, round down, or round numbers to a certain decimal place in Excel. In this article, we’ll show you how to round numbers in excel. If you use Excel a lot, you have probably noticed that it is frequently used to organize data in numerical form. While this is very helpful, sometimes you don’t really want to work with numbers like decimals. In certain cases, rounding your data up best suits your needs. In this guide, we are going to learn how to use Excel to do the rounding for us! Don’t sweat it. Using this feature is much simpler than you may think.

What is the Excel Round Function?

Have you ever heard of the Excel round function? Before we dive into how to round numbers in Excel, we need to address this question. While learning how to use different aspects of Microsoft software can often seem daunting, it is more simple than you may expect. The Excel round function brings a number to a given amount of rounded digits. You can round either to the left or the right of a decimal point.

how to round in excel

Why Should We Round?

To keep it simple, rounding numbers just makes them easier to deal with. First of all, if you are working with decimals, it can be such a pain to record them. Even worse, if you are presenting these numbers to someone out loud, reading decimals can get tedious.

Not just decimals are rounded, though. Whole numbers can be rounded too, most often to the nearest 10. Rounding is often used in business when dealing with money amounts or sales.

Instead of reporting in a meeting, “we sold $5,999,956.97 worth of products this year,” it is easier to say “we sold about $6 million worth of products this year.” If a business is looking for an estimate of numbers, they can easily use Excel to do so.

How to Round Numbers in Excel

Let’s say a company is trying to figure out its estimation of sales for the week by the transaction. Each transaction has a very different and specific number, but the company is just looking for rounded numbers. All of the sales are listed in Excel. How can we round them?

We’ll use a shortened version of their sales list as our example. Here are some examples of the types of numbers we are dealing with:

  • 56.09
  • 22.31
  • 43.33
  • 90.01
  • 87.55
  • 15.78
  • 25.36
  • 38.72

Using Functions To Round Numbers In Excel

Start by opening a new Excel sheet and listing your numbers in the first column. Excel uses functions to round. With the rounding function, you can use =ROUND, =ROUNDUP, and =ROUNDDOWN. For our first example, we are going to use the =ROUND function.

To begin, we are going to start by rounding just our first number, 56.09. Select the cell where you want the new number to appear.

For example, it may be best to put the new number directly next to the old number.

In the function bar, type =ROUND(A1,0) and hit Enter. A1 represents the cell we are round, while the 0 represents how many decimal places we would like to round to. Since we want to get rid of our decimals, we are typing 0. Our new number will appear in the cell. In this case, the number is 56.

Additionally, you can use the =ROUNDUP function to round up to the next whole number, as well as the =ROUNDDOWN function to round down to the next whole number. Let’s do this with our second number, 22.31.

In the cell directly next to 22.31, type =ROUNDUP(A2,0). This will take cell A2, or 22.31, and round it up to the next whole number. Hit Enter, and you will get 23. In the next cell over, type =ROUNDDOWN(A2,0) and hit Enter. This will round 22.31 down to the next whole number, or 22.

If the Numbers have Several Decimal Places

If you are working with numbers that have several decimal places, such as 45.6726865, you may want to narrow these numbers down to just 2 decimals.

To achieve this in Excel, list your numbers in column A. Select the column next to your first number and type the function =ROUND(A1,2). This will round your number to 2 decimal places instead of its high number of decimal places.

You can complete rounding actions with any number of decimal places you desire, like simply by changing the second number in your function.

Rounding Multiple Numbers at Once

If you are working on rounding numerical data, there is a good chance you are probably working with a lot of numbers. It wouldn’t be very convenient if you had to go through and round each of those numbers individually.

Luckily, Excel makes it easy to round multiple numbers at once while still using its rounding function.

There is a simple way you can accomplish rounding all of your numbers.

The way to do this is to list out all your numbers in the first column. Then select the cell next to your first column and enter the rounding function you want to use for all of your numbers.

Let’s use =ROUND(A1,2). Hit enter to get the result for that number. Then, select that same cell and move your mouse over the bottom, right-hand corner of the cell until you see a plus (+) sign. Click and drag down until you reach the last cell you filled out.

Excel will automatically drag your function formula down through the selected cells. It will then perform the function for each number you have listed, giving you your result in the rounded column.

It is also possible to drag the function down past your entered information.

Zeros will appear where there is no information in the first column. Additionally, if you need to enter more information, anything entered in column A will automatically incorporate the function in column B; the zero will then change to reflect the newly entered number.

How to Round Numbers in Excel: Final Review

Numbers can be difficult to deal with in general, but when we add decimals to the mix, sometimes we are asking for trouble. Rounding can be difficult, especially when you are using lots of them or very large numbers with several decimal places.

For a quick, easy solution to your rounding problem, follow this guide on how to round numbers in Excel. You will be a rounding expert in no time, and you’ll save yourself a lot of effort!

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.

creating graphs and charts using excel basics shown on the laptop

Sort Your Life into Neat Little Boxes with These Excel Basics

I’ve Heard of Excel, But I Don’t Know What It Is

Excel is like that one coworker whose name you never learned until it was too late to ask. You’ve seen Excel around. Maybe you’ve seen it on a job description or you know it as the little green icon on your computer you never click.

Excel icon with question marks around

Image via Pixabay (altered)

What does it actually do though?

Excel is part of the Microsoft Office collection of applications designed to for the workplace. You may be familiar with some other apps in the collection like Word or Powerpoint.

These other apps are self-explanatory. Word helps you write words. Powerpoint helps you make your point, powerfully. Excel is the weird one.

What does it help you excel at? Does it make you faster? Does it make you smarter? Does it make you better at movie trivia?

It does all those things. Excel is a tool for organizing data. It gives you a functionally infinite number of little boxes to fill with numbers, words, or any other piece of information you choose.

school teacher giving excel basics lesson

Image via Pixabay

Beyond that, it gives you tools to track, calculate, and predict many ways that information can intersect. When you learn the Excel basics, it takes care of all the small ideas so you can worry about the big ones.

That Sounds Great, What Does It Mean?

Excel lets you build spreadsheets, which consist of numbered columns and lettered rows. It’s like a bingo board but a lot more useful (unless you’re extremely lucky at bingo).

via GIPHY

Typically and in its most simple form, one set of data goes in the columns and the corresponding set goes in the rows. This gives you the power to communicate a connection between two ideas without using words.

via GIPHY

We do this all the time without realizing it. Say you’re making a shopping list. You wouldn’t write out in full sentences everything you need and how many of each item you need.

You would list every item you need and write the quantity of that item needed next to it. Excel makes it possible to do this on a greater and much more complex scale.

It’s a simple and effective method of bookkeeping in almost any scenario. It also creates an easily readable and searchable archive of all your data.

Remembering Things Is Boring and Hard, So Why Do It?

Even after learning only a few excel basics, you can create a detailed, living picture of what you have, what you need, and how you’re doing.

Let’s say you sell bulk circus equipment. Some Bozo calls you and says, “Hey! I bought 1,000 red noses from you two years ago. I just now got around to counting and you only sent 500! I want a refund!”

huge piles of office paper files

Image via Pixabay

This could go two ways. You could search through your paper records for this transaction. It could take a while and you may never find it.

If you knew the Excel basics, however, you could search your archive and within seconds discover that Bozo only ordered 500 noses.

Having a well kept digital archive will save you time and money. It may also keep you in the good graces of clowns, who you do not want to cross.

via GIPHY

Put It On My Computer!

Now you want Excel. Why wouldn’t you? It’s great! How do you get it?

You do the same thing you do when you buy literally anything, you go on the internet!

Excel is available through a subscription plan called “Office 365.” For a yearly fee, you get access to Excel and several other apps and services in the Microsoft Office suite.

Go to products.office.com and select the “products” pull-down menu. There, you’ll see options for individuals, homes, and offices. Select the plan that works for you and purchase it!

Follow the prompts to download and install Excel and you’re ready to go! Before you work with Excel, take time to familiarize yourself with the app.

Don’t be Basic, Learn the Excel Basics!

It’s a familiar scene. You’re at the watercooler. That spreadsheet hotshot Brian is talking about a beautiful budget proposal he put together and he is NOT humble about it.

Small business meeting in the office

Image via Pixabay

Everyone is talking about his formatting and vibrant use of shading and you’re worried what people will think of you if you don’t chime in soon.You open your mouth and a disastrous faux pas falls out.

You confused a workbook with a worksheet and everybody heard you. You’ve revealed yourself to be an Excel poser. Plus you KNOW Brian will not let this go, he’ll be calling you “worksheet” for weeks. If you only you’d studied your Excel basics.

Here’s a handy list of Excel terms and definitions to make sure this chilling scenario doesn’t happen to you.

Terms

Definition

Workbook

an Excel file. This is the main .xlsx file that contains every piece of data you’ve entered

Worksheet

A single page within your workbook. These are the actual spreadsheets in your file

Workspace

The specific arrangement of windows in your workbook

Columns

The numbered vertical alignment of cells on your worksheet

Rows

An individual box where you enter data on your worksheet

Headings

The numbered or lettered grey area of your columns and rows

Template

A workbook that has already been formatted to a specific need

Formatting

The visual appearance of cells

Conditional Formatting:

Formatting that applies only to cells which meet the criteria you have set

Formula

A sequence in a cell that defines the interaction between data in other cells and produces the value that results from that interaction

Formula bar

A bar above the worksheet that displays any formulas in the active cell

Ribbon

The space above the formula bar with tools and other options

Filter

A rule you can set to determine which cells in a worksheet are displayed

Freeze Panes

A group of columns or rows that may select to stay visible, even if you scroll away from them

A Fresh Start

You’ve downloaded and installed Excel. You learned some terms. Now it’s time to learn the Excel basics.

Open the “File” menu on the top left of the screen and select “New.”

From here you can watch in-app tutorials and select from thousands of pre-made templates, or you can make like Fleetwood Mac and go your own way by selecting “Blank workbook.”

Click on “Blank workbook.” That will open a blank sheet filled with nothing but potential!

via GIPHY

Fill Those Cells with Your Sweet Sweet Words (or Numbers)

The only people who like empty boxes are cats and kids with big imaginations, so let’s fill them up!

via GIPHY

Entering data is simple, just double click the cell you want to type in and type in it! Try it out with one of your favorite words or phrases, treat yourself.

excel document with “cellar door” written in one cell

You may find that the box is not wide enough for your input, but don’t worry! Like the maternity pants you always wear to Thanksgiving dinner, you can adjust the width.

Go to the top of the column where you see the letters and hover over the line between them. Your cursor should turn into a line intersected by two arrows. Click and drag the columns’ borders in the direction you choose.

excel document with columns of varying width

Make It Pretty

People are shallow, they’ll ignore the most important information in the world if it’s ugly. So let’s learn a little about the cosmetics of spreadsheets. Aesthetics are part of the Excel basics.

A new paint job can take a wall from drab to “dang! That looks good” and the same principle applies to the cells on your spreadsheet.

To apply cell shading, first select the cells you want to shade. You do this by clicking on one, holding it down, and dragging your cursor over the rest.

excel document with different shades of color on three cells

In the home menu, find the paint bucket icon and click the arrow next to it. This will bring down a selection of color options.

If you don’t see the color you want, don’t worry! Click “more colors” to bring up a box with a greater selection under the “Standard” tab and the full RGB spectrum under the “Custom” tab.

Consider your audience and the purpose of color for these cells. Choose a nice red to grab their attention, soft baby blue to put them at ease, or the exact shade of chestnut brown that matches the hair of someone they loved and lost to make them nostalgic and emotional.

color tab showing different options of color model in excel

You can use the same method to change the color text in a cell or cells by selecting the “A” next to the paint bucket.

To get rid of shading, use the same method but instead of selecting a color, select “No fill.”

Save Early, Save Often

You’ve spent hours entering data and perfecting the look of your workbook. It’s finally ready to show your boss and then …

Oh no.

No no no no no no no

Brian, that show off, was bragging about his latest fantasy football win, he ran back to go catch an imaginary pass and he knocked your laptop right out of your hands.

The screen goes black when it hits the floor. After several terrifying minutes, your computer comes back on. Miraculously, it still works. You smile until you remember.

You didn’t save your work. You’ve lost the perfect spreadsheet.

man using his laptop to create basic excel worksheet

Image via Pixabay

All you had to do was click file, then save, then choose your destination, and name the file. You even could have cut out the first two steps by clicking on the floppy disk icon at the top of the screen. There’s no point in learning the Excel basics if everything gets erased.

excel home screen showing autosave icon

You can also get around this whole problem if you have Microsoft OneDrive installed. In that case, turn on auto-saving using the switch directly next to the floppy disc icon. With this feature, your workbook will save automatically.

Let’s Play with Ribbon

Everybody loves ribbon, from overzealous moms wrapping presents at Christmas to cats

In Excel, the ribbon is the row of menu buttons and the tools and options that appear when you select each one. Let’s get into the functions of some of those buttons.

We’ve already covered several of the buttons in the file menu, which is first on the menu bar. The second button the menu bar is selected by default because it’s the only one you need to master the Excel basics. It says “Home.”

home screen of excel showing different options to make an edit

Makin’ Copies

The home menu has seven parts. Going left to right, the first one contains the copy and paste options.

autosave screenshot of excel

The clipboard icon is your basic paste. It takes whatever you’ve copied and places it in the active cell.

There are two different ways to do this, which you can change by clicking the arrow underneath the clipboard. “Keep source formatting” will paste the content you copied with the same formatting in which you copied it.

“Match destination formatting” will paste the content, but conform the formatting to the cell you are pasting it to.

Next to that is the scissors, but they’re not for cutting paper or hair. They’re for cutting ideas. Clicking this will copy what’s in the active cell and then delete it. This is ideal for moving something from one cell to another.

two men carrying a long block of wood in silhouette form

Image via Pixabay

Below the scissors, you’ll see two pieces of paper. This is the copy option, it will copy what’s in the active cell, but not delete it. It’s great for duplicating content.

Finally, we have the paint brush, below the paper. Click this to both copy and paste only the formatting of a cell without copying the content.

Clicking the arrow next to the word “clipboard” at the bottom will show you what you have last copied.

Stylin’ and Profilin’

The second section of the home menu allows you to change the font and style of cells. Remember when I said aesthetics were part of the Excel basics? Here’s where you can take more precise control of the look of your workbook.

font style and font size dropdown menu on excel

The bar controls the font. You can choose whatever font you like for your work, but remember: nobody likes Comic Sans.

Next to the font bar, there’s a box with a number in it and next to that there’s a big A and a littler A. These control the font size. Choose any number you want in the box or increase and decrease by one at a time with the A buttons.

Under these you’ll see the letters “B,” “I,” and “U.” Clicking “B” will make highlighted words bold, so readers will hear them loudly in their head. “I” puts highlighted words in italics. Readers will hear these like a whispered secret. “U” underlines highlighted words. This will tell the reader you’re serious.

Next to those you’ll see a box with a grid. This sets the color, style, and thickness of your cells’ borders.

borders dropdown page on excel

Clicking the grid button itself will apply the current border settings to the active cell. Clicking the arrow next to it will give you options. All the options have illustrations next to them, so pick the one you like.

Clicking the arrow at the bottom of this section will open the cell formatting menu, which has all the options described above and more!

To the Left, to the Left (or the Right or Top or Bottom)

Like a bad Dungeons and Dragons character, your text can change alignment at any time. The third segment of the home menu controls these options.

text alignment options on excel

On the left side of this segment, you’ll see six buttons with a collection of three to four straight lines. The top three buttons control the vertical alignment of the text in a cell, the bottom three control the horizontal alignment.

Next to these, you’ll see the letters “ab” with an arrow under them. This allows you to rotate text. It’s a nice formatting option, but it can increase the size of your cell. Act like a nervous DJ: spin cautiously.

Underneath the rotate button are two that look a lot like the alignment buttons. Use these to increase or decrease the amount of indentation in your cell. You can put your words square in the middle of a cell so they don’t get claustrophobic or have them hug the borders if they need a friend.

wrap text and merge options screenshot on excel

Next to that, you’ll see the words “Wrap Text” and “Merge & Center.” If your text does not fit in the cell and you don’t wish to make it wider, “Wrap Text” will stack the words on top of each other and increase the length of the cell if necessary.

screenshot of how to change the size of a cell

Maybe you don’t want to change the size of your cell at all. That’s reasonable, changing the size of one cell will create a lot of ugly empty space in all the other cells in that row or column. “Merge & Center” is a great way to avoid that.

“Merge & Center” combines two or more cells into one and places the text in the middle. In other words it …

Merges and centers them. It seems obvious in retrospect. If you want to merge without centering, click the arrow next to the button for more options. This is also where you unmerge cells. These are the Excel basics that get overlooked.

The arrow next the word “alignment” at the bottom takes you to the same menu as the font arrow.

Intro to Mathematics

Math nerds rejoice, it’s finally time to talk numbers. The driest part of the Excel basics, but it’s also what Excel does best. The fourth segment in the home menu controls the format in which numbers will appear in your worksheet. You can set the format for the overall sheet or for individual cells by highlighting them.

general view of setting on excel

At the top, you’ll see a bar that says “General.” This sets the general format. Click on it and you’ll see your options. Numbers will appear differently if you’re dealing in money, dates, times, percentages, etc.

Below that bar, you’ll see five buttons. The first defaults to a dollar sign (assuming you live in a country that uses dollars). Click on this to change the format of currency. You’ll see a drop-down menu with symbols for pounds, euros, yen, and a “more accounting options” button. That button will bring up a menu with all the currencies of the world.

Next to that you’ll see the percent symbol, this is a shortcut to the same button in the general drop-down menu.

Then you have the big comma button. This will format your numbers so have commas in them so that numbers go from 6000 to 6,000. This button saves both time and strain on your middle finger when writing out a lot of large numbers. There’s nothing worse than a middle finger injury because you can’t even register your displeasure with the thing that injured you.

Next to the comma you’ll see zeroes and arrows. These add or subtract decimal places to your numbers. You can add a hundred decimal places for extreme precision or play it fast and loose by taking them all away. There’s room for all styles within the Excel basics.

The arrow next to the word “numbers” once again takes you to that same formal cells menu as both the “alignment” and “font” arrows.

Stylin’ and Profilin’ Part II

Enough nerd stuff, let’s get back the fun part of the Excel basics. The fifth segment of the home menu is like Paris during fashion week: full of style!

formatting table options on excel

This one’s simple, only three buttons.

The first one is “Conditional Formatting.” You may remember those words from the handy chart up above. You can set rules which will automatically change the formatting of certain cells. You can do this with numbers above or a below a certain threshold, dates within a certain range, and more.

For example, let’s say you want to make the most recent information pop. You would click the “Conditional Formatting” button, hover over “Highlight Cell Rules” and click “A Date Occurring…”

an example of conditional formatting in a cell

This brings up a menu that says “Format cells that contain a date occurring:” with two bars below it. In the left bar, select “Yesterday” and in the right select “Light Red Fill with Dark Red Text.” Any instance of yesterday’s date will automatically change to this format. This will stay consistent whenever someone views the file. Call yourself Dr. Frankenstein because you’ve brought life to your document, it can think and change as time goes by.

Let’s Table This

This next button is a doozy that’s why it’s getting its own section. It’s a robust piece of software, so even the Excel basics can get a little complicated. The second button in this segment says “Format as Table”. Excel tables organize your data like dinner tables organize food, they give you a nice pretty plane to put it all on.

sample formatted table on excel

Tables can be made up of the following parts …

Header Row

By default, tables have header rows. This is a row of cells at the top of your table that labels the columns and allows you to sort and filter your content. We’ll go deeper on sorting and filtering later.

Banded Rows

The banded rows are all the other rows. The alternate in color to improve readability. This is a good opportunity to have fun but still look professional. You can make them tiger striped!

sample table with its header colored as orange

Calculated Columns

If you type a formula (again, more on those later) into one cell in a blank column, it will automatically spread to the rest of the cells in that column. That’s a calculated column.

Total Row

This is a row at the bottom of the table that calculates the totals of each of the columns.

Sizing Handle

The sizing handle is the little mark on the bottom right corner of the table. Drag this to change the borders of the table.

Okay Where Were We?

Now you know what a table is, let’s get back to the Home menu. The button next to “Format as Table” says “Cell Styles.” This is your walk-in closet, it gives you even more style options for your cells.

cell styles option on excel

This menu suggests different shading styles to communicate different things. It also lets you select gradients of color. When full color is too powerful, you can use the more subtle touch of a 20, 40, or 60 percent accent. You’re learning the Excel basics, but they give the tools to be complex.

Let’s Get Some More Cells in There

The sixth segment in the home menu lets you add and subtract cells. It also gives you another way to format them.

cells menu options to add or remove cells

“Insert” will add a row of cells above your active cell, giving you a little more head room if you need it. Clicking the arrow on the bottom of the button gives you the option to add rows or columns and in different places.

Like the proverbial good Lord, Excel can both giveth and taketh away. “Delete” removes cells in the same way “Insert” adds them with one difference. Instead of deleting the row above your active cell, it deletes the row your active cell is in. Be careful with this one and remember: if you delete the wrong cells, a quick press of Ctrl + Z (or ⌘ + Z on a Mac) will bring it back as long as you have done nothing else since you deleted it.

This also applies to any singular incorrect move in Excel.

The final button is “Format.” This button is another way to change the size and shape of cells like we already described, but unlike an old dog, it also does some new tricks.

Click the arrow on this button and you’ll see those familiar ways of altering cells, but you’ll also have the option of renaming your worksheet. Give it a nice name like “William” or “Susan.” You could also name it something professional like “Budget” or “Calendar”.

altering cells on excel tabs

You can also change the color of these tabs. It’s not the most prominent part of the worksheet, but with the Excel basics, you can change the look anything in the window.

Cut the Fat!

I know. Your work is precious. After spending all this time on your sheet, cutting anything is heartbreaking but the more you work on something, the more chances it has to get bloated. The seventh and final segment on the home menu is “Editing” and it can help with that.

editing tab with autoSum option

The first button in this segment says “AutoSum” and you know what that means …

It’s Time to Talk Functions

This is another big one so get ready. Let’s talk about formulas, functions, what they are, and the difference between them.

In Excel, a formula is an algebraic equation you put in a cell to generate numbers.

Nobody likes to think about algebra when they’re in school so you especially don’t want to think about algebra now you’re out of school. Don’t worry, it’s not as scary as it seems and this is probably the hardest part of learning the Excel basics so it won’t get worse than this.

Here’s a simple example with the most basic formula there is: AutoSum.

If you enter the value of 2 in cell A1 and the value of 2 in A2, you can enter =SUM(A1:A2) in A3 and when you hit enter, Excel will do the math of adding the values in A1 and A2 and display the number 4 in A3.

how AutoSum formula is done for adding values of cells

Formulas always start with an equals sign. This lets Excel know that whatever follows that = is a formula.

In love, it’s up to you and your partner to define the relationship. In Excel, the word immediately after the equals sign defines the relationship between the cells listed in between the parenthesis. So in this instance, “SUM” tells Excel to add those numbers.

There is no limit to the number of cells you can place in between the parenthesis. You can do this by entering the coordinates of the cells, which has a fun Battleship vibe or you can select them with your mouse while the formula is open, which is typically faster.

Don’t enter every individual cell’s coordinates! If you wanted to select A1 through A100, you wouldn’t type “A1:A2:A3:” and so on. It’s tedious and will not work. Instead, type “A1:A100” It will automatically include all cells in that range.

Congratulations! You’ve learned your first formula AND your first function. A function is a formula that’s pre-programmed into Excel. Here’s a list of the most popular functions in Excel. This is a cornerstone of the Excel basics.

Terms

Definition

SUM

Creates a sum of values in the selected cells

AVERAGE

Calculates the average of the values in the selected cells

COUNT

 Will count the number of cells that have a numerical value in them

MAX

 Will return the highest value in the selected cells

MIN

Will return the lowest value in the selected cells

IF

Displays one value if a condition is met and another if it is not

LOOKUP

When given the value of a single row or column, will return the value in the same cell in another row or column

MATCH

Searches a range of cells for a specific value and returns to relative position of that value within the range

DAYS

Will return the number of days between two dates

Okay, Back to the Ribbon!

The first five functions in the chart are all listed in the AutoSum drop-down menu. Clicking on them will save you the trouble of having to type them out.

AutoSum dropdown menu

Under the AutoSum button is the Fill button. This is another tricky one!

Clicking on “Fill” results in a drop-down menu with the basic options “Up,” Down,” “Left,” and “Right” and more advanced options below that.

The Fill option takes a value from one cell and automatically moves it to another. There are two important things to remember here!

1. The cell being filled is always the active cell. So “Up” doesn’t take the value of the active cell and move it up one cell like you might assume, it takes the value of the cell below the active cell and moves it up into the active cell.

2. Formulas are relative. “Fill” won’t copy the same values, it will copy the relative position of the values in the formula. So if you’re moving the formula one cell up, the values in the formula will also move one cell up.

Time for Spring Cleaning

Unless it’s not spring, then it’s time for Summer, Fall, or Winter cleaning. Beneath the Fill button is the Clear button. Pressing Delete or Backspace on a cell deletes the contents of the cell. Pressing those buttons on an active cell deletes the contents one character at a time but neither deletes anything else.

man fixing and sealing his garbage in a garbage bag

Image via Max Pixel

“Clear” brings up a drop-down menu where you can also delete things like formatting, comments, hyperlinks, or everything at once.

clear dropdown menu showing options to delete formatting, comments and the like

I Need to Get Organize

Next to “Fill” is “Sort & Filter” and you better be ready for another drop-down menu. By now you should know that drop-down menus are the life blood of the Excel basics.

sort and filter dropdown menu

“Sort A to Z” or “Sort Smallest to Largest” will sort a selection of cells in alphabetical order if they’re words or numerical order if they’re numbers. “Sort Z to A” or “Sort Largest to Smallest” does the same thing in reverse. If you want to go wild, you can create a custom sort by clicking the “Custom Sort” option.

Below that is the “Filter” button. Click on this and arrows will appear on top of all your columns. This gives you access those same sorting options above but also includes the secret number filters. These are the more advanced methods of sorting that only apply to numbers.

text filter dropdown menu

Finally, we have the Private Detective’s favorite button “Find & Select,” represented by a magnifying glass. In this drop-down menu you can find specific values, replace them with other values, or go to a specific cell by typing in its coordinates.

One Last Thing …

I hope you’ve enjoyed your time with the Ribbon because it’s the biggest part of learning the Excel basics. Before we say goodbye to the Ribbon, we must learn to … say goodbye to the Ribbon. Clicking the small arrow on the bottom left of the Ribbon will collapse it. If you do this on accident, don’t panic! You can bring the Ribbon back by clicking the box in between the minimize button and “Sign in” on the top bar of your window.

Do All This But Faster!

Now that you know the Excel basics, it’s time to learn the keyboard shortcuts so you can do them in a flash. Here’s your last chart

gear ison

Image via Flaticon

General

  1. – Ctrl + N: New workbook
  2. – Ctrl + O: Open workbook
  3. – Ctrl + S: Save workbook
  4. – Ctrl + P: Print
  5. – Ctrl + W: Close workbook
  6. – Alt + F4: Close Excel (⌘ + Q on a Mac)
  7. – Ctrl + A: Select all cells

test results icon

Image via Flaticon

Editing

  1. – Ctrl + Z: Undo previous action
  2. – Ctrl + Y: Redo previous action
  3. – Ctrl + C: Copy selected cells
  4. – Ctrl + X: Cut selected cells
  5. – Ctrl + V: Paste from clipboard

spreadsheet icon

Image via Flaticon

Tables

  1. – Ctrl + T: insert table (^ + T on a Mac)
  2. – Alt + ↓ : Activate filter (⌥ + ↓ on a Mac)
  3. – Shift + Space: Select table row
  4. – Ctrl + Space: Select table column (^ + Space on a Mac)
  5. – Ctrl + A: select table

Mac users note that unless stated otherwise, substitute the Ctrl key for the Command key in your shortcuts.

Now Go Out There and Make Spreadsheets!

With these Excel basics under your belt, you can wield worksheets like a champion!

laptop with karate uniform and belts on the side on top of the table

Image via Pixabay

If you want to learn more than just the Excel basics, you can go straight to the source. The Microsoft website has articles and tutorials for everything and anything you can do in Excel.

How to Delete Duplicates in Excel

How to Delete Duplicates in Excel: An Easy Guide

How to Delete Duplicates in Excel

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

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

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

Reasons For Deleting Duplicates

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

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

In this tutorial, we’ll cover:

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

Checking for Duplicates

check duplicates how to delete duplicates in excel

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

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

How to Delete Duplicates in Excel

data tools how to delete duplicates in excel

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

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

Another Option: Advanced Filtering

sort filter how to delete duplicates in excel

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

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

Deleting Duplicates in Microsoft Excel 2003 or earlier


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

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

Conclusion

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

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

how to make a scatter plot in excel

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

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

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

What is a Scatter Plot?

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

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

how to make a scatter plot in excel

Relationship Between Variables

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

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

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

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

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

The Data

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

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

Insert Your Chart

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

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

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

The five different types of scatter chart are:

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

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

how to make a scatter plot in excel

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

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

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

how to make a scatter plot in excel

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

Adding Design Elements to Your Scatter Plot

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

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

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

how to make a scatter plot in excel

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

The Design and Format Tabs

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

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

How to Make a Scatter Plot in Excel: Final Review

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

How to Create a Drop Down List in Excel with Data Validation

Those wondering how to create a drop down list in Excel will be relieved to know that it is easier than it sounds. As you may already know, drop down lists make data entry a breeze. For example, if you have ever used such a menu for surveys, polls, and web forms, you know how convenient they are. As tech nerds, we’re happy that such an option exists within an Excel spreadsheet.

Adding a drop-down list to a cell or range using Data Validation is a simple matter. Data Validation is used to define restrictions on what data can or should be entered into a cell. Here we’ll use a List to restrict what values can be entered into a cell. This article walks you through a step-by-step guide of how to create a drop down list in Excel.

How to Create a Drop Down List in Excel for a Cell or Range

Select the cell or range you want to use for a drop-down list, then

  • Choose Data Validation from the Data Tools group on the Data tab
  • Select the Settings tab
  • In the Allow box, select List
  • Click the Source box
  • Type in a list of values separated by a comma
  • Make sure the In-cell dropdown box is checked
  • Click OK

Data validation - Excel pick from drop down list

The list I created was for cell A1, which is shown below.

Excel 2010 Drop Down List

Excel 2010 drop down list

List Data Sources

Manually entering the source data for the Excel 2010 drop down list is probably the least desirable method. A better way is to put the list in a range, then refer to the range.

Data Validation list source range

The same list data was put into the range J1: J6, then I changed the source reference to these cells. This is a better method than manually entering the values, but older versions of Excel require the list to be on the same worksheet. One way around this and a better solution is to give the List range a Name.

Data Validation list source Named rangeYou can give the List range a Name then use it for the Source. For example, I selected the range J1: J6 then typed TheNames into the Name box, thereby creating a Named Range. On the Data Validation dialog box, I typed in =TheNames into the Source box.

Change the Reference to the Named Range

Now let’s assume that we have to add a couple more names to the list. Instead of changing every cell that references this Data Validation list, we just change the reference to the Named Range. (Choose Formulas > Name Manager, select the Named Range, change the reference in the Refers to box, then click the green arrow to make the change and click Close.)

But if the list will grow over time, changing the reference should be done automatically with a dynamic Named Range formula. We’ll do this by using the OFFSET formula.

  • Choose Formulas > Name Manager
  • Select New
  • Type a Name in the Name box (I’ll use myNamesList)
  • In the Refers to box type =OFFSET(Sheet1!$J$1,0,0,COUNTA(Sheet1!$J:$J),1)
  • Click OK

Now select the cell or range with Data Validation and,

  • Choose Data > Data Validation
  • Select the Settings tab
  • In the Source box type in =myNamesList (or the Name you created)
  • Click OK

This Named Range formula is dynamic, which means the source list will expand when names are added to the list. If the list contains more than 8 values the drop-down list will have a scroll bar.

Excel – Pick from Drop Down List

Data Validation list source range expanded

A Bonus Tip

One of the things we love the most about creating a drop down list in Excel is that the program reminds you to save your work before you click out. However, the program will not remind you to create a backup. If you do not have an automated backup system in place, we highly recommend implementing one. For example, you can download and save a copy locally to your desktop and USB drive. If you are using a thumb drive, store the saved copy offsite. Does this sound like overkill? Maybe. But you will not think so if you lose your first copy and then find yourself in need of it. 

How to Create a Drop Down List in Excel: Final Review

That’s it. We hope you enjoyed reading our article on how to create a drop down list in Excel with data validation. It’s not as hard as you might have thought.  Hopefully, you can now create a drop-down list that will meet your needs. Happy organizing!

Image that shows how to freeze cells in Excel.

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

Image that shows how to freeze cells in Excel.

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

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

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

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

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

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

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

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

How to Freeze Cells in Excel

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

Freezing the Top Row

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

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

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

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

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

Freezing the Leftmost Column

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

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

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

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

More Options for How to Freeze Cells in Excel

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

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

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

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

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

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

Here’s a Shortcut to Freeze Cells in Excel

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

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

How to Freeze Cells: Final Review

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

how to use goal seek in excel

How to Use Goal Seek in Excel

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

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

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

Why Use Goal Seek?

why_use_goal_seek_

 

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

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

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

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

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

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

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

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

Here’s How To Use Goal Seek In Excel

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

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

Sample Scenario

how to use goal seek in excel

How To Start

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

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

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

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

Next Move

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

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

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

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

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

Final Steps

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

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

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

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

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

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

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

Conclusion

 

how to use goal seek in excel

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

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

The Goal Seek serves to perform exactly that.

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

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

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

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

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

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

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

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

how to freeze a row in excel

How to Freeze A Row in Excel: A Practical Guide

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

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

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

how to freeze a row in excel

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

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

How to Freeze a Row in Excel

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

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

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

Keep Your Headers at the Top of Your Worksheet

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

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

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

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

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

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

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

Freezing More Than One Row in Excel

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

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

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

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

Unfreezing Rows

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

Freezing Rows Vs. Splitting Panes

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

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

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

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

Freezing a Row in Excel: Life Hack

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

Commercial 

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

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

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

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

How to Freeze a Row in Excel: Final Review

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

how to alphabetize in excel

How to Alphabetize in Excel: Fast & Simple

how to alphabetize in excel

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

What is Excel?

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

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

What is an Excel Spreadsheet?

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

How To Alphabetize In Excel

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

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

We’re going to break down:

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

Let’s dive right in!

The Reasons for Alphabetizing

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

products how to alphabetize in excel

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

If we alphabetize the data, we could:

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

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

Basic Alphabetization

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

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

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

customers table how to alphabetize in excel

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

One thing to remember:

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

Advanced Alphabetization

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

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

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

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

sort how to alphabetize in excel

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

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

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

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

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

sort 2 how to alphabetize in excel

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

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

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

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

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

products table 3 how to alphabetize in excel

Alphabetizing in a Custom Order

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

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

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

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

products table 4 how to alphabetize in excel

We have added a column for the month now.

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

custom lists how to alphabetize in excel

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

products table how to alphabetize in excel

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

Why Isn’t My Spreadsheet Sorting?

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

Effectively Sorted 

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

Off-base or Mixed Data Type 

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

Additional Considerations 

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

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

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

Spreadsheet Programs 

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

Account 

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

Structures 

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

School and Grades 

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

Records 

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

Sports 

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

What is a Functioning Worksheet? 

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

Why Not Use a Word Processor Instead of a Spreadsheet? 

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

How to Alphabetize in Excel: Final Review

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

How to Make a Line Graph in Excel

How to Make a Line Graph in Excel

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

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

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

What is a Line Graph?

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

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

When Should You Use a Line Graph?

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

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

When You Should Not Use a Line Graph

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

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

How to Make a Line Graph in Excel

Why Use Line Graphs?

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

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

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

How To Make A Line Graph In Excel

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

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

Beginning with Creating your Line Graph in Excel

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

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

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

Editing Line Graphs

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

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

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

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

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

Changing Colors

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

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

The Advantages of Using a Line Graph to Demonstrate Data

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

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

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

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

How to Make a Line Graph: Final Review

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

how to subtract in excel

How to Subtract in Excel

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

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

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

how to subtract in excel

Important Points to Remember

Different Types of Excel Subtraction

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

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

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

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

Subtracting with Simple Numbers

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

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

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

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

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

Subtraction Using Two or More Unique Cells

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

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

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

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

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

How to Subtract Using the SUM Function

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

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

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

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

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

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

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

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

how to subtract in excel by first using the SUM function

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

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

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

How To Subtract In Excel: Final Review

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

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

Excel SUM Formula: What Is It And When Do I Use It?

If you have a large database of information, it can be difficult to make sense of all those names numbers. The Excel SUM formula lets you focus on specific categories within an Excel worksheet and come up with subtotals that can help you to spot trends and patterns in your data.

Read on to find out more about:

  • How the SUM function works
  • Using the Excel SUM function
  • Different applications for SUM formulas

About the SUM Function

The SUM function is one of the simplest functions in Excel, but it’s also one of the most useful. It’s one of several math and trig functions available and allows you to add up multiple values. You can add up individual numbers, cell references, ranges of cells, or mix these values together. You can also use the SUM function as part of a more complicated order of operations to manipulate values. For example:

  • Addition: =SUM(A1:A10)+B1
  • Subtraction: =SUM(A1:A10)-B1
  • Multiplication: =SUM(A1:A10)*B1
  • Division: =SUM(A1:A10)/B1
  • Exponentiation: =SUM(A1:A10)^B1

How to Use the SUM Function

The Excel SUM formula is easy to use and easy to remember, even for complete beginners. The basic syntax of the function goes as follows:

=SUM(number1,[number2],…)

You only need to input a single range or value into the brackets, but if needed, you can include as many as 255 numbers. The parameters you’ll be using include:

  • number1: This value is required for the function to work properly. You can use a number such as 4, a cell reference such as A4, or a cell range like A1:A4.
  • number2-255: While you don’t have to input this value, if necessary, you can add hundreds of secondary numbers to your formula.

The SUM formula is a worksheet function, meaning that to use it, you simply type the appropriate syntax into a free cell to find out your end result. You can also use it in tables to total up rows and columns.

If you don’t want to display totals on your worksheet, you can find out the sum of a group of cells using the Excel status bar. Simply click and select the range, then look at the sum total listed in the lower right-hand side of the window.

Using SUM With Other Excel Functions

Excel has some built-in formulas that combine the SUM function with other useful tools. For example, combining SUM with the IF formula creates the SUMIF function, which allows you not only to add up data points, but also to choose these points based on criteria such as date, numeric value, text descriptors, and more.

You can also create your own formulas that combine the SUM function with other actions. Combining the operations of two or more functions is commonly known as “nesting functions.” There are endless ways that you can nest SUM with different Excel formulas. For example, you can combine SUM with the ROUND function to round decimals and fractions before adding them up, which can be handy come tax season.

Combining SUM with the VLOOKUP allows you to locate and sum values with that meet one or several criteria. No matter what the application, you can find a way to nest SUM with other Excel functions to get the results that you need.

Using Excel SUM Formula Across Multiple Worksheets

When using the SUM function, you might not always be dealing with values on a single spreadsheet. For example, if your company uses a different worksheet for each month or quarter, you may need to total data across several separate datasets. Instead of manually adding each relevant cell, it’s much easier to use a 3-Dimensional (or 3D) SUM.

The syntax to add a cell value from different sheets reads:

=SUM(SheetX:SheetY!cell#)

Common Problems With Using Excel SUM Formula

Although the Excel SUM formula is relatively straightforward, every now and then even the most tech-savvy of us run into issues. Here are some common problems you may run into when using SUM functions, and quick fixes to help save you a headache:

The result is displayed as ####: Often, this reading simply means that your column is too narrow to display the full result. Increase the width of your column to prevent this problem.

The formula won’t change to a result: The cell containing your SUM function may be formatted as a text cell, preventing the formula from working. Hit Ctrl+1 to bring up the Format Cells dialog, then click the Number tab to select the right format. You can also use F2 > Enter.

The function won’t update automatically: Open the Formula tab and go to Calculation to check that it’s set to automatic. You can also use the F9 key.

The #NAME? error appears: This usually means that a formula’s syntax is incorrect. Check to make sure that everything is spelled correctly and in the right order.

Examples of SUM Formulas

As an Excel user, you’re bound to run across the SUM function in various ways, shapes, and forms. Here are some of the more common formulas that utilize this tool:

When inputting individual cells or values:

=SUM(A1,A3,B2,C3…)

When inputting a range of values:

=SUM(A1:A3,B1:B3)

Sum an entire column:

=SUM(A:A)

Sum every nth row:

=SUM(OFFSET(A1,(ROW()-offset)*n,0,n,1))

Calculating a running total:

=SUM($A$1:A1)

Conclusion

excel shortcuts

The SUM function is a must-know tool for all Excel users, whether you’re just starting out or working with Microsoft on a professional level. The Excel SUM formula can help you to add points from complex sets of data and gain valuable insights that will help you or your company to make more well-informed decisions in the future.

We hope that our comprehensive guide has helped you to better understand the many uses of the SUM formula and hone your skills so that you can take full advantage of this invaluable tool.

Related Posts Plugin for WordPress, Blogger...