Category Archives: General Update

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

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.

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.

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!

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!

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.

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.

average function in excel

How to Use the Average Function in Excel

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

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

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

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

In this tutorial, we’ll show you:

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

Using the average function in excel

What is an Average Function in Excel?

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

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

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

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

When Do Averages Come in Handy?

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

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

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

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

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

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

The Basics of Excel’s Average Function

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

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

Using the Average Function in Excel

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

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

AVERAGE (number1,[number2], …)

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

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

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

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

The AVERAGEA and AVERAGEIF Function

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

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

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

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

The Average Function in Action

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

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

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

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

Conclusion

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

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

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

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

Photo of spreadsheet on laptop with column selected for "how to move columns in Excel" article.

How to Move Columns in Excel While Keeping Your Data Intact

How to move columns in Excel

Ready to hone your skills in working with Excel spreadsheets for a more organized workflow? Start with learning how to move columns in Excel. This especially helps with columns containing large swaths of data.

Columns, rows, and cells are perhaps the three most recognizable features of Excel. They provide the window in which to view, calculate, and organize your information. Further, they make moving your info around very easy. We will show you two ways to work with columns in your Excel spreadsheet. You’ll also learn how you can move columns in Excel without losing large swaths of data.

How to Move Columns in Excel: The Cut or Copy Method

How to move columns in excel

Understanding how to move columns in Excel with this method is very easy. Cut or copy is arguably the most widely used editing function across all platforms. Within Excel, it is particularly useful.

To move a single column of data, click the header of the target column.

  1. With your mouse hovering over the selected column, right-click and select either Cut or Copy from the menu.
  2. The keyboard shortcut for highlighting an entire column is Control Spacebar. For the move functions, you can use shortcut Control X for cut and Control C for copy.
  3. Be mindful of the difference between Cut and Copy. When you cut a selection, you are removing it from its original location and placing in another part of your spreadsheet or workbook.
  4. Copying a selection of information creates a duplicate of that data for you to place elsewhere.
  5. When you are ready to place your column, select the heading of the column to the right of where you want it to go.

If you cut column D and want it inserted after column A, then you would select column B, right-click and choose Insert Cut Cells from the pop-up.

Note that the insert function will shift your data to the right to make room for the new column.

You can also use the paste function to place your column in its new location. But doing so will replace the old data with the new.

How to Move Columns in Excel: Replace Data in One Column with Data from Another

Going back to our previous example, here’s how to move columns in Excel when replacing data in one column with data from another.

  1. Select column B, right-click and choose Paste from the pop-up.
  2. Cut, copy, insert and paste can all be used with a single column or a range of columns depending on your needs.
  3. If trying to move multiple columns, i.e., Column A and Column D, this will only work with the copy function. The cut function is unable to perform this task.
  4. You can also select columns within your spreadsheet and move those as necessary without needing to select the column headers. Be mindful that this will alter your sheet in a specific area as opposed to the top to bottom change that occurs in the above examples.

How to Move Columns in Excel: Drag and Drop

Another efficient method to use when you need to move columns in Excel is to drag and drop. This is relatively easy to complete although it may take a couple of attempts before you can do it efficiently.

  1. First, highlight the column you are moving. You can also perform this task using just a section of data.
  2. Along either edge of the highlighted column, hover your mouse until the cursor changes to four small arrows and click and hold the mouse button.
  3. Note that depending on your version of Excel and if you are using a Mac, the four arrows may alternatively be a small hand symbol.
  4. Hold down the Shift button and then pull the column towards its intended destination. A thin line will appear to help guide you before placing the column.
  5. Once you reach the end location, just let go of the mouse button first and then the shift key. The column will drop into place.

The shift key is vital in drag and drop as it directs Excel to insert the columns. If you perform the same steps above without the shift key, the column you are moving will replace the info in the destination column.

Excel will prompt you with a warning message before you complete the drop.

What Happens When You Move Columns in Excel

With both techniques above, when you move a column, you also move all of the data within each cell of the column.

Formats, formulas, and values all maintain their original integrity regardless of which procedure you use.

Additionally, although we focused on columns, these instructions will also work when you need to move rows within your Excel spreadsheet.

Are There Other Ways How to Move Columns in Excel?

There are a few other ways to move columns in Excel, but they typically require the use of a plug-in or creating a Macro. This is doable, but requires more advanced knowledge, so you should proceed with columns.

Plug-ins can create more headaches by adding unnecessary toolbars and duplicate functions to an already robust program. Macros may be potentially useful in some circumstances. But these tend to be highly specific and are usually best used by those with an in-depth knowledge of Excel.

Troubleshooting on How to Move Columns in Excel

There are very few issues you can run into when moving columns, with the main one being choosing Copy instead of Cut as your initial move method.

Thankfully, Excel will prompt you before any data is changed with the following pop-up:

Do you want to replace the contents of the selected cells?

This is a common message when pasting information, so it comes in handy should you use the drag and drop method without holding the shift key.

With the drag and drop option, it is essential to be careful when selecting either edge of the column you want to move.

If you see a clear, block plus sign instead of the four-arrow cursor, the highlight cursor is active. A black, block plus sign means the single cell drag and drop tool is active. When these tools are active, they can create some issues that could require correction of your data.

Conclusion

There are a lot of helpful tools when working in Excel. The ability to move columns is undoubtedly one of them. It doesn’t matter whether it’s a full data set from top to bottom or a simple ten-row column. Excel makes it a cinch to get your info where it needs to be.

The 24-Hour Rule

A long time ago I was doing some reading on how you write and publish blog posts. One suggestion that helps me is the 24-hour rule. And that is to finish your post and then wait 24 hours before reading it one-last-time. After all, a blog post is going to be public, out there on the internet with your name on it, for a very long time.

The problem with this 24-hour rule is that I usually find one or more things that need to be changed. And then I have to wait another 24-hours.

When you are on a deadline, like, for instance, having a self-imposed quota of 2 or 3 blog posts a week, the 24-hour rule is inconvenient. It drives you to compromise on the level of quality.

By that I mean that there is a hierarchy of quality levels starting with spelling and grammar and ending with saying what you intended in the most concise, articulate manner possible. A deadline drives you to ensure the spelling and grammar are correct and then you compromise on just how well you get your point across.

Tech Man

For me the deadline and quality levels force a certain tension, all of which drove me to abandon my blogging of all things Excel. Yet since a deadline and quality levels are self-imposed, they are also controllable.

I’ve decided to stick with the 24-hour rule and forget about the deadline, or any deadline for that matter. Consequently my blogging will continue, at my own pace.

Right now I’m working on a post about the Excel iPad app.

So the 24-hour rule stays and the journey continues.

Excel without a Mac

I sold my 15″ MacBook Pro yesterday and am waiting on a 21.5″ iMac to arrive in 2-3 weeks. So no more adventures with Excel 2011 for a bit.

Time to dust off the Dell desktop and reacquaint myself with the “real” Excel. That of the Windows variety. I’m putting Excel 2003 in my rear view mirror (finally) and will focus on Excel 2007 and 2010.

I would love to get Excel 2013 and test it out, but the Dell will need to be replaced this year so I’ll wait until I have a new PC. I would like it to be a PC / Tablet combo machine with Windows 8, but am going to wait until the dust settles on the new operating system.

When upgrading to a new version of Windows I usually make it a practice to wait until after “service pack 1” is released before making the switch.

So Excel without a Mac is a survivable condition when you have a Windows PC for a backup. The converse is not true. Having a Mac without Excel would be the worst form of torture. Living with Numbers is not, in my opinion, a survivable condition. Excel on a Mac is a challenge, but nirvana when compared to Numbers.

Which leads me to wonder when the next version of Excel for the Mac will come out. I have high expectations: Power Pivot, Name Manager, Evaluate Formula dialog box, Status Bar Functions that aren’t circa Excel 2003, and elimination of the Menu bar.

However, my expectations might just be the result of some wishful thinking rooted in a dream state.

20130208-185246.jpg

Brain

Blog Comments and Inspiration

I’d like to thank everyone who has left a comment on this blog within the past year. It’s been a privilege to interact with some of you.

When I started this blog a couple of years ago, I was partially employed. Consequently I had lots of time to spend learning how to write blog posts about a topic that I am relatively good at, and a desire to share my knowledge.

Brain

My big problem is that I am a perfectionist. I want to express myself clearly, about topics that can be quite complicated.

About a year ago I started a full time job, which includes a long commute. Obviously this impacted the amount of time available for blogging and my articles dwindled to a trickle.

Strangely enough, about the time I started my new job, the popularity of this blog began to take off. One practical effect of this is the increased number of reader comments.

Many of these comments require interaction on my part. Answering questions, looking up old articles (to remember what I had written) and locating those files (what was that file name?), and even going as far as looking at readers spreadsheets to help them solve a problem. All of which takes up time.

The comments I receive have made me consciously aware that I need to provide more content to my readers, and on a consistent basis. (I believe that’s my Conscience talking). So I’ve resolved to do just that by publicly declaring that I’ll write at least one article per week.

In doing so I may have to lose some of my perfectionism, but I still have standards. My next article will solve the problem of not having the Control+Home keyboard shortcut on a Mac.

Hopefully you all will stay tuned.

Excel 2010 icon

An Excel Crossroads – Mac and Windows

Excel 2010 iconI bought a MacBook Pro and installed Windows 7 with Boot Camp Assistant so I could access the “normal” Excel. This was important to me at the time because I was doing Excel development work in versions 2003, 2007 and 2010 on a Windows PC.

MacBook ProI love my MacBook Pro, which I consider my computer of the future. It’s great for dealing with Photos, Movies, Music, and all of my iOS devices.

I purchased the Mac versions of Excel (2008 and 2011) but they seem like a mixture of the three Excel versions mentioned above, and can be very frustrating at times. There are a few things missing in the latest Excel Mac version, like the Name Manager and the Evaluate Formula dialog box, to name just two.

Despite these shortcomings, I’m seriously thinking about selling my desktop PC and going exclusively with the Mac. That’s the crossroads I’m at right now. Letting go of the PC, but keeping the Windows versions of Excel.

My Hangups

BootCamp allows me to run Windows, and the Excel versions I need, but is very restrictive in the sense that I have to shut down my Mac, then restart with BootCamp to run Windows 7. This is a real pain in the you-know-what.

There are other problems with running Windows on a Mac. For instance, I haven’t been able to get my Apple Magic Mouse to work with Windows 7. (Go figure.) And using Dropbox was the best way to get Excel files between Windows 7 and Mac OS X.

My Saving Grace

All of those issues were solved when I purchased the Parallels Desktop software for Mac. Now I can switch to Windows without having to shut down Mac OS X Lion. That’s just plain awesome!

I’m still getting used to how Parallels works, but this software is easily worth the purchase price. And my Apple Magic Mouse, just works.

Parallels Desktop on my MacBook Pro

Here’s a screen shot of the Parallels Desktop open on my MacBook Pro. You can see that I have Microsoft 2010 (Windows) open, but I want you to notice that I also have the Finder dialog box open.

Parallels Desktop

I dragged the Card Size Lookup.xlsx file from Finder onto the Excel 2010 (Windows) program and it opened. I then made a change to the spreadsheet and saved it. The file was saved back on my Mac from whence it came. This is the miracle of modern technology.

I still have lots of testing to do but it looks like the Parallels Desktop has made my life much, much easier. I know now which way to turn at the crossroads.

Related Posts Plugin for WordPress, Blogger...