Index function can be used to get a reference of cell from an Array for required or matching value. It’s basic use require three parameters. We’re going to describe the function with example. Step#1: We’re going to use the following list in excel to use the index function: Index function’s structure is: = INDEX(array, rownum,[columnnum]) […]
Category: Excel 2013
Define and use names in formulas – Excel
In this tutorial. We’re going to set the Range names for the cells in worksheet. Let’s have a look on the worksheet we’ll be using. In this worksheet, we’ll change the price of wines from US Dollars to British Pounds, Euros and Japanese Yen. We’ll be using currency conversion rates in a table so the […]
Excel Data validation Errors for Users
In this tutorials, we’ll learn how to use Data validation function to restrict or warn user about the data. For users under training about the work on worksheet, can be provided with Data validation Error messages. So whenever they do something that’s not allowed, get explained which Worksheet rule they’ve broken. Let’s start with the […]
Excel Data Validation Messages for Users
To make your worksheet more efficient and user friendly, it’s better to inform the user of the worksheet rules before they intent to enter the value. In this tutorial, we are going to use data entry input messages to provide the better user experience. Following is the worksheet we are going to use Data validation […]
Use of Excel Left, Right and mid function to extract text
In this lesson, we’re going to extract data from a string using Text Left Function, Text Right Function and Text Mid Function. Step#1: We’ll be using ISBN number from a table of books record to extract information into separate columns. Anatomy of ISBN Number is as follows. Digits 1-3 EAN (European Article Number) Digit 4 […]
Link Excel Worksheet into Word Document
In this lesson, we’ll link Excel Worksheet inside the Word Document instead of embedding Worksheet in the document. Linking worksheet will create shortcut as an Object instead of as a table. Step#1 Open new Word Document. Write any text before the Worksheet object. Step#2 Next step is to link Excel Worksheet into the Word document. For […]
Import web data into excel
There is so many data on the internet that is continuously changing. Common example is: Currency Exchange Rates and Stock Prices. This type of information is held in the Web Page Construct called Table. Excel allow us to create a web query that goes to page, get the table data and populate the cells in […]
Combo Box from Excel Form Control
In this tutorial, we’ll show you how to use Combo Box form control in your excel sheet. We’ll be using Interest Calculator for this purpose, as shown below: Step #1: We’ll replace “Rate group box” with Combo Box. So, we’ll select Group box and press ‘Del’ button. Then, press ctrl and click on Radio Buttons […]
Group Box and option button excel form controls
In this tutorial, we’ll be teaching, how to make a group box and add option button for excel form controls. Below is the sheet, we are using for interest calculator. Step 1: We will use form controls. Form controls are advanced level features in excel that are hidden from normal users. So, we need to […]
Publish an Excel workbook as a single Web Page
Excel has an excellent Web building tool. You can convert any Worksheet to an Html file to open it your browser. Below are the steps to get an Html file of Excel sheet. Step 1: Open any excel sheet. We do this with Excel online Template. It’ll load a complete template to use in your […]