Learn how to use Excel like a pro with easy-to-follow tutorials and tips — spreadsheets, charts, pivot tables, and macros.
The Union method in Excel VBA returns a Range object that represents the union of two or more ranges. The Intersect method in Excel VBA returns a Range object that represents the intersection of two or more ranges.
Column charts are used to compare values across categories by using vertical bars. To create a column chart in Excel execute the following steps.
This example teaches you how to import data from a Microsoft Access database into Excel by using the Microsoft Query Wizard. With Microsoft Query you can select the columns of data that you want and import only that data into Excel.
This example describes how to create dependent drop-down lists in Excel. Here's what we are trying to achieve:
A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel.
A macro comment is a piece of text in a macro which will not be executed by Excel VBA. It is only there to provide you information about the macro.
This example teaches you how to perform an F-Test in Excel. The F-Test is used to test the null hypothesis that the variances of two populations are equal.
If you create a custom list in Excel you can easily fill a range with your own list of departments clients cities credit card numbers etc. This can save time and reduce errors.
To shade alternate rows in Excel use conditional formatting or apply a table style. Coloring every other row makes it easier to read your data.
Merge cells into one large cell to make clear that a label in Excel applies to multiple columns. Use CONCATENATE TEXTJOIN or Flash Fill to merge cells without losing data.
If you know the text to be replaced use the SUBSTITUTE function in Excel. If you know the position of the text to be replaced use the REPLACE function.
One of the great things about tables in Excel is that you can use a table as source data for a chart pivot table etc. To see why this is useful let's create a simple chart.
A workbook is another word for your Excel file. When you start Excel click Blank workbook to create an Excel workbook from scratch.
To view all conditional formatting rules in an Excel workbook use the Conditional Formatting Rules Manager. You can also use this screen to create edit and delete rules.
A row or column of numbers in Excel that are plotted in a chart is called a data series. You can plot one or more data series in a chart.
This page illustrates 4 ways to insert bullet points in Excel. You can use a keyboard shortcut insert a symbol create a custom number format (our favorite) or use the CHAR function.
If you have more than one option button only one of the option buttons can be selected. To create option buttons in Excel VBA execute the following steps.
Excel offers five different depreciation functions. We consider an asset with an initial cost of $10000 a salvage value (residual value) of $1000 and a useful life of 10 periods (years).
This example shows you how to create an array formula in Excel that sums a range with errors.
A spin button can be used to increment a number in a cell. To create a spin button in Excel VBA execute the following steps.
This page teaches you how to create a running total (cumulative sum) in Excel. A running total changes each time new data is added to a list.
Use the SEARCH function in Excel to find the position of a substring in a string. The SEARCH function is case-insensitive and supports wildcards.
You can pass arguments to a procedure (function or sub) by reference or by value. By default Excel VBA passes arguments by reference. As always we will use an easy example to make things more clear.
You can use Excel to sort data in a custom order. In the example below we would like to sort by Priority (High Normal Low).