You are reading the article How To Create Excel Template? updated in December 2023 on the website Moimoishop.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 How To Create Excel Template?
Create Templates in ExcelWe have different types and categories of templates available in Excel, which can access from the File menu ribbon’s New section. This has different types of Templates such as Business, Calendar, Budget, Planner, Financial Management, etc. To create customized templates other than these, we can use Data Validation for drop-down, Table, and Images and give them proper header names. We can also insert a logo for our template. To standardize the template, always fix the theme or template, and visuals should see the purpose of creation. In this article, we will learn about Create Templates in Excel.
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
How to create Templates?
Templates can be made by saving an Excel file with a specialized extension and then saving the file in a specified directory.
What type of content can be stored as a Template?
Text data can be stored as a template. Various document sections, such as page titles, column and row labels, text, section headings, and any cell in Excel containing text or numbers, or any kind of data, can all be included in a template. We can also include any graphical shapes, logos of companies, or any background image and even Excel formulae.
The type of text formatting, such as font, color, or size, can be saved along with the data as a template. Formats of cells or worksheets, such as column width or background fill color or alignment of text and even formats of numbers and dates, and several sheets can be saved in templates.
All hidden and protected areas, such as locked cells that cannot be altered and hidden columns and rows, or even worksheets that may contain data not meant for general view.
All Macros are specially customized toolbars that may contain frequently used options; macros and the quick access toolbar can be saved as templates.
How to Create Templates in Excel?To create a template in recent versions of Excel, very little work must be done.
Excel 2013 & later versions – Before saving a file as a template, one has to define the custom template directory.
Go to File.
Select the option Save in the menu ribbon.
Find the option Default personal templates location among the various options.
Choose a directory where you want to save all the templates. DocumentsCustom Office Templates is regarded as a good choice.
Firstly, go to the File.
Now, the option to provide a name to your template file appears.
Here we see that in the drop-down menu, there is an option called Excel Template (*.xltx)
Note: It is better to choose “Excel Macro-Enabled Template” (.xltm) for cases where the workbook might contain macros. “Excel 97-2003 Template” (.xlt) is to be chosen for the cases where the version of the Excel workbook is very old. “Excel Template” (.xltx) should be chosen for all other cases.
Examples to Create Templates in ExcelBelow are some examples to create templates in Excel.
Example #1First, we will make all the changes in a new file and modify it until all the items you wish to save in the template are ready. Then you have to save the file as a template. Template files have a special extension.
In the screenshot above, we have added an image and text as the template’s structure. Now we shall follow the steps below to create the Excel template.
Step 1 – Firstly, go to File.
Now, the option to provide a name to your template file appears.
Step 3 – Here, we see that in the drop-down menu, select Excel Template (*.xltx)
Now, automatically, Excel will place this template file in the appropriate directory. And new Excel documents can be created based on this template file by navigating and choosing “Personal” in the new file window (right next to Featured) and then choosing the appropriate template.
Concepts always become much clearer when we have more examples. So, let us look at yet another example of creating Excel Templates.
Example #2Let us explore how to save a Macro-Enabled Excel template through an example. Suppose we have an Excel with some macros(s) to be used as a base for other files; then we need to save this Excel as a macro-enabled Excel template.
In the screenshots above, we added a macro in the template file, and now we shall create the template in Excel.
Now, the option to provide a name to your template file appears.
Here we see that in the drop-down menu, there is an option called Excel Macro-Enabled Template (*.xltm)
Now, automatically, Excel will place this template file in the appropriate directory. And new Excel documents can be created based on this template file by navigating.
Firstly, go to File.
Choose Personal in the new file window (next to Featured) and then choose Template 2.
Example #3Now, let us look at another example. Firstly, we will make all the changes in the new file and modify it until all the items you wish to save in the template are ready. Then you have to save the file as a template. Template files have a special extension.
We have added an image and text as the template’s structure in the above screenshot. Now we shall follow the steps below to create the Excel template.
Now, the option to provide a name to your template file appears.
Step 2 – Here, we see that in the drop-down menu, select Excel Template (*.xltx)
Example #4Now, let us look at yet another example. Now, we will make all the changes in a new file and modify it until all the items you wish to save in the template are ready. Then you have to save the file as a template. Template files have a special extension.
In the screenshot above, as you can see, we have added the template structure – we have added a world map, increased the default worksheets, and renamed them, and now we shall proceed to save this file as a template.
Now we shall follow the steps below to create the Excel template.
Now, the option to provide a name to your template file appears.
Step 2 – Here, from the drop-down menu, select Excel 97-2003 Template (*.xlt)
Example #5Now we shall use a template file to create a new file in Excel. We will make use of the Example #4 template file.
Firstly, go to File.
Choose “Personal” in the new file window (next to Featured) and choose the appropriate template.
We shall choose Template 4 as the base and create a new file in Excel.
As we can see, all the template structures are retained, and the new file is named Template4 1 – the first file based on Template 4.
Example #6Now we shall use another template file to create a new file in Excel. We will make use of the Example #3 template file.
We shall choose Template 3 as the base and create a new file in Excel.
Hence, we can see that the image and the header structure are retained in the new file. And it is also important to note that this new file is named Template 3 1 – signifying that it is the first file based on Template 3.
Example #7Now we shall create another new template. We will create the template structure in Excel, as shown below.
Now we shall follow the steps below to create the Excel template.
Now, the option to provide a name to your template file appears.
Step 2 – Here, from the drop-down menu, select Excel Template (*.xltx)
This will create a Template 7 template with the template structure defined in the Default Template location in Excel.
Example #8Now we shall attempt to use Template 7 to create another file in Excel.
Now, automatically, Excel will place this file in the appropriate directory.
Example #9Let us see an example where we have Excel formulae in the Template file.
As we can see above, we have created a Template structure with the formula for Net Profit Margin defined as:
Net Profit Margin = (Net Profit/Total Revenue)*100
Since this is the template, no data is present here. Let us see how to create the template file in Excel.
Now we shall follow the steps below to create the Excel template.
Now, the option to provide a name to your template file appears.
Step 2 – Here, from the drop-down menu, select Excel Template (*.xltx)
This will create a Template 9. xltx template with the template structure defined in the Default Template location.
Example #10Now, we shall attempt to use the previous example template to create a new file and see if that works in Excel.
We see that in the new file, we have the structure defined, and once we feed in the data on Columns A, B, and C, the Net Profit Margin in Column D is automatically calculated using the Template File formula.
Example #11Let us now use our second example – Template 2 to create a new file in Excel. Template2 has a defined macro, so let us see if the same is available in the new file.
Now let us see what happens when we select “Template2”.
It opens up a new file with the same macro (that was defined in the template file) loaded automatically.
We will get the desired result.
Things to Remember
For versions of Excel 2013 and later, it is possible to change Excel’s default template for a workbook by saving the template at the appropriate location. All default templates must have a specific name – chúng tôi or chúng tôi and must be saved in Excel’s startup directory. C:Users%username%AppDataRoamingMicrosoftExcelXLSTART
The template has to be named xltx or Sheet to modify the template to add new sheets in existing files. xltm and must be saved in the same folder.
Recommended ArticlesThis is a guide to Create Templates in Excel. Here we discuss how to Create Templates in Excel, practical examples, and the type of content that can be stored as a Template. You can also go through our other suggested articles –
You're reading How To Create Excel Template?
How To Calculate Age In Excel Using Formulas + Free Calculator Template
Watch Video – How to Calculate Age in Excel (in Years, Months, and Days)
Using a combination of Excel functions and the date of birth, you can easily calculate age in Excel. You can either calculate the age till the current date or between the specified period of time.
The technique shown here can also be used in other situations such as calculating the duration of a project or the tenure of the service.
In this tutorial, you’ll learn how to calculate age in Excel in:
The number of years elapsed till the specified date.
The number of Years, Months, and Days elapsed till the specified date.
You can also download the Excel Age Calculator Template.
Suppose you have the date of birth in cell B1, and you want to calculate how many years have elapsed since that date, here is the formula that’ll give you the result:
=DATEDIF(B1,TODAY(),"Y")
If you have the current date (or the end date) in a cell, you can use the reference instead of the TODAY function. For example, if you have the current date in cell B2, you can use the formula:
=DATEDIF(B1,B2,"Y")
DATEDIF function is provided for the compatibility with Lotus 1-2-3.
One of the things that you’ll notice when you use this function is that there is no IntelliSense available for this function. No tooltip appears when you use this function.
This means that while you can use this function in Excel, you need to know the syntax and how many arguments this function takes.
If you’re interested in knowing more about DATEDIF function, read the content of the box below. If not, you can skip this and move to the next section.
Syntax of DATEDIF function:
=DATEDIF(start_date, end_date, unit)It takes 3 arguments:
start_date: It’s a date that represents the starting date value of the period. It can be entered as text strings in double-quotes, as serial numbers, or as a result of some other function, such as DATE().
end_date: It’s a date that represents the end date value of the period. It can be entered as text strings in double-quotes, as serial numbers, or as a result of some other function, such as DATE().
unit: This would determine what type of result you get from this function. There are six different output that you can get from the DATEDIF function, based on what unit you use. Here are the units that you can use:
“Y” – returns the number of completed years in the specified time period.
“M” – returns the number of completed months in the specified time period.
“D” – returns the number of completed days in the specified period.
“MD” – returns the number of days in the period, but doesn’t count the ones in the Years and Months that have been completed.
“YM” – returns the number of months in the period, but doesn’t count the ones in the years that have been completed.
“YD” – returns the number of days in the period, but doesn’t count the ones in the years that have been completed.
You can also use the YEARFRAC function to calculate the age in Excel (in years) in the specified date range.
Here is the formula:
=INT(YEARFRAC(B1,TODAY()))
The YEARFRAC function returns the number of years between the two specified dates and then the INT function returns only the integer part of the value.
NOTE: It’s a good practice to use the DATE function to get the date value. It avoids any erroneous results that may occur when entering the date as text or any other format (which is not an acceptable date format).
Also read: How To Calculate Time In ExcelSuppose you have the date of birth in cell A1, here are the formulas:
To get the year value:
=DATEDIF(B1,TODAY(),"Y")
To get the month value:
=DATEDIF(B1,TODAY(),"YM")
To get the day value:
=DATEDIF(B1,TODAY(),"MD")
Now that you know how to calculate the years, months and days, you can combine these three to get a text that says 26 Years, 2 Months, and 13 Days. Here is the formula that will get this done:
=DATEDIF(B1,TODAY(),"Y")&" Years "&DATEDIF(B1,TODAY(),"YM")&" Months "&DATEDIF(B1,TODAY(),"MD")&" Days"
Note that the TODAY function is volatile and its value would change every day whenever you open the workbook or there is a change in it. If you want to keep the result as is, convert the formula result to a static value.
Excel Functions Used:
Here is a list of functions used in this tutorial:
DATEDIF() – This function calculates the number of days, months, and years between two specified dates.
TODAY() – It gives the current date value.
YEARFRAC() – It takes the start date and the end date and gives you the number of years that have passed between the two dates. For example, if someone’s date of birth is 01-01-1990, and the current date is 15-06-2023, the formula would return 26.455. Here the integer part represents the number of years completed, and the decimal part represents additional days that have passed after 26 years.
DATE() – It returns the date value when you specify the Year, Month, and Day value arguments.
INT() – This returns the integer part of a value.
You May Also Like the Following Excel Tutorials:
Example Of Risk/Reward Ratio (With Excel Template)
Definition of Risk/Reward Ratio
The Risk/Reward Ratio is measured by the trader/investor for the level of risk taken on investment against the level of income and growth achieved on investment. The ratio measures probability and level of profit against probability and level of loss taken by the investor.
Start Your Free Investment Banking Course
Every trader/investor, according to his /her risk appetite, generally decides the Risk/Reward ratio. In general high-risk results in high rewards, but there is an investment option where this statement is not true. This ratio helps the investor to make the decision of investment from investment options depending on the level of returns against the level of risk involved in case investment does not move in the expected direction.
Formula
Risk to Reward Ratio = Risk / Reward
In general, the lower ratio is better than the investment.
Example of Risk/Reward Ratio (With Excel Template)Let’s take an example to understand the calculation in a better manner.
You can download this Risk/Reward Ratio Excel Template here – Risk/Reward Ratio Excel Template
Example #1A decision to invest $400,000. He is willing to take 10-15% of the risk on a short-term investment. He shortlisted two stocks one of which he prefers to invest. They are either Microsoft Corporation at the current price of $172 per share or Apple Inc. at the current price of $320 per share. Mr. A study and analyzed both stock trends and realized that Microsoft Corp. share price can go up to $225 per share and Apple Inc. share price can go up to $400 per share in a period of 3 months. Since it is a stock market investment it involves the risk of share price goes down instead of up. He is ready to take risks up to $48000.
Solution:
Risk is calculated as
Risk/Reward Ratio is calculated using the formula given below
Risk to Reward Ratio = Risk / Reward
For Apple Inc.
Risk/Reward Ratio =$35 / $ 80
Risk/Reward Ratio = 0.44
Risk/Reward Ratio = $19 / $53
Risk/Reward Ratio = 0.36
Above, calculation, suggests Microsoft is the better investment as per the Risk/Reward ratio. However, it is up to Mr. A to decide which investment he prefers or he may choose to invest in both companies by dividing his investment.
Difference between Risk and RewardBasis of Comparison
Risk
Reward
Definition Risk is the probability and level of loss of investment taken by the investor. The reward is returns or growth earned on investment by the investor during period
Source Risk is a result of the category of asset, investment and trading strategy, Economic conditions affecting investment. The reward is a result of Interest, dividend, increase in the underlying value of the investment.
Types
Systematic Risk: Risk, which cannot be avoided, affect almost all market investments. E.g. interest rate, exchange rate, inflation, political.
Unsystematic Risk: Specific type of risk affecting a particular investment or industry. E.g., management change, competition, performance.
Growth: The price of asset increases resulting in the growth of the underlying value of the investment.
Income: Reward earned through interest, dividend on investment.
Managing There are 4 way to handle risk
Avoid: After considering the level of risk avoid investment.
Reduce: Change in trading/investment strategy, hedging, Diversifying investment, cutting off loss-making investment.
Transfer: Transfer of risk can be done through insurance by paying the premium.
Accept: Understanding and accepting risk for better rewards.
Rewards are managed by
Reinvestment: Income earned by selling an investment or through dividend and interest can be reinvested in different asset categories.
Expand: in business, expansion is a way to invest in the business for expansion and more profit.
Diversification: Portfolio upgraded and managed by diversifying investment in various categories.
Example Mr. Rock decides to invest in stock A at the current price of $100 he expects the price of the stock can rise up to $120 in 1 month while he decides he should not make the loss of more than $10 in this stock. $10 is a risk taken by Mr. Rock in this investment in case stock moves down. Mr. Rock decides to invest in stock A at the current price of $100 he expects the price of a stock can rise up to $120 in 1 month while he decides he should not make the loss of more than $10 in this stock. Stock price moves up to $120 in a month this earning of $20 on $100 investment is the reward.
Below we will learn about the benefits and limitation for the same:
Advantages
Risk appetite: Every individual has a different risk level of risk capacity. Risk/Reward ratio helps them to make selection and decision from various investment option according to capacity and expected returns.
Investment Decision: Risk/reward ratio help investor to make investment decision from various investment options like mutual funds, stocks, hedge funds, etc.
Risk-returns estimates: Even if investment provides returns it is important to calculate whether returns earned on investment are worth in comparison with the risk taken. If returns are not as expected compare to risk, an investor can decide whether an investment is worth or not. For e.g. Investor can decide to make an investment in bonds, debentures, fixed deposits that have less risk but will also generate less return on investment, or other options likes the stock, mutual funds which can generate high returns but includes the risk of loss. Investment decision depends on an investor’s expectation and risk capacity.
Risk Management: Risk can be managed by four ways i.e. avoid, reduce, transfer and accept. With the help of risk to reward ratio investors can manage their portfolio to maximize returns and minimize risk level through various options. Trader trading in various financial instruments can limit his loss with stop-loss by using risk to reward ratio.
Not completely accurate: Risk/Reward ratio is not always accurate; the investor has to make the decision based on risk capacity and on certain assumptions on price movement. Technical and fundamental analysis help in making better analysis of stock understand risk/reward ratio but they are not completely accurate, and still include assumptions.
Not Certainty of movement: Risk to reward ratio based on an assumption of certain movement but in reality in the market financial instrument does not necessarily move in expected or opposite direction. Many time if the stock remains stagnant, which will turn the investment into a dead investment without either profit or loss.
Conclusions – Risk/Reward RatioRisk/Reward ratio is an important tool for trader/investor to understand the level of risk involved in investment decisions compared to returns. Lower the risk/reward ratio i.e. below 1 is considered as good ratio since the return on investment outweighs the risk. In general, short-term investors and traders use this ratio to select from a variety of categories of investments. In case the price does not move in the expected direction this ratio, helps them to limit their losses.
Recommended Articles
How To Match Data In Excel
How to Match Data in Excel (Table of Contents)
Start Your Free Excel Course
Excel functions, formula, charts, formatting creating excel dashboard & others
Introduction to Match Data in ExcelMicrosoft Excel offers various options to match and compare data, but we usually compare only one column in most scenarios. Comparing the data for one or more or multiple columns, various options are available based on the data structure. It’s the most frequent task use in comparative data analysis on tabular row or column data.
Definition of Match Data in Excel
It’s a process to find out or spot a difference between datasets of two or more columns or rows in a table. It can be done by various procedures, depending on the dataset structure type.
Examples of How to Match Data in ExcelLet’s check out the various available option to compare data sets between two rows or columns in Excel & to spot a difference between them.
You can download this How to Match Data Excel Template here – How to Match Data Excel Template
Example #1How to Compare or Match Data in the Same Row
In the below-mentioned example, I have two columns, i.e., List 1 & 2, which contains the list of student names; now, I have to compare & match a dataset in these two columns row by row.
To check whether the name in List 1 is similar to list 2. There are various options available to carry out.
Method 1 – I can apply the below-mentioned formula in a separate column to check out the row data one by one, i.e., =A3=B3; it is applied to all the other cell ranges.
If there is a data match, it returns a value “True”; otherwise, it will return a “False” value.
Method 2 – To Compare data by using IF logical formula or test
If logical formula gives a better descriptive output, it compares case-sensitive data.
i.e., =IF (A3=B3, “MATCH”, “MISMATCH”)
Suppose the logical test is case-sensitive. It will help out whether the cells within a row contain the same content. I.e., Suppose the name is “John” in one row & “John” in the other row; it will consider it as different & result in Mismatch or a false value.
Here is the result mention below.
Method 3 – To Compare data with the help of Conditional Formatting
Here, suppose I want to highlight the matching data between two rows with some color, say, Green, otherwise no color for data mismatch; then, to perform this, Conditional Formatting is used with a set of certain criterion
To perform conditional formatting, I must select the entire tabular data set.
Go to the Home tab, under the style option, and select a Conditional Formatting option.
Various options under the conditional formatting appear in that select New Rule.
Now, it will highlight all the matching data with green color and no color for the data, which is a mismatch between rows.
You can use the above three methods to compare and match numeric data, date, or time values. The same process can use to compare multiple column data matches in the same row.
Example #2How to Compare or Match Data between Columns & Highlighting the Difference in the Data
In some cases, the dataset matches may be in different rows (It is not present in the same row); in these scenarios, we have to compare two columns & match the data.
In the below-mentioned example, student list 1 in column B is slightly bigger than student list 2 in column C. and. Also, a few names are there in both student lists, but it is not present in the same row (such as John, Mark, and Edward).
Data match or comparison between two columns can be made through a duplicate option under conditional formatting.
To perform conditional formatting, I must select the entire tabular data set.
In the Home tab, select a Conditional Formatting option under the style option.
Various options under the conditional formatting appear; under the Highlight Cells Rules, you have to select the Duplicate Values.
Once the duplicate values are selected, a popup message appears, where you need to select the color of your choice to highlight the duplicate values; here, I have selected Light Red Fill with Dark Red Text.
After selecting, you can observe that common students present in both columns are highlighted with red color (along with dark red text), while the unique values are not colored.
Note: In Conditional Formatting duplicate values option is not case-sensitive; where it considers the upper or lower case as similar records, it will not highlight them as duplicates.
Example #3Highlighting the Row Difference with the help of the “Go to Special” Feature
Compared to other methods, with the help of this option, we can perform the task faster. It can also use for multiple columns.
It will highlight the cells with different datasets; now, you can color it green to track a difference in the dataset between rows.
Things to Remember about the Data Match in ExcelApart from the above methods, various third-party add-on tools perform the textual data match in Excel.
Fuzzy Lookup Add-In tool for Excel
It is most commonly used to match and compare a customer’s name & address data. It will help track the difference in the data table within a selected row. It will also help find various errors, including abbreviations, synonyms, spelling mistakes, and missing or added data.
Recommended ArticlesThis is a guide on How to Match Data in Excel. Here we discuss How to Match Data in Excel using different methods, Different Examples, and a downloadable Excel template. You may also look at the following articles to learn more –
How To Use Excel Vba Round Function?
Excel VBA Round Function
Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.
If the number you are trying to round have the last digit after decimal <= 0.4, it rounds it down (Round Down). Suppose the decimal part is exactly 0.5 then what does it do? In such cases, it checks with the integer part of the number. If the integer part is even then it rounds down to the even number of the decimal part. If integer part is odd, then it rounds it up to the even number. This method of rounding is also called as “Banker’s rounding”.
Syntax of Round Function in Excel VBAVBA Round function has the following syntax:
Where,
Expression – The floating number you wanted to round.
Decimal_places – It’s an optional argument which takes an integer value which specifies the decimal places up-to which the number is supposed to be round. Should always be greater than or equals to zero. If not specified, by default zero is considered. Which means a number is rounded to an integer.
How to Use Excel VBA Round Function?We will learn how to use a VBA Round function with few examples in Excel.
You can download this VBA Round Excel Template here – VBA Round Excel Template
Example #1 – VBA Round Function to Round a NumberFollow the below steps to use Round function in Excel VBA.
Step 1: Insert a new module under Visual Basic Editor (VBE).
Code:
Sub
Round_Ex1()End Sub
Step 3: Use MsgBox function to be able to pop up a message like this “The Value Rounded is:”
Code:
Sub
Round_Ex1() MsgBox "The Value Rounded is: "End Sub
Step 4: Now, add “& Round (10.9834, 2)” in front of MsgBox command, so that the rounded value will be shown up in the message box along.
Code:
Sub
Round_Ex1() MsgBox "The Value Rounded is: " & Round(10.9834, 2)End Sub
Step 5: Hit F5 or the Run button placed at the uppermost panel to run this code. You will see an output as shown in the below.
Step 1: Define a new sub-procedure in VBE to store a macro.
Code:
Sub
Round_Ex2()End Sub
Step 2: Define a variable named roundNumber as Double which can hold the value of the number to be rounded.
Code:
Sub
Round_Ex2()Dim
roundNumberAs Double
End Sub
Step 3: Assign a number value which is to be rounded to this variable using assignment operator.
Code:
Sub
Round_Ex2()Dim
roundNumberAs Double
roundNumber = 23.98End Sub
Step 4: Use a combination of MsgBox along with Round function to round this number up-to one decimal point.
Code:
Sub
Round_Ex2()Dim
roundNumberAs Double
roundNumber = 23.98 MsgBox "The number rounded is: " & Round(roundNumber, 1)End Sub
Step 5: Let’s hit F5 or the Run button to run this code and see the output.
You can see an output as shown in the screenshot above. Please note that with the logic of “round to even”, the last decimal is rounded to 10 and the next decimal becomes 10 itself, due to which the number is rounded to the closest even part of the integer (i.e. 24).
Example #3 – Round Cell Value Using VBA RoundSuppose the value you wanted to round is stored in one cell of your excel worksheet.
All you want is to round this value up-to two decimal places.
Step 1: Define a new sub-procedure in a Module to store the macro.
Sub
Round_Ex3()End Sub
Step 2: Write command as “Range (“A2”).Value =”. This command works as a location where the output will be stored.
Code:
Sub
Round_Ex3() Range("A2").Value =End Sub
Step 3: Now, use a round function to round the value present in cell A1 and store the result in cell A2. Write the following piece of code: Round (Range (“A1”).Value, 2).
Code:
Sub
Round_Ex3() Range("A2").Value = Round(Range("A1").Value, 2)End Sub
Step 4: Hit F5 or Run button to run this code and see the output in cell A2 of your worksheet.
Example #4 – Round a Number Using VBA RoundUp FunctionSuppose, you want to round a number up using VBA. You can do that by using WorksheetFunction.RoundUp function.
Step 1: Define a new sub-procedure in Visual Basic Editor that can store your macro.
Code:
Sub
Round_Ex4()End Sub
Step 2: Define two variables, one to hold the number you wanted to round up. And the other to store the roundup result.
Code:
Sub
Round_Ex4()Dim
numToRoundAs Double
Dim
numRoundUpAs Double
End Sub
Step 3: Store a value into variable numToRound which you wanted to round up.
Code:
Sub
Round_Ex4()Dim
numToRoundAs Double
Dim
numRoundUpAs Double
numToRound = 12.7543712End Sub
Step 4: Now, use RoundUp to roundup this number and store the result in a numRoundUp variable.
Code:
Sub
Round_Ex4()Dim
numToRoundAs Double
Dim
numRoundUpAs Double
numToRound = 12.7543712 numRoundUp = Application.WorksheetFunction.RoundUp(numToRound, 4)End Sub
Step 5: Use MsgBox to show the output under the message box.
Code:
Sub
Round_Ex4()Dim
numToRoundAs Double
Dim
numRoundUpAs Double
numToRound = 12.7543712 numRoundUp = Application.WorksheetFunction.RoundUp(numToRound, 4) MsgBox "The number rounded up is: " & numRoundUpEnd Sub
Step 6: Hit F5 or Run button to run this code and see the output.
Example #5 – Round down a Number Using VBA RoundDown FunctionStep 1: In a new sub-procedure, define two new variables with the name numToRoundDown and roundDownNum. One to hold the value of the number to be rounded down and others to store the output after the number is rounded down.
Code:
Sub
Round_Ex5()Dim
numToRoundDownAs Double
Dim
roundDownNumAs Double
End Sub
Step 2: Assign value you want to be rounded down to the variable named “numToRoundDown”.
Code:
Sub
Round_Ex5()Dim
numToRoundDownAs Double
Dim
roundDownNumAs Double
numToRoundDown = 7.075711End Sub
Step 3: Now, use RoundDown to roundup this number and store the result in a roundDownNum variable.
Code:
Sub
Round_Ex5()Dim
numToRoundDownAs Double
Dim
roundDownNumAs Double
numToRoundDown = 7.075711 roundDownNum = Application.WorksheetFunction.RoundDown(numToRoundDown, 4)End Sub
Step 4: Use MsgBox function to show up the value of a number rounded down.
Code:
Sub
Round_Ex5()Dim
numToRoundDownAs Double
Dim
roundDownNumAs Double
numToRoundDown = 7.075711 roundDownNum = Application.WorksheetFunction.RoundDown(numToRoundDown, 4) MsgBox "The number rounded down is: " & roundDownNumEnd Sub
Step 5: Hit F5 or Run button to run this code and see the output as a message box.
This is it in the article. We have captured the concepts of VBA Round, Round Up and Round Down with a handful of examples.
Things to Remember
This function uses a Banker’s rounding method to round the numbers which are somewhat different than the actual rounding method.
This function is used to round a number with a floating point or a number with fixed-decimals up-to specified number of places.
It means the function will round up or down the number depending upon the number after decimal points.
Argument decimal_places must be greater than or equal to zero.
If decimal_places is left blank, it will be considered as zero and the number will then be rounded to the nearest integer.
If decimal_places is set less than zero, then run time error 5 occurs. “Run-time error ‘5’: Invalid procedure call or argument”.
It is not really predictable to what this function will round the value when the digit after the decimal is 5.
If one or both the argument of Round function is non-numeric, then the function will return run time 13 error. “Run-time error ’13’: Type mismatch”.
Recommended ArticlesThis has been a guide to VBA Round Function. Here we have discussed how to use Excel VBA Round Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –
How To Create And Configurewith Workflow
Introduction to Gulp.js
Gulp is used to run different tasks with the help of chúng tôi as a platform. Gulp depends on the JavaScript code, provides the platform to run the front-end tasks, and helps us run large-scale web applications. Commonly gulp is used to build the automated system to perform different tasks such as CSS and HTML. It also provides the provision for concatenating additional library files and compiling SASS files. In this operation, we need to act on the command line using Shell or Bash scripts.
Start Your Free Software Development Course
Web development, programming languages, Software testing & others
What is Gulp.js?Gulp is an order line task sprinter for chúng tôi Gulp lets us computerize cycles and run redundant errands efficiently. Gulp is not the same as other assignment sprinters because it utilizes Node streams, channeling yield starting with one undertaking as information and then onto the next. It has to peruse a record once, process it through numerous errands, and compose the result document. These outcomes result in quicker constructs since there is a compelling reason to make and peruse middle-person documents on the hard drive.
Utilizes SASS and LESS as CSS preprocessors.
Consequently, it invigorates the page in the wake of altering the source records.
Straightforward and fabricate the chúng tôi because it utilizes unadulterated JavaScript code to construct the assignment.
Using Gulp.jsUsing gulp helps us automate the two different tasks; here, we try to remove the white space from the HTML file and modify the JavaScript files. Here we need to use gulp to copy the resultant files into the built folder.
Let’s consider our application placed under the folder C:GulpDemo. There is no need to use the global installation of gulp; instead of global, we can install a local version.
For execution, we need to reach the application and open the command line prompt; after that, configure our application using npm package manager because we install gulp using npm, so use the npm init command and hit enter to create a JSON file. Here we get some question lists relevant to the application, and finally, we created a package, a JSON file, under the root folder. This newly created file stores all the dependencies.
The created JSON file we can see below is as follows.
Code:
{ "stud_name":"Jenny", "Class":"First", "RollNo":"10", "scripts":{ "exam":"echo"Error Message: no exam specified " && exit 2" }, }Explanation:
The chúng tôi for npm is like a package.config utilized by Nuget to store the application conditions.
By having a neighborhood establishment of Gulp, when the application is most recent from adaptation control, it will design the application with every one of its conditions locally instead of utilizing a worldwide establishment.
We need to add an HTML task; gulp provides the plugin to remove the white space from the HTML file, so use the following command.
Code:
npm install gulp-htmlclean --save-devExplanation:
Inside the root folder, we will get an unlearned HTML file; now, we need to create a gulp runner to clean the HTML file and transfer it into the destination folder under the root folder.
After executing the above command, we get the following screen.
Output:
We usually know that gulp writes all tasks in a JavaScript file called chúng tôi so first, create that file and write the following code.
Code:
var gulpfile = require('gulp'), htmlcleanfile = require('gulp-htmlclean'); var f = { src: 'src/', build: 'build/' }; gulp.task('html', function() { var o = folders.build + 'html/'; return gulp.src(folder.src + 'html/**/*') .pipe(htmlclean()) .pipe(gulp.dest(o)); });
In the above code, we use different methods, such as gulp.task, chúng tôi gulp.dest, and gulp.watch.
Gulp.task: It is used to create a newjobk per our requirement.
Gulp.src: It is used to read files from a specified source folder.
Gulp.dest: it is used to write the file after the operation.
Another strategy called readable.pipe() really chains the above swallow techniques to play out an assignment. Swallow works with Streams, for example, the gulp.src() technique peruses records as stream from the source and passes it to a swallow module through the pipe() strategy for doing an assignment. This way, the line() technique peruses the stream from the gulp.src(), executes the module assignment and passes it to the objective stream through gulp.dest() to compose it to the objective.
Creating Server Gulp.jsNow let’s see if we can create server chúng tôi as follows.
Server creation helps us reload live tasks on the browser; after that, we can integrate all required tasks. So first, we need to install the Browsersync module on the machine using the following command.
Code:
$npm install –save-dev browser-sync After that, we need to write a function as below. gulp.task('webserver', function(){ connect.server({ livereload:true}); });In the second step, we can set up an automatic compilation of fewer files to the CSS style sheet and move them into the browser. We may need to change the hostname and port in the next step. Gulp provides the connect plugin, and it has many configuration options. As per our requirement, we can modify the hostname and server port.
Code:
connect.server({ port:80, host: ‘gulp.dev’ }) ; Configure and Workflow Gulp.jsAfter installing gulp and chúng tôi we need to create a starter using the express-generator.
Code:
$ npx express-generator -v ejs –sample-project $ cd sample-project $ npm installNow create a GitHub repository for our project, as shown in the below screenshot.
Copy the repository URL and make the initial push using the following command.
Code:
$git init $git remote add origin specified url $git add –A $git commit –m “commit” $git push origin master gulp js Project StructureNow let’s see the project structure as follows.
The following screenshot shows the project structure of chúng tôi follows.
Using the above structure, we can create an automated workflow such as compiling CSS files from the source, creating HTML pages, checking application code, etc.
ConclusionWith the help of the above article, we saw about chúng tôi From this article, we saw basic things about Gulp js and the integration of chúng tôi and how we use it in Gulp.js.
Recommended ArticlesThis is a guide to chúng tôi Here we discuss the introduction, creating server chúng tôi configure, and workflow for better understanding. You may also have a look at the following articles to learn more –
Update the detailed information about How To Create Excel Template? on the Moimoishop.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!