I-8 Markaz, Islamabad, Pakistan +92-313-1523538
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
In this tutorial, you will learn, how to make a group box and add option button for excel form controls. Below is the sheet, i’ll be using for interest calculator.Group Box and option button excel form controls 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 show developer tab in Ribbon. For that, we’ll right click in the blank area. Group Box and option button excel form controls And select “Customize the Ribbon…” You’ll see the following window dialog. Group Box and option button excel form controls You’ll find everything ticked except “Developer” option. Tick this option and Click “OK” to add Developer features to Ribbon. Group Box and option button excel form controls Step 2: Under Developer Tab, you’ll find insert option, that’ll be holding two types of controls. Group Box and option button excel form controls Form Controls are used in excel forms, while ActiveX Controls are used with Visual Basic. I’ll use Form Controls here. HOW TO USE COMBO BOX FROM EXCEL FORM CONTROL I’ll use Group options for “Rate” and “Amount of Periods”. So, i’ll remove the row holding “Rate”. And insert two rows for Group Box.Group Box and option button excel form controls Select Group box, and Move cursor to Row 6. Press Alt and Click & Hold mouse button and drag it to row 6 and 7 under Column A & B. Group Box and option button excel form controls Change “Group box 4” Caption of group box by double tap on it and Write text to “Rate”. Step 3: Now we need to add Option button from Form Controls again. So i’ll select and insert them into Group box. Group Box and option button excel form controls After Selecting Option Button from Form Control, Move mouse Cursor into Group Box, Press Ctrl and Draw with mouse button. It is the best practice and more professional to start from left side aligned. Group Box and option button excel form controls Step 4: To change the caption of Option box, Press Ctrl and Click on option to get focus and Click on Text to edit. Group Box and option button excel form controls After changing caption of Option boxes, you need to check that clicking on one option box should diminish the other selection of option box. It means one option box should be selected at one time.   Step 5: Now, you see the option boxes are not of same size. We can resize them equally by focusing and moving there edges with Mouse. But that is not so professional way to do as there will be some difference left. So we need to resize it using Format tool. Move cursor to any option button and get focus. Right click on it and select “Format Control”. You’ll get a window Dialog in front. Group Box and option button excel form controls Note Height and Width of one Option button.   Step 6: Now, Get focus of one option button and Press Ctrl and click on all Option buttons to select. Group Box and option button excel form controls And Right click on “Format Object” option. Group Box and option button excel form controls Enter those values you found in Step 5, in Height and Width. And you’ll see all option buttons in same size. Step 7: We need to align all the option buttons. So, again i’ll select all by selecting them by pressing Ctrl and Mouse click. And then Move to “Format” tab. And select “Aling Middle” in “Align” Option. Group Box and option button excel form controls And then we’ll relocate them with equal gaps in between them by selecting “Distribute Horizontally” in “Align” Option. Group Box and option button excel form controls You’ll get this group like Group Box and option button excel form controls Step 8: Same Steps to follow for “Amount for Periods”. You’ll get it like: Group Box and option button excel form controls Share your reviews in the comment section and let me know
0