5 New and Creative Ways To Utilize Excel

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

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

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

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

Create Patterns For Your Hobbies

create hobby lists

Image via Tech Advisor

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

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

The steps are simple and are as follows:

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

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

Make A Calendar

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

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

Step 1

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

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

Step 2

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

Step 3

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

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

Step 4

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

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

Step 5

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

Create Your Own Crossword Puzzle

creating a crossword puzzle on excel

Image via YouTube

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

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

Step 1

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

Step 2

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

Step 3

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

Step 4

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

Step 5

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

Step 6

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

Create A Sudoku Puzzle

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

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

Make Your Own Trivia

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

Step 1

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

Step 2

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

Step 3 

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

Step 4

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

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

=COUNTA(A:A)-1

In the section for the score, use this formula:

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

Step 5

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

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

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

The 20 Most Useful Excel Shortcuts to Utilize

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

Reasons to Use Shortcuts

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

keyboard with shortcuts on it

Image via Keyshorts

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

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

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

Workbook Operations

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

Open Workbook: Ctrl + O (Windows and Mac)

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

New Workbook: Ctrl + N (Windows and Mac)

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

New Worksheet: Shift + F11 (Windows and Mac)

This shortcut adds a new worksheet to a workbook.

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

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

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

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

Ribbon Operations

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

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

Use this shortcut to open or hide the ribbon box.

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

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

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

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

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

Editing Operations

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

Copy: Ctrl + C (Windows and Mac)

Paste:  Ctrl + V (Windows and Mac)

Undo: Ctrl + Z (Windows and Mac)

Cut: Ctrl + X (Windows and Mac)

Spellcheck: F7 (Windows and Mac)

Formatting Operations

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

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

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

Underline: Ctrl + U (Windows)   ⌘ + U

Data Editing Operations

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

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

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

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

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

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

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

Calculations Operations

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

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

Insert Function: Shift + F3 (Windows and Mac)

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

How to Memorize Excel Shortcuts

excel shortcuts on green background

Image via Udemy

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

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

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

Laminated Cards

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

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

Online Classes

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

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

Write Shortcuts Down

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

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

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

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

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

pv table

Pivot Tables (PV Table): Everything You Need to Know

When working with Excel spreadsheets, it can be difficult to extract the information you need from large sets of data. Pivot Tables (pv table) offer a great way to quickly condense and analyze, and present your data, allowing you to make informed decisions in both your professional and personal life.

Pivot Tables allow you to effortlessly summarize large amounts of data into a simple format that’s easy to read and analyze. You can subtotal numeric data, sort information into subcategories, or create custom calculations and formulas to focus your results.

Here, we’re going to discuss everything that you need to know about a PV Table, including:

  • Why you should use Pivot Tables
  • How to create a Pivot Table
  • What you can do with Pivot Tables

pv table

What are Pivot Tables (AKA Pv Table)?

Pivot Tables, also known as Pv Tables, are an Excel tool that allows you to organize data in a way that’s easy to understand. You can use data from a spreadsheet or import a database table to access the information you need. Excel is able to connect to external sources such as SQL Server tables, Azure Marketplace, Office Data Connection (.odc) files, XML files, Access databases, and text files. Making a Pivot Table won’t alter your original data in any way, but instead will arrange it into a tabular format that makes sense and is easier to read than the original spreadsheet.

The Advantages of Using Pivot Tables

The main function of Pivot Tables is to help you organize large quantities of data in a way that’s quick to analyze. You can filter and sort groups into a table that’s more user-friendly than a raw data set or spreadsheet. Pv Tables also make it easy to expand or collapse rows and columns to narrow down your results, giving you a more detailed picture of important data while cutting out unnecessary background noise.

Not only do Pivot Tables make it easier for you to track data more effectively, but they also make it easier to present information. Whether you’re speaking to family members, co-workers, or supervisors, Pivot Tables give a clear and concise picture of your data that’s easy on the eyes.

If you’re not happy with the layout of your Pivot Table, Excel makes it easy to manipulate and reformat information. Not only can you sort, filter, and group data. But you can also add, rearrange, remove, or change the order of fields. You can also easily change the Pivot Table form, choosing between Compact, Outline, or Tabular.

Tables are in compact form by default, but this may not suit your needs if you want headings for Row fields. If this is the case, you can switch your Pivot Table to Tabular Form, which displays one column per field and provides space for field headers, or Outline Form, which displays subtotals at the top of every group.

Creating Recommended PV Table

Pivot Tables are easy to make with just a few clicks of a button. If you’re new to Excel or Pv Tables in general, you may want to start out using Recommended Pivot Tables. This feature automatically comes up with a layout to match your data set. If you aren’t pleased with the final result, you can always experiment by tweaking rows and columns. To create a Recommended Pivot Table:

  1. Click on any cell in your original data set or table range.
  2. Go to Insert > Tables > Recommended Pivot Table.
  3. Excel analyzes your data and presents you with several options based on the categories it detects in your data.
  4. Select whichever table looks like it will best suit your needs. And then hit OK to create a Pivot Table on a new Excel sheet.

The Recommended Pivot Table feature is a relatively new one, introduced in 2013. It’s only available for users that have the Office 2013 suite or above. If you have an earlier version of the software, you’ll have to create Pv Tables manually.

Manually Creating Pivot Tables

Creating a Pivot Table manually is just slightly more complex than making a Recommended Pivot Table. In addition, it gives you more control over your end results and only takes a few more steps. Here’s how you can manually create a Pivot Table to display your data:

  1. Click on any cell in your original data set or table range.
  2. Go to Insert > Tables > Pivot Table.
  3. A box will pop up displaying the Create Pivot Table dialog. You can select and name a range of Excel cells, or import from an external data source. If you want to analyze multiple tables at once, check the “Add this data to the Data Model” box at the bottom of the popup screen.
  4. On this screen, you can also choose whether you want your report to be opened in a new sheet or an existing worksheet. If you choose to place your table in a current worksheet, you need to select both the file and the cell where you want your Pivot Table to be stored.
  5. Click OK, and you’ll see Excel create a blank Pivot Table and display the Pivot Table Fields list. Here, you’ll select the checkbox for any field you want to add to your Pivot Table.

Using Pivot Tables

Once you’ve finalized the formatting of your PV Table, you can take things one step further. You can turn it into a Pivot Chart.

This gives you an even more powerful way to display data. Especially if you’re planning on using it in a presentation. Pivot Charts add visualizations to data in the form of a graph or other chart type.

This makes it easy to summarize data and spot trends and patterns over time. Pivot Charts automatically update when you adjust your Pivot Table.

Conclusion

Excel is a powerful tool both at home and in the office, but datasets and spreadsheets can get confusing. Pivot Tables help you to make the most of your data. It allows you to eliminate unnecessary information and highlight what’s important to you or your business.

By using Pivot Tables, you can quickly come to conclusions and make informed decisions based on large caches of data. Pivot Tables also make it easy to wow bosses, coworkers, and investors during presentations.

Follow the steps laid out in this article. Spare yourself from a headache by effortlessly organizing and analyzing large data sets using Pivot Tables.

Whether you’re keeping on top of your home life or climbing the corporate ladder. Pivot Tables can come in handy when analyzing and presenting data.

how to unhide columns in excel

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

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

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

how to unhide columns in excel

How to Hide and Unhide Data in an Individual Cell

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

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

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

Hiding Data in Columns and Rows

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

How to Hide Data in Excel Using Shortcut Keys

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

How to Hide One or More Columns

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

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

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

How to Hide One or More Rows

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

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

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

How to Hide Columns or Rows Using the Home Menu

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

To Hide a Single Column or Row

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

To Hide Adjacent Columns or Rows

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

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

To Hide Non-Adjacent Columns or Rows

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

How to Unhide Columns in Excel

To Unhide All Hidden Rows and Columns Simultaneously

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

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

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

To Unhide the First Row or Column of Your Worksheet

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

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

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

how to make a drop down list in excel

The Drop Down Basics: How to Make a Drop Down List in Excel

This article shows you how to make a drop down list in excel.

It provides two ways for you to quickly and easily make drop down lists in an Excel spreadsheet.

You can follow the instructions to create a new drop down list that will help streamline efficiency when collecting information in your spreadsheet.

A dropdown list can be a useful thing in your spreadsheet. Especially if you want to make it easier for others to enter information on your sheet quickly.

With a dropdown list, you can give other users a quick set of options instead of having to type in each piece of information in every cell.

It is also helpful for the person responsible for the spreadsheet because it standardizes the data you want to collect, which may cut down on confusion or problems when it’s time to inspect your data or make calculations.

This article will go through a step-by-step process of how to make a dropdown list in Excel.

We will show you:

  • where to find the dropdown feature
  • how to enter a list of options that will appear in your dropdown list
  • and even how to use a dropdown list that you have in a different spreadsheet

Let’s get down to business and learn how to make a drop down list in excel.

How To Make A Drop down List In Excel: Getting Started

Today we are going to use an example of a group of friend’s favorite types of fruit. There are two ways you can do this.

The first involves making one spreadsheet. The other requires you to make two.

For the method involving two separate sheets.

The first spreadsheet, which will be the one where you will eventually insert your dropdown list, will contain the list of all the friends.

The second sheet will be where you create the possible selections of favorite fruits that will make up the dropdown list.

Excel sheet with names and favorite fruit how to make a drop down list in excel

Here is the list of people who will be taking the survey:

list of fruits how to make a drop down list in excel

And here is the sheet containing the list of fruits that the participants can select:

Now that we have our two spreadsheets set up, we can go about making the list of fruits in Sheet 2 into a dropdown list that users with access to Sheet 1 can select.

Finding the Dropdown Feature

The dropdown feature is not the easiest feature to find in Excel. However, with our help, you’ll be an expert in no time.

In Excel, you must scroll to the “DATA” tab in the menu.

Be sure you are currently on the sheet that you want your dropdown list to appear. In the section named “Data Tools” you will see the feature for “Data Validation” which should look like this:

excel menu how to make a drop down list in excel

Once you have found the button, begin by selecting the cell, or in this case the range of cells, you want the list to appear in.

Then click the data validation button.

After that, a dialog box will appear with several options.

Under the “Allow” box, click the dropdown menu and select “List,” which will look like this:

validation criteria how to make a drop down list in excel

After selecting “List,” you will then tell the spreadsheet what source you want to use, in this case, the list of fruit we made in Sheet 2.

data validation how to make a drop down list in excel

Note that in the “source” line there is now a somewhat complicated looking formula. That’s just telling spreadsheet 1 to look to Sheet 2 to find the items that will be in your dropdown list.

Once you press enter, you will be taken back to spreadsheet 1 with the names of the people taking the survey.

In the column underneath the “FAVORITE FRUIT,” the cells now have a dropdown list. You’ve now made a simple dropdown list for your spreadsheet.

favorite fruit drop down how to make a drop down list in excel

If you want to do all this in a single spreadsheet you will follow some of the same steps for setting up spreadsheet one, but don’t need to make a second sheet.

Instead of pointing your dropdown list to Sheet 2, when you fill out the “source” in the data validation box, you type in the possible options for the dropdown box.

The one-sheet method is useful when you have a limited number of responses, for example, if you are gauging attendance for an event and your only answers are attending/not attending.

But, as in our example, if you have a long list, it’s much easier to make everything on a separate sheet. Using two sheets will also make it easier to modify the list later.

More on Dropdown Lists

Now that you have made your dropdown list, there are a few things that you need to decide on. And you also have a few things to check.

First, if you only want the users to see one spreadsheet, you can hide the other spreadsheet by right-clicking on the tab. And then selecting “hide” from there.

However, if you do it all on one sheet other users could make changes to the dropdown list. Thus, if you are the only one who wants to be able to control what is on the lists. It’s better to follow the two sheet method.

You can also protect the spreadsheet or choose to lock it down so that no other user besides yourself can make changes.

One thing to check on is making sure that the cells are wide enough to display the whole entry.

In our example above, all of the words are relatively short and this isn’t a problem. But it is always a good thing to check before you send your spreadsheet to a bunch of people.

Modifying Your Dropdown List

Maybe you forgot to add your own favorite fruit, or just want more options. Don’t worry. It is quite simple to add items or remove them, from your dropdown list.

If you have made your dropdown list with two spreadsheets, which we feel is the better method, all you have to do is go to that sheet and add the items that you want to be included.

The dropdown list will then automatically be updated in the other spreadsheet.

Similarly, if you want to delete an item you just go to the sheet. From there, you delete whichever items you no longer want to include in your dropdown list. Easy as that!

If you created a dropdown list by manually entering the options, you just have to go into Data Validation again. And then enter the new items to the list from there.

Conclusion

You now know how to make a dropdown list in Excel!

Dropdown lists are useful any time you want users to enter specific data that you have already identified. It can keep things uniform and is more efficient for other users who are entering data.

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.

How to add cells in Excel (screen shots of cell data added up via the SUM formula).

How to Add Cells in Excel to Sum Up Totals Automatically

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=SUM(B2:H2)

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

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

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

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

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

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

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

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

Conclusion

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

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

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

How to merge cells in excel: Screen shot of an Excel spreadsheet with cells being merged.

How to Merge Cells in Excel (and Split Them) for Better Layouts

How to merge cells in excel: Screen shot of an Excel spreadsheet with cells being merged.

Knowing how to merge cells in Excel is a crucial skill. Although it may not seem like necessary information, it can be more useful than you might think.

Get the most out of your Excel user experience by reading on and learning more about merging and splitting your document’s cells.

Why Split or Merge Cells?

When you are organizing an excel document, the format in which you set up your sheet can be crucial to the information you are trying to lay out or present. A simple step like merging and splitting cells can give your document a cleaner look or make it appear less confusing.

Why learn how to merge cells in Excel (and split them)

  • Centering a title across all of the columns in your document.
  • Organizing multiple sections under one heading.
  • Splitting mistakenly merged cells.

It is important to remember that only cells that have been merged can be split, so it is a good idea to learn how to merge cells in Excel first.

How to Merge Cells in Excel

Cells can be merged horizontally, vertically, or in groups.

The first thing you will want to do is highlight two or more cells in a row or column that you would like to merge. For example, you could highlight cells A1, B1, and C1 since they are all in a row. After you have highlighted your chosen cells, locate the “Merge & Center” button on the formatting bar. If you simply want to merge these cells, click on the drop-down menu and select “Merge Cells.”

how to merge cells in excel

Let’s assume you are merging these cells to create a title that spans several columns. For formatting and visual reasons, rather than just merging your cells, you will want to center the title. To do this, select your cells and click the “Merge & Center” button on the formatting bar.

You can also merge cells in a column using this same method, as well as groups of cells. For instance, if you wanted to merge a block of cells, all you would have to do is highlight that block – say A1, B1, C1, A2, B2, and C2. Then, you would only use the same method of clicking the “Merge & Center” drop-down menu and selecting your merge.

Lastly, Excel allows you to merge several rows at once. If you wanted to merge the first, second, and third row across three cells, you can get all of that done in one shot rather than merging each row individually.

To do this, highlight all three rows together like you did for merging a block. Once highlighted, click the “Merge & Center” drop-down menu and select “Merge Across.” This option will merge all of your selected cells horizontally, but not vertically.

Important Data Reminder

It is important to remember that when you merge cells in Excel, the program will automatically use the data from the upper and left-most cell. The data entered in the other cells will be deleted. Some programs will remind you of this before finalizing your merge. After you learn how to merge cells in Excel, make sure you check to avoid losing data during merges.

To ensure you do not lose any critical data, maintain clear organization of your information. Always double-check what you are merging before you do so. It is a good idea to keep a backup of all your information as well.

How to Split Cells in Excel

Now that you’ve learned how to merge cells in Excel, you can learn how to split them. If the last thing you did was merge a cell and now you want to split it, you can always hit the “Undo” button or Ctrl +Z. However, that’s not always the case. Remember, only cells that have been merged can be split, or “unmerged.”

Splitting cells is very similar to the process of merging cells. First, select the merged cell that you would like to split. Then, locate the “Merge & Center” button on the formatting bar again and click it. Your cells will split into their original cells, and the information will move to the upper and left-most cell.

Splitting cells will not return your lost data to its original cell, so do not use this method as a way to retrieve information that was lost due to merging.

Splitting Cell Information

While single cells cannot physically be split prior to merging, information within a cell can be split into different cells.

Let’s say your cell contains this information:

Blue 10; Red 15; Yellow 11

This information can be split so that Blue, Red, and Yellow each have their own cell.

To do this, first select the cell. Next, click into the Data tab on the formatting bar. Locate the button that reads “Text to Columns.” A window titled “Convert Text to Columns Wizard” with several options will open.

Once you have this window opened, select “Delimited,” then click “Next.” In your next step, you’ll see a list of delimiters, or options of where to split your content. In our case, we would select semicolon since we have semicolons separating our items.

Select semicolon and then click next. In the next window, you can select the data format for your new columns. The program will default the new columns to the original cell, so from here you can click finish. Your finished product will have split the cells into:

Blue 10
Red 15
Yellow 11

As a reminder, your split content will automatically overwrite any content in the cell to the right. For this reason, make sure the number of cells you need next to the one you intend to split are empty.

Conclusion

Excel is an excellent way to organize and present information for various businesses. Merging and splitting cells and cell content can serve to give you an Excel document that is visually presentable and highly organized.

This article contains all you need to know about how to merge cells in Excel and how to split them. Use this guide to master the content in your cells and get the most out of your Excel spreadsheets.

excel sumif

How to Use the SUMIF and SUMIFS Functions in Excel

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

In this tutorial, we’re going to cover:

The difference between SUMIF and SUMIFS functions.

How to use SUMIF and SUMIFS.

Common examples of formulas.

The Basics of SUMIF Functions

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

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

Using SUMIF Functions

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

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

SUMIF(range, criteria, [sum_range])

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

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

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

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

Examples of SUMIF Functions

sumif example

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

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

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

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

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

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

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

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

Comparing SUMIF and SUMIFS

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

When to Use SUMIFS Functions

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

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

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

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

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

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

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

Examples of SUMIFS Functions

using sumifs excel

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

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

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

Conclusion

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

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

Excel for iPad – Paste as Values

Being a long standing Excel user and an avid Apple products consumer, I downloaded the new Microsoft Excel iPad app in April 2014 and quickly found out that you can’t do anything of substance until you pay a subscription fee. After looking at the available options I decided to purchase the Office 365 Personal yearly subscription, which is $69 + tax USD. It comes with full access to all Office iPad apps, OneDrive document storage in the cloud, and allows me to install the latest Office applications on a PC or Mac, which I haven’t done as of yet.

Here is Microsoft’s official blurb on what you get:

Office 365 Personal enables you to install the latest full desktop version of Office applications, including Word, Excel, PowerPoint, OneNote and Outlook, on 1 PC or Mac, in addition to 1 Windows tablet or iPad — so you can use all the features offline as well as online. You can also install Office on multiple smartphones.

I’m tempted to use my PC because I get to install Office 2013, which I don’t have, as compared to using Office 2011 that I already have on my iMac. But Microsoft is bringing out Office 2014 this year and I’ve decided to wait and see how they incorporate this version into Office 365 Personal. But I digress.

Excel on the iPad

The Excel app is more intuitive and much better than I imagined it could be. I’ve tried to use Numbers, but frankly it always feels like an alien designed it. I’m glad Microsoft held off until they “got it right” for the iPad.

I created a spreadsheet to document my vacation expenses and used it for a week to make entries. The file is saved automatically to OneDrive in the cloud were I can access it with the Safari browser on my iMac or my wife’s MacBook Air – or any browser for that matter. I can even view or modify the spreadsheet using the Office Mobile app on my iPhone.

The Ribbon menus are straightforward, albeit a little sparse in functionality. Yet the popup and shortcut menus help to increase that functionality, as you shall see in the following example.

I wanted to enter the current date into the aforementioned spreadsheet, but didn’t have a calendar handy so I entered the TODAY() formula. I needed to Copy and Paste as Values, but couldn’t find the Paste as Values functionality on the Ribbon. As it turns out, it was relatively easy to find.

When you enter an equal sign into a cell a list of formulas appears in a popup menu, which is similar to the desktop version of Excel. I tapped on TODAY() and hit the return button on my keyboard to enter the formula into the cell. Easy-peasy.

To copy the formula just tap the cell, a popup menu appears, and then you tap Copy. A moving rectangle on the cell border lets you know there is a copy value in memory.

Pasting the formula as values is not readily apparent, but easily discovered. Just tap the cell, then tap Paste from the popup menu. Notice the paste format icon at the bottom right corner of the cell – very similar to the desktop version on Excel.

Tap the paste format icon to bring up a menu where you tap Paste as Values and BOOM, you’re done. The screen shot sequence below shows what I’m talking about.

Paste as Values Excel iPad App Steps 1-3

Paste as Values Excel iPad App Steps 4-5

If you use Microsoft Excel, especially on a PC, this is the app for you. The look, feel, and functionality will be very familiar. The only problem is deciding if you need full functionality of the app and will purchase a yearly subscription to get it.

Note: I type on a Logitech Ultrathin Keyboard Folio for iPad Air (affiliate link) when I’m using the Excel app on my iPad, which increases the screen real estate immensely by not having an onscreen keyboard. The added benefit is that keyboard shortcuts work similar to the desktop version of Excel 2011 for Mac.

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.

Where is Control+Home for Excel on a Mac

I wrote a post stating that I could not find the Windows Ctrl+Home keyboard shortcut equivalent on a Mac. Well I’m here to tell you that I found the keyboard shortcut combination that does the same thing on a Mac. The Excel Gods are with me. Hallelujah!

Finding My Way Home

The key to finding this elusive keyboard shortcut is in the Keyboard Viewer. On your Mac select the Apple icon () and click System Preferences… Select Keyboard, and then make sure to click the Keyboard tab. Check the box for: Show Keyboard & Character Viewers in menu bar.

Excel home key on mac

Click the Keyboard Viewer icon Keyboard Viewer Iconin your Mac menu bar and a nice replica of your Mac keyboard will appear.

Keyboard Viewer Mac

You will notice that this viewer reflects the keys you tap on your keyboard. The screen shot above shows the Command and Shift keys are depressed. The Keyboard Viewer will also show different symbols when you press various keys, like fn, Control, Option, Command, etc.

This is where I noticed something interesting. While depressing the fn key, the left arrow button changes its angle to point up about 30 degrees. Knowing that allowed me to do a little testing in Microsoft Excel 2011 for the Mac.

Excel Control+Home Key on Mac

What I found is that the Windows Control+Home keyboard combination can be replicated on a Mac by either of the following keyboard shortcut combinations. This is the home key on Mac:

fn+Command+Left Arrow

fn+Control+Left Arrow

Another mystery solved.

Keyboard Shortcuts

Another aid in finding keyboard shortcuts comes in the form of an overlay for your Mac keyboard. The kind folks over at Excel Skin™ gave me an overlay that slips over the Mac keyboard and shows, via color coding, a wide array of shortcuts that work in Excel for Mac. Here is why you might want an excel skin.

Extracting Integers and Fractions in Microsoft Excel

Sometimes you need to extract the integer portion of a number. Sometimes the fractional part. Sometimes both. Excel makes it easy to get the integer and somewhat harder to get the fraction. If you just want the answer, skip to the technical details.

The Integer Part: Excel INT Function

What could be easier than the Excel INT function? I mean INT almost screams INTEGER. So the name is intuitive. You almost “know” what it’s going to do, even if you haven’t used it before.

With only one argument, it’s execution is even simpler. Just feed it a number and out pops the integer portion.

Below you can see I have the number 14.125 in cell D1 and the formula =INT(D1) returns the integer 14 in cell E1.
Excel INT Function

Date/Time Tip: A practical application for the INT function is to extract the date value from a date/time number. An example is a date/time value like 2/14/2013 9:04 AM in cell A2 and you only want the date. Using the formula =INT(A2) will strip out the time and leave the date 2/14/2013.

The Fractional Part

Here is where I would like some simplification. I mean, if we have the INT function for extracting an integer, you would think that there would be a FRAC function, or a simple name like that, to extract the fractional part.

If there was a FRAC function, you could also imagine that it would have only one argument. Just feed it a number and out pops the fraction. Simple. Elegant. Intuitive. No thought required. But, I digress.

To extract the fractional part of a number we can use the MOD function, which has not one, but two arguments. The reason it has two arguments is because it does more than extracting the fractional part. Forget about that.

Here is what you need to know about the MOD function and how to extract the fractional part of a number. The second argument of the MOD function is 1. Remember that. To reiterate, 1 is the second argument.

The first argument of the MOD function is the number. Shown below, you can see the number 14.125 in cell D1. The formula =MOD(D1,1) returns the fractional part 0.125 in cell E1.
MOD Function

Date/Time Tip: A handy way to extract a time value from a date/time number is to use the MOD function. Per our previous example, the date/time number 2/14/2013 9:04 AM in cell A2, the formula =MOD(A2,1) will return 9:04 AM.

Technical Details

Assume you have a number in cell A1.

To extract the integer value, use the formula:
=INT(A1)

To extract the fractional value, use the formula:
=MOD(A1,1)

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

Related Posts Plugin for WordPress, Blogger...