Excel Tools

2011-03

How to Create A Linkable Hot Image

You might have noticed that some Web sites feature an hot image map to help navigate the site. An image map is a single image, but it’s sensitive to the location at which you click it on hot area. This tip demonstrates how to create a linkable hot image.

2010-11

How To Highlight Entire Current Row And Column In Excel

How can I use VBA or conditional formatting to highlight the entire current row and column of the current cell?

In your sheets worksheets selection change event, you can use something like this:

Getting Coefficients Of Chart Trendline

When you add a trendline to a chart, Excel provides an option to display the trendline equation in the chart. This article describes how to create formulas that generate the trendline coefficients. You can then use these formulas to calculate predicted y values for give values of x.

2010-10

How To Create A Splash Screen For An Excel Workbook

This article describes how to create a splash screen for a Excel workbook. A splash screen appears when the workbook is opened and disappears automatically after a specific time has elapsed.

Setting it up

Follow these instructions to create a splash screen for your project.

Using COUNTIF function to compare two columns in Excel

I have an excel document with columns A and B. Row A has 2595 values. Row B has 411 values.

I’m trying to figure out how I can compare these two columns by marking column C with the number of times we find a value(s) from column A.

Use keyboard shortcut to Zoom in or out on Excel Workbook

For most purposes, Excel allows you to issue commands and perform functions by using either the mouse or the keyboard. Unfortunately, Excel does not provide “equal access” for all commands. For instance, it is relatively easy to zoom in or out using the mouse, but there is no easy way to do it using the keyboard (other than using the keyboard to traverse the menus and select a zoom setting).

Choose the best type of chart in Excel

One factor to consider as you want to choose the best type of chart in Excel is the relative strengths and weaknesses of each type of graph. One could, of course, write an entire book on this subject. However, May you find it useful to examine the strengths and weaknesses described in the paragraphs that follow to choose a graphic.

How to set default zoom size for specific Workbooks when opened

If you work with workbooks first worked on by your colleagues, you may be frustrated by the zoom factor applied to those workbooks by those others. For instance, if your colleague (Wanda) has a huge monitor, it wouldn’t be uncommon for her to reduce the zoom factor on Excel to 75% or even 60%. The purpose, of course, is so she isn’t overpowered by things that look very large at the full zoom factor.

Excel Tools

Introduction

 

This section is for users with no knowledge of Excel.

Microsoft Excel is one of the most used software applications of all time. Hundreds of millions of people around the world use Microsoft Excel. You can use Excel to enter all sorts of data and perform financial, mathematical or statistical calculations.

1 Range: A range in Excel is a collection of two or more cells. This chapter gives an overview of some very important range operations.

2 Formulas and Functions: A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel.

Basics

 

This section explains the basics of Excel.

1 Ribbon: Excel selects the ribbon's Home tab when you open it. Learn how to minimize and customize the ribbon.

2 Workbook: A workbook is another word for your Excel file. Excel automatically creates a blank workbook when you open it.

3 Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. By default, each Excel workbook contains three worksheets.

4 Format Cells: When we format cells in Excel, we change the appearance of a number without changing the number itself.

5 Find & Select: Learn how to use Excel's Find, Replace and Go To Special feature.

6 Templates: Instead of creating an Excel workbook from scratch, you can create a workbook based on a template. There are many free templates available, waiting to be used.

7 Data Validation: Use data validation in Excel to make sure that users enter certain values into a cell.

8 Keyboard Shortcuts: Keyboard shortcuts allow you to do things with your keyboard instead of your mouse to increase your speed.

9 Print: This chapter teaches you how to print a worksheet and how to change some important print settings in Excel.

10 Share: Learn how to share Excel data with Word documents and other files.

11 Protect: Encrypt an Excel file with a password so that it requires a password to open it.

back to top

Functions

 

Discover how functions in Excel help you save time. If you are new to functions in Excel, we recommend you to read our introduction to Formulas and Functions first.

1 Count and Sum: The most used functions in Excel are the functions that count and sum. You can count and sum based on one criteria or multiple criteria.

2 Logical: Learn how to use Excel's logical functions such as the IF, AND and OR function.

3 Cell References: Cell references in Excel are very important. Understand the difference between relative, absolute and mixed reference, and you are on your way to success.

4 Date & Time: To enter a date in Excel, use the "/" or "-" characters. To enter a time, use the ":" (colon). You can also enter a date and a time in one cell.

5 Text: Excel has many functions to offer when it comes to manipulating text strings.

6 Lookup & Reference: Learn all about Excel's lookup & reference functions such as the VLOOKUP, HLOOKUP, MATCH, INDEX and CHOOSE function.

7 Financial: This chapter illustrates Excel's most popular financial functions.

8 Statistical: An overview of some very useful statistical functions in Excel.

9 Round: This chapter illustrates three functions to round numbers in Excel. The ROUND, ROUNDUP and ROUNDDOWN function.

10 Formula Errors: This chapter teaches you how to deal with some common formula errors in Excel.

11 Array Formulas: This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell.

back to top

Data Analysis

 

This section illustrates the powerful features Excel has to offer to analyze data.

1 Sort: You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.

2 Filter: Filter your Excel data if you only want to display records that meet certain criteria.

3 Conditonal Formatting: Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell's value.

4 Charts: A simple Excel chart can say more than a sheet full of numbers. As you'll see, creating charts is very easy.

5 Pivot Tables: Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.

6 Tables: Tables allow you to analyze your data in Excel quickly and easily.

7 What-If Analysis: What-If Analysis in Excel allows you to try out different values (scenarios) for formulas.

8 Solver: Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems.

9 Analysis ToolPak: The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis.

back to top

VBA

 

Excel VBA (Visual Basic for Applications) is the name of the programming language of Excel.

1 Create a Macro: With Excel VBA you can automate tasks in Excel by writing so called macros. In this chapter, learn how to create a simple macro.

2 MsgBox: The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program.

3 Workbook and Worksheet Object: Learn more about the Workbook and Worksheet object in Excel VBA.

4 Range Object: The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA.

5 Variables: This chapter teaches you how to declare, initialize and display a variable in Excel VBA.

6 If Then Statement: Use the If Then statement in Excel VBA to execute code lines if a specific condition is met.

7 Loop: Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.

8 Macro Errors: This chapter teaches you how to deal with macro errors in Excel.

9 String Manipulation: In this chapter, you'll find the most important functions to manipulate strings in Excel VBA.

10 Date and Time: Learn how to work with dates and times in Excel VBA.

11 Events: Events are actions performed by users which trigger Excel VBA to execute code.

12 Array: An array is a group of variables. In Excel VBA, you can refer to a specific variable (element) of an array by using the array name and the index number.

13 Function and Sub: In Excel VBA, a function can return a value while a sub cannot.

14 Application Object: The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.

15 ActiveX Controls: Learn how to create ActiveX controls such as command buttons, text boxes, list boxes etc.

16 Userform: This chapter teaches you how to create an Excel VBA Userform.

back to top

300 Examples

 

Are you looking for Excel examples? Are you looking for clear explanations that help you master many more Excel features quickly and easily? You can find related examples and features (300 Examples) on the right side of each chapter.

Our 30 most popular examples:

1 Find Duplicates: This example teaches you how to find duplicates (or triplicates) in Excel.

2 Drop-down List: Drop-down lists in Excel are helpful if you want to be sure that users select an item from a list, instead of typing their own values.

3 Percent Change: The percent change formula is used very often in Excel. For example, to calculate the Monthly Change and Total Change.

4 Regression: This example teaches you how to perform a regression analysis in Excel and how to interpret the Summary Output.

5 Loan Amortization Schedule: This example teaches you how to create a loan amortization schedule in Excel.

6 Pareto Chart: A Pareto chart combines a column chart and a line graph. The Pareto principle states that, for many events, roughly 80% of the effects come from 20% of the causes.

7 Histogram: This example teaches you how to create a histogram in Excel.

8 Random Numbers: Excel has two useful functions when it comes to generating random numbers. The RAND and RANDBETWEEN function.

9 Remove Duplicates: This example teaches you how to remove duplicates in Excel.

10 Count Unique Values: This example shows you how to create an array formula that counts unique values.

11 Lock Cells: You can lock cells in Excel if you want to protect cells from being edited.

12 Gantt Chart: Excel does not offer Gantt as chart type, but it's easy to create a Gantt chart by customizing the stacked bar chart type.

13 Budget: This example shows you how to create a budget in Excel.

14 Line Chart: Line charts are used to display trends over time. Use a line chart if you have text labels, dates or a few numeric labels on the horizontal axis.

15 Transpose: Use the 'Paste Special Transpose' option to switch rows to columns or columns to rows in Excel. You can also use the TRANSPOSE function.

16 Correlation: We can use the CORREL function or the Analysis Toolpak add-in in Excel to find the correlation coefficient between two variables.

17 Time Sheet: This example teaches you how to create a simple timesheet calculator in Excel.

18 Offset: The OFFSET function in Excel returns a cell or range of cells that is a specified number of rows and columns from a cell or range of cells.

19 Pie Chart: Pie charts are used to display the contribution of each value (slice) to a total (pie). Pie charts always use one data series.

20 Nested If: The IF function in Excel can be nested, when you have multiple conditions to meet. The FALSE value is being replaced by another If function to make a further test.

21 Data Tables: Instead of creating different scenarios, you can create a data table to quickly try out different values for formulas. You can create a one variable data table or a two variable data table.

22 t-Test: This example teaches you how to perform a t-Test in Excel. The t-Test is used to test the null hypothesis that the means of two populations are equal.

23 Advanced Filter: This example teaches you how to apply an advanced filter in Excel to only display records that meet complex criteria.

24 Frequency Distribution: Did you know that you can use pivot tables to easily create a frequency distribution in Excel? You can also use the Analysis Toolpak to create a histogram.

25 Scatter Chart: Use a scatter chart (XY chart) to show scientific XY data. Scatter charts are often used to find out if there's a relationship between variable X and Y.

26 Anova: This example teaches you how to perform a single factor ANOVA (analysis of variance) in Excel. A single factor or one-way ANOVA is used to test the null hypothesis that the means of several populations are all equal.

27 Compare Two Lists: This example describes how to compare two lists using conditional formatting.

28 Compound Interest: What's compound interest and what's the formula for compound interest in Excel? This example gives you the answers to these questions.

29 Bar Chart: A bar chart is the horizontal version of a column chart. Use a bar chart if you have large text labels.

30 Calendar: This example describes how to create a 2016 calendar in Excel (or 2017 calendar, 2018 calendar, etc). If you are in a hurry, simply download the Excel file.

Check out all 300 examples.