Library

Vlookup in Excel VBA

Use the WorksheetFunction property in Excel VBA to access the VLOOKUP function. All you need is a single code line.

218

Tasks on Schedule in Excel VBA

Below we will look at a program in Excel VBA that sets the background color of tasks that are on schedule to green and sets the background color of tasks that are behind schedule to red.

219

Floating Point Errors in Excel

Excel stores and calculates floating point numbers. Sometimes the result of a formula is a very close approximation.

220

Userform in Excel VBA

This chapter teaches you how to create an Excel VBA Userform. The Userform we are going to create looks as follows:

221

Union and Intersect Methods in Excel VBA

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.

222

Clustered Column Chart in Excel

Column charts are used to compare values across categories by using vertical bars. To create a column chart in Excel execute the following steps.

223

Microsoft Query in Excel

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.

224

Dependent Drop-down Lists in Excel

This example describes how to create dependent drop-down lists in Excel. Here's what we are trying to achieve:

225

Formulas and Functions in Excel

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

226

Excel Macro Comments with VBA

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.

227

F-Test in Excel

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.

228

Custom Lists in Excel

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.

229

Shade Alternate Rows in Excel

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.

230

How to Merge Cells in Excel

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.

231

SUBSTITUTE vs REPLACE

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.

232

Table as Source Data - Excel Tutorial

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.

233

Workbook in Excel

A workbook is another word for your Excel file. When you start Excel click Blank workbook to create an Excel workbook from scratch.

234

Manage Conditional Formatting Rules in Excel

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.

235

Chart's Data Series in Excel

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.

236

Insert Bullet Points in Excel

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.

237

Option Buttons in Excel VBA

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.

238

Depreciation Formulas in Excel

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

239

Sum a Range with Errors in Excel

This example shows you how to create an array formula in Excel that sums a range with errors.

240

Spin Button in Excel VBA

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.