Pivot Tables

Pivot tables are one of  excel is most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.
Our data set consists of 214 rows and 6 fields. Order ID, Product, Category, Amount, Date and Country.
Pivot Table Data in Excel
1. Click any single cell inside the data set.
2. On the Insert tab, click PivotTable.
Insert Excel Pivot Table
The following dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet.
3. Click OK.
Create PivotTable Dialog Box

Drag fields

The PivotTable field list appears. To get the total amount exported of each product, drag the following fields to the different areas.
1. Product Field to the Row Labels area.
2. Amount Field to the Values area.
3. Country Field to the Report Filter area.
Drag Fields to Areas
Below you can find the pivot table. Bananas are our main export product. That’s how easy pivot tables can be!
Pivot Table
Click any cell inside the Total column.2. The PivotTable Tools contextual tab activates. On the Options tab, click the Sort Largest to Smallest button (ZA).
PivotTable Tools Contextual Tab
Result.
Sorted Pivot Table

Filter

Because we added the Country field to the Report Filter area, we can filter this pivot table by Country. For example, which products do we export the most to France?
1. Click the filter drop-down and select France.
Result. Apples are our main export product to France.
Filtered Pivot Table
Note: you can use the standard filter (triangle next to Product) to only show the totals of specific products.

Change Summary Calculation

By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation that you want to use, execute the following steps.
1. Click any cell inside the Total column.
2. Right click and click on Value Field Settings…
Value Field Settings
3. Choose the type of calculation you want to use. For example, click Count.
Summarize Value Field By
4. Click OK.
Result. 16 out of the 28 orders to France were ‘Apple’ orders.
Count

Two-dimensional Pivot Table

If you drag a field to the Row Labels area and Column Labels area, you can create a two-dimensional pivot table. For example, to get the total amount exported to each country, of each product, drag the following fields to the different areas.
1. Country Field to the Row Labels area.
2. Product Field to the Column Labels area.
3. Amount Field to the Values area.
4. Category Field to the Report Filter area.
Create Two-dimensional Pivot Table
Below you can find the two-dimensional pivot table.
Two-dimensional Pivot Table in Excel
To easily compare these numbers, create a pivot chart and apply a filter. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful pivot table features Excel has to offer.
Pivot Chart

Excel 2010

Saving workbooks

Video: Saving Workbooks in Excel 2010

Launch video!

When you create a new workbook in Excel, you’ll need to know how to save it to access and edit it later. excel allows you to save your documents in several ways.
Optional: You can download this example for extra practice.

To use the Save As command:

Save As allows you to choose a name and location for your workbook. Use it if you are saving a workbook for the first time or if you want to save a different version of a workbook while keeping the original.
  1. Click the File tab.
  2. Select Save As.
    Clicking Save As
  3. The Save As dialog box will appear. Select the location where you want to save the workbook.
  4. Enter a name for the workbook, then click Save.
    The Save As dialog box
If you are using Windows 7, you will most likely want to save files to your Documents library. For other versions of Windows, you will most likely want to save files to the My Documents folder. For more information, check out our lessons on Windows 7 and Windows XP.

To use the Save command:

  1. Click the Save command on the Quick Access toolbar.
    Saving a workbook
  2. The workbook will be saved in its current location with the same file name.
If you are saving for the first time and select Save, the Save As dialog box will appear.

To use AutoRecover:

Excel automatically saves your workbooks to a temporary folder while you’re working on them. If you forget to save your changes or if Excel crashes, you can recover the autosaved file.
  1. Open a workbook that was previously closed without saving.
  2. In Backstage view, click Info.
  3. If there are autosaved versions of your workbook, they will appear under Versions. Click the file to open it.
    Opening an autosaved file
  4. A yellow caution note will appear on the Ribbon of the workbook. To restore this version of the workbook, click Restore, then click OK.
    Restoring a file
By default, Excel autosaves every 10 minutes. If you are editing a workbook for less than 10 minutes, Excel may not create an autosaved version.
If you do not see the file you’re looking for—or if you’re looking for an autosaved version of a file that has no previously saved versions—you can browse all autosaved files by clicking the Manage Versions button and selecting Recover Unsaved Workbooks from the drop-down menu.
Accessing all autosaved files

An Excel Template for Every Occasion

Getting the best out of excel can sometimes be daunting. Known for its functions, formulas, and data analysis capacities, it might leave you wondering how you can use Excel as a practical productivity tool in your life, without the steep learning curve.
You can streamline your organization skills using the massive range of Excel templates: money, budgeting, payslips, project management, and much more can be efficiently managed, freeing up time for…well, more awesome things.
You can download well balanced, auto-updating Excel templates that you, or anyone you share your work with, can use straight away. It’ll be EXCEL-LENT!

Budget Templates

I’ve included a few budget templates as they come in numerous flavors. I’m still relatively new to budgeting. Having been quite the carefree student, I suddenly found myself a family-man, so learning to account for each penny quickly became a must. I have some of these Excel budget templates to thank.

Family Budget Planner

I went from party-animal student, to student with partner-expecting-child, to terrified-student-father in a matter of months (let’s assume it was 9…). This little template helped me no end, and it’ll certainly help you, too. It’s thorough, well planned, and has a number of savings and planning functions.
It isn’t limited to Excel either. Once downloaded, this template works perfectly well in Open Office, LibreOffice, and Google Sheets – all perfect for really squeezing that budget, if propriety software has its own column.

Money Management

A big part of understanding your budget is effective money management: where it’s coming from, where it’s going, your debtors, your creditors, and everything else you can think of. When you have so many different sources coming into and out of multiple checking accounts, plus a mortgage, plus your business accounts, you can quickly lose track of incomings and outgoings. Unless you use a handy spreadsheet like this:

Event Budget

I’m planning my wedding at this very moment. Huhrah /s. At the moment, the immense excitement is being seriously overawed by the minute finance management my partner and I seem to spend most of our waking time talking about. We’ve kept right on top of things using a dedicated Event Budget planner – and so far it’s helped us come in way under-budget.
Event Budget Excel
We’ve used it for our wedding planning, but you can use it for your fundraiser, your social event, or if you’re throwing a big ol’ shindig.

Fundraiser Thermometer Template

Visualizing your progress toward a finance goal can provide the extra impetus to get you over the line – and further! Some people are just visually inspired, whilst others find visualizing a numerical amount makes it easier to interpret. Give this one a look:
Fundraiser Thermo Excel
It isn’t the flashiest, but you can add On Track Targets, calculate donations for specific period, or donations for a single day.

Maintenance Schedule Template

Home ownership comes with many additional tasks, aside from the massive repayments. You can no longer call your landlady and cry down the phone because the gas boiler is broken and you really, really wanted a hot bath.
Home Maint Excel
No, you’ll need to keep up with regular home maintenance or else it’ll literally start falling apart. Keep abreast of what needs doing, and when, with a handy home maintenance template. In this case, I’ve used an inbuilt Excel template as it has everything I need, but you can search for ones that suit your home repair schedule.

Database Template

Much like the budget templates, I’ve included a few database templates as they come in so many flavors. You might have to try a few before you find one that fits your data processing requirements, be that home or business use.

CRM

You can use Excel as an effective CRM system for your business. Streamline communications with existing and potential clientele to gain a competitive edge, using a free template. As they say, take care of the little things, and the big things will take care of themselves.
CRM Excel

Inventory Management

Running a small business – or a large one – requires organization. If you’ve a warehouse full of stock, you need to keep track of each coming and going, ensuring that each day your database is updated. It doesn’t take Warren Buffet to understand stock management. This is an inbuilt Excel template, but there are plenty more available across the web.
Warehouse Inventory Excel

Payroll Management

Managing your employees is essential – they’d be mighty disappointed if they realized all of their financials were written on a napkin, stashed underneath that snazzy Home Depot paperweight. Use a preformatted template to keep track of your employees – their total and hourly rates, their SS numbers, their over-time status, and 401(K) contributions.
Payroll Management Excel

Planning Templates

Are you a project manager? Or builder? Or simply work in an office, with deadlines? Let’s go further: do you actively plan aspects of your live to ensure there aren’t any massive blips? You’ll love these Excel planning templates.

Gantt Chart

A Gantt Chart template is essential for any project you are tasked with managing. Ever tried managing without one? I cannot fathom how you succeeded, if indeed you did. Gantt Charts are another excellent time visualization tool, helping you plan each aspect of your project in correlation. Use one – I guarantee it will help.
Gantt Chart Excel
The best part is the automation. Add a detail, or edit the timeline, and your template will automatically update each correlating detail.

Continuous Monthly Calendar

I’ve only just started using a continuous monthly calendar, but it has quickly made a few differences to my work flow. Along with the budgeting, getting to grips with a real-life work flow following the many years of university was a challenge, but something I’ve steadily improved on over the years. Little tools like this can make all the difference if you’ve several different sources of work running concurrently, with smatterings of deadlines along with more work appearing.
Calendar Excel

Weekly, Bi-Weekly and Monthly Timesheets

Keep abreast of your hours worked. Even if you have a login system in the workplace, you should be tracking your hours. If there are any discrepancies or shortcomings in your paycheck, you’ve tracked each and every hour worked. It also helps you plan your incomings and outgoings a little better, and understand the value in time.
Bi-Weekly Payslip

Other Planning Templates

It isn’t always about business. The inbuilt Excel templates feature gardening schedules for the green fingered, family meal planning for the waste conscious, and detailed family travel itineraries for those efficient fun-having parents. Scroll through the Excel template repository, or search using “Planning” to bring up a number of relevant results.
Plant Inventory Excel

Excel Template Sources

If you want to continue your productivity drive, check out these Excel template sources:
  • Spreadsheet123
  • Vertex42
  • Office Store
  • Excel Templates
  • Chandoo
Once you start managing more of your life with Excel templates, you’ll have so much time for other activities, you’ll have to design a new template to keep track of your new-found time-wealth. And hopefully some monetary wealth, too.
Do you use templates? What’s your favorite time-saver? Are you a meticulous planner? Let us know below!

Free Online Education: Word & Excel Training – Touch Typing – Math & Language Games

In this website you can learn and self practice all the basics of excel
Choose one of the subjects from above, and a list of relevant videos will appear. It includes many tutorial videos with easy step by step manual instructions.
Free Microsoft Word tutorials, as well as the other subjects, could be found on the left menu.
You will notice that all of the learning materials are freely available online, and the videos are short and very easy to understand, planned especially for beginners.
Though they were recorded in Excel 2007, they are totaly relevant to the 2010 and 2013 versions as well.
For your convenience, the spreadsheets demonstrated in the videos are available for instant download.
This way you can immediately practice what you have just seen and learned.
Just look for the “Try it yourself” link above the video, click it and choose whether to open the spreadsheet inside your browser, or better download it to your computer and then open with Excel.
Who can benefit from these Excel tutorials?
  • School and College students learning an IT course (which usually includes the MS Office 2010 applications) and wish to rehearse at home the materials taught in class.
  • Job applicants who are going to be tested on their spreadsheet skills as a part of their job interview.
  • People already working with this application, who need help with freshening up their skills from time to time, or are looking for some specific answer to their problem such as how to create charts or to use formulas.
Actually anyone who seeks some quick Excel help can benefit from these short online lessons.

 

What else is there to find on this website?
Free typing practice – how about improving your keyboarding skills? Do you still type with your index fingers? Then this might be the time to advance one step further: You will find a huge section of typing games, enabling you to practice and improve your typing abilities not with boring passages, but with fun, dynamic and challenging activities.
I’ve also devoted the last couple of years to thoroughly search and index free learning materials on the web, mainly for elementary school, with a special emphasis on the younger ages (mostly 2nd, 3rd & 4th grades), though middle school materials are also presented. You can therefore find a huge assortment of free online math games, some of which (place value, rounding, skip counting, addition & multiplication) I’ve programmed by myself.
There are also language arts online activities, including games to practice spelling, many crossword puzzles for kids (originally and unique to this website), and also unique word search puzzles. These activities aim to enhance the child’s vocabulary as well as spelling skills.
The learning resources on this website are usually used by teachers to complement the materials taught in class, and by homeschool parents wishing to engage their kids with educational interactive activities.
I hope that you can find benefit from this website, and I hope that I succeed just in a tiny bit to bring my vision of “Free Education For All” into reality.
You can contact me at : www.excel-helps.com

How to correct a #VALUE! error

Applies To:  , excel Excel 2013 , Excel 2010 , Excel 2007 , Excel 2016… for Mac , Excel Starter
The most common reason for the #VALUE! error is that a formula is expecting numbers in a cell, but instead it finds spaces, text, or other characters.

Top solution – Try using functions instead of operators

Formulas with math operators like +, -, *, ^ and / may not be able to calculate cells that contain text or spaces. In this case, try using a function instead. Functions will often ignore text values and formulate everything as numbers, eliminating the #VALUE! error.

Replace mathematical operators with functions

If you’re not sure what to do at this point or what kind of help you need, you can search for similar questions in the Excel Community Forum, or post one of your own.
If you want to move forward, then the following checklist provides troubleshooting steps to help you figure out what may have gone wrong in your formulas.

Finding where a formula is broken with the Evaluate Formula Wizard
If your formula is throwing a #VALUE! error, but you don’t know where it is, you can use the Evaluate Formula Wizard. Select the cell with the error and goto Formulas > Evaluate Formula, and an interactive dialog will appear:

Use the Evaluate Formula tool to see what part of a formula is causing an error

Each time you click the Evaluate button, Excel will step through the formula and calculate each part individually, then display the results. This won’t fix your formula, but it can help you figure out where it’s breaking, so you can go fix it. In this case the formula =A2+B2+C2 breaks because of a leading space in cell A2, but you can’t see it. The Evaluate Formula Wizard does though and it shows that the value is =” “+B2+C2, where the ” ” indicates a blank space.

Check for hidden spaces inside cells
Sometimes cells look blank, when in fact they may have hidden spaces. Double-click a cell that your formula is referencing (or press F2), and check for spaces. In the following illustration, there are extra spaces to the left of cursor in cell A2.

VALUE Error caused by leading spaces in cell A2

Try deleting the spaces, or select the cell and press Delete to see if the error goes away. If you have many rows to check, you can use the ISBLANK() function in an empty column to see if cells are truly blank or not. In the following illustration, cell A2 has a hidden space that you can’t see, and the ISBLANK function in E2 returns FALSE. Whereas A3 is truly blank and the ISBLANK function returns TRUE.

Use ISBLANK to identify potential errors - Formual in cell E2 is =ISBLANK(A2)

Tip: Use Auto Filter to mass delete blank cells. You can click Data > Filter and Filter for Blanks on the column with blanks (Auto Filter will see cells with leading spaces as blanks as well). Next, select the entire column and press CTRL+G (Goto) > Special > Visible Cells only, then press DELETE to clear everything.

Use AutoFilter to display only cells with Blanks

Use Goto > Special > Visible Cells only to hide non-blank rows so they can be deleted

Clean text using the CLEAN function
Sometimes when you import data from external sources, it can come into Excel with non-printing characters like ^ or ‘. Unfortunately, these characters can cause problems in formulas and it can be hard to delete them. However, you can use the CLEAN function to strip the characters out, and convert the values from text to numbers. You can then copy the CLEAN function range and paste just the cell values back over the original range. Here’s how: Select the CLEAN function range, and then press CTRL+C. Select the original cells and click Home > Paste > Paste Special > Values (or Alt > E > S > V for keyboard shortcuts for the PC – For MAC you can use Image of the MAC Command button icon +OPTION + V > V > Enter).

Use CLEAN to remove non-printing characters - formual in cell E2 is =CLEAN(A2)

Clean text using the TRIM function
Sometimes leading or trailing spaces can cause problems. Excel will generally try to remove these spaces in simple values. For example, it will remove spaces if you type “ 123”, but it might not be able to do the same with date values like “ 1/1/16”. In this case, you can use the TRIM function to remove those leading and trailing spaces, and then reference the converted TRIM value in your calculation, or use the Copy > Paste Special > Values method to replace the original values.
In this example, leading spaces in the date in A2 cause a #VALUE! error with =B2-A2. But by using =TRIM(A2), we can then use =B2-D2 to resolve the error.

USE TRIM() to remove leading or trailing spaces - Formula in cell D2 is =TRIM(A2)

Check if the formula is referencing cells that contain text
Right-click a cell that the formula is referencing, and then click Format Cells (or use CTRL+1). Make sure the format is not Text. Sometimes this is not practical for more than one cell, so if you have more than one to check, insert a new column and use the ISTEXT() function to see if the cells are formatted as text. In the example below “173 0” isn’t a valid number, so Excel will see it as text. This can often be the result of a typo. Note that ISTEXT won’t resolve the error, it will just tell you if text could be causing the issue.
Use ISTEXT() to identify potential errors caused by non-numeric values - Formula in E3 is =ISTEXT(C3)

Your workbook uses a data connection that isn’t available
To fix this, restore the data connection, or consider importing the data if possible. This can happen a lot with distributed workbooks, where you might send a workbook to someone who doesn’t have the data connection. In cases like this it can be a good idea to create a distribution copy where you copy an entire worksheet(s) and Paste > Special >Values, which will eliminate formulas and links.

Replace the #VALUE! error with something else
Sometimes you just want to replace the #VALUE error with something else like your own text, a zero or a blank cell. In this case you can add the IFERROR() function to your formula. IFERROR() will check to see if there’s an error, and if so, replace it with another value of your choice. If there isn’t an error, your original formula will be calculated. IFERROR will only work in Excel 2007 and later versions. For earlier versions you can use IF(ISERROR()).
Warning: IFERROR is a blanket error handler, meaning that it will suppress all errors, not just the #VALUE! error. It’s not advisable to use IFERROR until you are absolutely certain that your formula performs the way that you want. Otherwise, you won’t see potentially valuable error messages that might indicate you have a problem.
Here’s an example of a formula that has a #VALUE! error due to a leading space in cell D2:

Example of a #VALUE! error caused by a leading space in cell D2 - Formula in Cell E2 is =C2-D2

And here’s the same example with IFERROR applied to replace #VALUE! with zero:

Use IFERROR() to suppress all errors - Formula in cell E2 is =IFERROR(C2-D2,0)

You could also use =IFERROR(C2-D2,””) to display nothing instead of 0, or even substitute your own text, like: =IFERROR(C2-D2,”Discount Error”).
Unfortunately, you can see that IFERROR doesn’t actually resolve the error, it simply hides it. So be certain that hiding the error is better than fixing it.

Excel OR Function

Formula examples

If NOT this or that
If cell is x or y and z
If cell is this OR that
Highlight dates that are weekends
Purpose
Test multiple conditions with OR
Return value
TRUE if any arguments evaluate TRUE; FALSE if not.
Syntax
=OR (logical1, [logical2], …)
Arguments
  • logical1 – The first condition or logical value to evaluate.
  • logical2 – [optional] The second condition or logical value to evaluate.
Usage notes
Use the OR function to test multiple conditions at the same time, up to 255 conditions total.
For example, to test if the value in A1 OR the value in B1 is greater than 75, use the following formula:
=OR(A1>75,B1<75)
OR can be used to extend the functionality of functions like IF. Using the above example, you can supply OR as the logical_test for an IF function like so:
=IF(OR(A1>75,B1<75), “Pass”, “Fail”)
This formula will return “Pass” if the value in A1 is greater than 75 OR the value in B1 is greater than 75.
If you enter OR as an array formula, you can test all values in a range against a condition. For example, this array formula will return TRUE if any cell in A1:A100 is greater than 15:
={OR(A1:A100>15}
Notes:
Each logical condition must evaluate to TRUE or FALSE, or be arrays or references that contain logical values.
Text values or empty cells supplied as arguments are ignored.
The OR function will return #VALUE if no logical values are found
Web : excel

How to Use Excel

Excel is a powerful spreadsheet program made by Microsoft Office. You can create and format spreadsheets and workbooks (collections of spreadsheets), build models for analyzing data, write formulas, perform many calculations, and present professional charts. Cash flow statements, income statements, budgets, calendars, or profit and loss statements can all be easily created if you know how to use excel .

Open the Excel program. There may be a shortcut on your desktop, or you may need to go to “Start” and then “Programs” to locate the Excel icon.
Start a new workbook (an Excel file). Click “File” and “New.” Under “Available templates,” click “Blank Workbook,” then “Create.” A blank workbook will open.
Save the workbook. Click the Office button (or File tab if you have an earlier version of Excel) and select “Save as.” Choose a location on your computer to save your file (such as the “my documents” folder), type the name of your workbook in the “File name” box, and be sure your file type is set to “Excel Workbook”

Familiarize yourself with the tabs on the ribbon at the top of your workbook.They are File, Home, Insert, Page Layout, Formulas, Data, Review and View.

Familiarize yourself with the language needed to use Excel. Knowing the terminology of the technology is important for being able to use it easily and understand step-by-step guides.
A row is a section that goes right to left across the screen (indicated by numbers along the left side of the screen).

  1. A column is a set of data that goes from the top to the bottom of the worksheet, and it is identified with a letter at the top of the sheet.

A cell is any individual square of the worksheet that data may be placed in.

6. Prepare your worksheet for data storage. Every Excel workbook has 3 worksheets by default. Sheet 1 opens by default and you can see this tab at the bottom of the window.
Rename a sheet by right clicking on the Sheet 1 tab.
Select “Rename” and type the new name for your sheet.

If necessary, add sheets by clicking the button to the right of “Sheet 3” that shows a sheet of paper with a star in the corner.

On the top row of your sheet, type a title in each cell to identify what will be placed in each column. For example, you might type Name, Date, and Amount. The rows underneath these titles are for your data.
Save frequently. When you are entering data, you may want to save your work frequently by clicking the floppy disk symbol at the top left of your screen or clicking on the office button and selecting “Save.” Alternately, you can hold down the control “Ctrl” key on your keyboard while you type “S.”