POSTS PORTFOLIO SEARCH SHOP
SEARCH
SHOP
I-8 Markaz, Islamabad, Pakistan +92-313-1523538
Excel Tutorials, Tutorials

Name a cell with an excel Range name

By on

February 11, 2018

- Excel Tutorials, Tutorials

In this tutorial. We’re going to set the Excel Range name for the cells in worksheet. Let’s have a look on the worksheet i’ll be using. Name a cell with an excel Range name In this worksheet, we’ll change the price of wines from US Dollars to British Pounds, Euros and Japanese Yen. I’ll be using currency conversion rates in a table so the values will be used in this worksheet. Let’s see the exchange rate table. Name a cell with an excel Range name Step#1: Let’s calculate the currency amount in British Pounds for the “Chateau Lafite”. So, we’ll use the following way: Name a cell with an excel Range name Where C4 is the reference to price in US Dollars and “ExchangeRates!B3” is the reference to conversion rate of USD to GBP. “ExchangeRates” is the name of the worksheet containing table of currency conversion rates. We’ll get the following value in box. Name a cell with an excel Range name Step#2: Wouldn’t it be nice if instead of “ExchangeRates!B3”, it is something “=C4/USD_GBP”. So it’d be completely clear that what formula is representing. This can be done by using Range Names. Range Names point to the value in a cell that can be used in formulas, constants and tables. LINK EXCEL WORKSHEET TO WORD DOCUMENT We’re going to rewrite the formula. So i’ll delete the existing formula. Step#3: First of all, we’ll create the names of the currency conversion rates. For that we’ll select both columns in “ExchangeRates” worksheet. And we’ll move to “Formulas” in Ribbon Tab and Select “Create from Selection”. Name a cell with an excel Range name Step#4: A pop-up box will appear on the screen.Name a cell with an excel Range name Select the “Left Column” and Click OK. So, it’ll create the range names point to the values in Column B. Step#5: You can check the defined range names by clicking box in left up corner of worksheet table. Name a cell with an excel Range name HOW TO DO DATA VALIDATION IN EXCEL Step#6: Now moving to the main Worksheet to apply the formula again. Again selecting box under “British Pounds”, and applying formula: Name a cell with an excel Range name It’ll give same value but with different Formula Description that is clearly understandable. Name a cell with an excel Range name In the same way, we’ll do for the Euros and Japanese Yen. Name a cell with an excel Range name Step#7: There is another way to get a list of range names to be used in formula or constants. For that you need to select box, write formula structure and go to “Formulas” in Ribbon Tab” and select “Use in Formula”. It’ll give a list of range names, so you can select one to use. Name a cell with an excel Range name Step#8: Select the three boxes, and drag them down to get the values of all the prices in currencies. Name a cell with an excel Range name In this way, we can define and use the range names in formulas. That makes it easier for user to understand the formula. Share your reviews in the comment section.
0
Excel Tutorials, Tutorials, Word Tutorials

Link Excel worksheet to Word Document

By on

February 10, 2018

- Excel Tutorials, Tutorials, Word Tutorials

In this tutorial, you’ll learn to link Excel Worksheet to Word Document instead of embedding Worksheet in the document. Below are some steps to make it work:

Excel worksheet to Work document

Step#1:

Open new Word Document. Define your template and Write whatever the text you want before the Worksheet. Link Excel Sheet to word Document

Step#2:

Next step is to link Excel Worksheet into the Word document. For that, we’ll move to “Insert” from the ribbon tab and Select “Object” in “Text” Section. You can use shortcut key for that as “alt+n+j+j”. Link Excel Sheet to Word Document

Step#3:

Object Popup box will open. Select “Create from file” tab and Press “Browse”. You’ll see file selection box to Select file. Link Excel Sheet to Word Document After selecting file, don’t forget to check “Link to file”. It’ll create a shortcut display on your word document file. HOW TO USE COMBO BOX FROM EXCEL FORM CONTROL

Step#4:

You’ll see the Excel worksheet in Word document in the form of object. Link Excel Sheet to Word Document Here is the view of Excel worksheet linked to Word Document. Link Excel Sheet to Word Document You’ll see if the value in worksheet is changed, Word document will not detect it suddenly. To update the link in document file, right click on Worksheet Object and select “Update Link”. It’ll update all the values.

Precautions:

Whenever you link the Excel file to Word Document, you need to keep the link breakage in mind. There are following reasons that will break the link in your word and Excel file.
  • If you’ll rename the Excel file linked in the word document, you’ll break the link.
  • If you’ll rename the folder that contains the excel file linked to document, you’ll break the link.
  • If you’ll email the document file and doesn’t attach linked excel worksheet, you’ll break the link.
Please Share your review with me in the comment section to help improvements and make things better. I’d love to hear from you.
0
Excel Tutorials

How to do data validation in Excel 2016

By on

February 9, 2018

- Excel Tutorials

Data Validation helps us to make our Excel sheet error free. With data validation, we can define which data type cell can accept. We can also provide a list of values, in which we can select. Entering wrong value will prompt with an error. In this tutorial, we’ll learn how to do data validation in Excel 2016.

Data Validation:-

We have a sheet with a table of data. Excel Data Validation In the table above, These are just five entries. Suppose we have 1000 of entries like this in one table. And we are using that data for some calculations. A little mistake may give us wrong results. Suppose we have room numbers. We need it error free. We’ll add data validation on room numbers column. For that, You need a pre-occupied list of data. Verify that list is perfect and error free, all the values are right. Select the range of the cell on which you want to apply validation. And in the menu ribbon, Go to “DATA” and select “DATA VALIDATION”. You can use shortcut key for that. Select the range of cells, and Press “Alt+A+V+V”. These shortcuts are hard to remember but practice makes everything perfect. You will see a dialogue box as below in the picture. Excel Data Validation You’ll see Validation criteria dropdown list. *These are all those options you can apply for validation.” Select “List” among those options. And select the source data. Source data is the list of pre-occupied data that is verified as error free. Select the source range on the sheet. In the upper portion of dialogue box, You will see two more tabs. Input Message Excel Data Validation You can enter information about the cell in this tab. When cell is selected, information pops out for user ease. Error Alert Excel Data Validation It facilitates with restriction about the wrong value. It will prompt if user will enter any wrong value and restricting to update the data in the cell. After finishing, You will see that the cells in column of Room number is showing a dropdown arrow. Clicking on that one, you’ll see verified list values. You have successfully applied data validation on Room number Column.
0