Excel Formulas Archives

Use Python Xlsxwriter To Create Exc...

Use Python Xlsxwriter To Create Excel Formulas And Links (Part 2)

Excel Tip- Formula Friday -How To Create a drop down list for your hyperlinks in Excel

September 23, 2022 by Barbara —2 Comments

Adding a drop down list to your Excel sheet is a really great and convenient way to allow data entry to your Excel worksheets whilst maintaining good quality consistent data and reducing the amount of space taken up by lists!. If you want to review how to create a drop down list for data validation then check out my blog post here. So, if you are like me you possibly store a lot of hyperlinks in Excel?. They could be for reports, websites, data sources…you get the idea. I have all of my websites in one worksheet which is usually open all of the time.

So I am going to show you how to use the same drop down validation list to store Excel hyperlinks and reduce the amount of space taken up on your worksheet. (Real estate space is of a premium on a worksheet don’t you agree?) We will also insert a clickble cell to take you right to hyperlink destination.

Its so simple to do. Here is my example. I have a named range called My_Links. They are stored on Sheet2 starting at cell A1.

Select the cell where you want your drop down list to appear. In my example it is in C4.
Select data- Validation
Data Validation Settings tab, select Allow: List, and make sure the In-cell dropdown box is checked.
In Source ensure your range of cells (My_Links)
Click Ok

Select E4- this is where the clickable cell will be set up
Finally, in E4 type the following =HYPERLINK(C4,”Take Me To Website!”) – type anything you want to.

So that’s it. A super easy way to create a drop down list for your Excel hyperlinks. Have you used this method before? Let me know in the comments below.

More Excel Tips

Formula Friday – Let’s Use TEXTJOIN To Join Text Strings Without The CONCATENATE Complexity

September 9, 2022 by Barbara —Leave a Comment

Happy Friday Excellers. Welcome back to another #FormulaFriday #Excel blog post in my Excel series. I will compare TEXTJOIN and CONCATENATE. If you have Excel 2016 then you may be already aware of some of the new functions that are available. One of those functions is TEXTJOIN, and also CONCAT which was introduced in February 2016. I have been using TEXTJOIN recently to replace the CONCATENATE function I used on a regular basis. I get the same results, without the hassle the CONCATENATE brings.

Want To See How Simple TEXTJOIN is?

Let’s carry out the same process, first with CONCATENATE then TEXTJOIN. Here is the data below, and we are going to carry out some standard joining of the two strings, First_Name and Last_Name.

Here is the difference between the two formulas TEXTJOIN and CONCATENATE.

The Syntax of TEXTJOIN

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Where

delimiter – this is required this is what you want in between the cells that you are joining, for example- a comma, or space.

ignore_empty – this is so you can choose to ignore and empty cells in your selected range. This can be important if you are using a space ” ” as your delimiter and if you do not want extra spaces in between your joined text strings due to blank spaces in your selected cells.

text1,-is required, which can be a single text string or an array of strings like a range of cells

[text2] – this is an optional argument; use this additional text strings to be added to text1

note:- if the length of the value created by TEXJOIN is over 32,767 characters long then Excel will return a #VALUE! error.

It is really useful to declare the delimiter at the beginning of the formula and then be able to select the range of cells to join, in particular when the number of cells you want to join increase in number, as you can see in the example below. TEXTJOIN makes life so much easier and I am definitely a convert!.

Formula Friday – 3 Reasons To Use Named Ranges In Your Excel Formulas

July 29, 2022 by Barbara

Hey Excellers, thanks for joining me for another #formulafriday. Today I want to share my top 3 reasons to use named ranges in your formulas. There are many benefits to using named ranges in your Excel formulas. This blog post will discuss three of the most important reasons why you should start using named ranges today. Named ranges can help you save time and improve accuracy in your data analysis, and they are straightforward to use. Let’s take a closer look at each of these benefits. Starting with the basics how to create named ranges in Excel.

What Is A Named Range?

A named range is a name that refers to either a cell, a range of cells, a constant value or a formula. Named ranges can then be used in a formula to replace a constant value or a cell reference. So, for example, if you have a constant value in a cell such as Exchange Rate, you can name that cell that contains your exchange rate value as ‘Exchange Rate’ instead of using A2 in your formulas.

What Are the Rules?

Yes, there are a few rules about naming conventions, but they are not too confusing!!

What characters? The first character of a name must be a letter; an underscore character (_), or a backslash (\). The remaining characters in the name can be letters, numbers, periods, and underscore characters. Tip: You cannot use the uppercase and lowercase characters “C”, “c”, “R”, or “r” as a defined name because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.
Cell references are disallowed. Names cannot be the same as a cell reference, such as A2
No Spaces, please. Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, for example Sales_Tax or Second.Quarter.
Name length. A name can contain up to 255 characters.
Case sensitivity. Names can contain uppercase and lowercase letters. So if you create a name called SALES_TAX and then try to create another sales_tax you will be prompted to create another unique name.

How To Create A Named Range

There are a few ways to create a named range in Excel. Here are three ways as a lightening fast guide on how to generate those names.

Use the Name Box.

Select the range of cell or cell you want to name.
Click in the Name Box in the top left of the workbook.
Name your cell or range of cells.
Hit Enter.

Create Names From Worksheet Labels.

Select the labels and the cells you want to name.
On the Formula Ribbon – Defined Names – Create From Selection.
Select the location of the label you want to use.
Hit Ok
Your selected name will appear in the Name Box.

Use The Name Manager.

Formula Tab – Defined Names – Define Name
Give your range a name
Type in your value for this named range or use the selector to select a cell that contains your named data.
Hit Ok

Ok, so here are my top three reasons for using those named ranges in a formula.

Your Formulas Will Make More Sense.

Names make your formulas more understandable. Let’s look at my monthly sales summary in Euros. The total figure at the end of the summary is required to be converted to GBP to send to Head Office. We can click on the cell that contains the Total Sales In GBP calcualation and can easily see that the Named Range of Exchange_Rate is multipled by the value in cell B16 which is the Total Sales Value. We could make the formula even more strightforward by naming cell B16 as Total Sales Value……… so readers would not have to look to see what number cell B16 is refering to….

You Can Easily Edit Your Formulas.

Let’s say that you need to change the Exchange Rate that the company is using this month. By using the Name Box to select the Exchange_Rate named range we can quickly navigate to the correct cell, and change the rate, which then populates anywhere we are using the Exchange_Rate reference in a formula. Nice and easy.

Lightening Fast Simple Formula Creation.

Once your ranges are named or defined then you can use them over and over again in formulas. Let’s return to the Sales Summary that we need to send over to Head Office. I have gone ahead and named some more ranges in the worksheet for Total Sales Summary and Total Sales Volumes. You can see in the Name Manager which range of cells they refer to.

I can now write super fast formulas using the names. Let’s look at the average unit price our Sales people are getting for our products.

So that’s my top 3 reasons for using named ranges with formulas. Hope you enjoyed today’s Formula Friday Excel Tip.

Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.

How To Create An In Cell Bar Chart Using The REPT Function In Excel -Formula Friday

July 8, 2022 by Barbara

Hello Excellers it is Friday again and time for #formulafriday where we have some Excel formula fun. Have you used the REPT function in Excel or ever used it?. It’s a bit elusive so let’s get it out of the shadows and bring into the limelight as see how we can use it!. Are you ever short of space on your Excel worksheet? Did you know that you can create in-cell bar charts?. You can do so using the REPT function in Excel and produce a very handy Excel chart like the one below. They are really easy to set up and very simple to use.

Did You Know About The REPT Function?

In my experience, not that many people have heard of or use the REPT function. So what does it do?. The Microsoft Excel REPT function returns a repeated text value a specified number of times. The syntax of it is as follows-

=REPT(text, number_times)

With the following arguments.

text – this is a required argument, and is the text you wish to repeat.

number_times – this argument is also required and is a positive number specifying the number of times to repeat the text in the first argument.

Let’s get charting!. And Create An In Cell Excel Chart

So, follow the steps below to create your in cell Excel chart.

Enter the REPT formula in the cell you want the chart to sit
Use a divisor to make the length of the bars smaller
Chnage the font to a more ‘chsrt like font’. I have used IMPACT
Adjust the colour of the font to fit your overall theme
Simple. An in cell chart with the REPT function in Excel

The formula I have used in this exmaple is

=REPT(“|”,B4/100) I have used 100 as the divisor to make the length of the cells more appropriate for the visual chart.

Formula Friday- Two Ways To Create A Time Stamp In You Excel Workbook

July 1, 2022 by Barbara

Do you need to create a timestamp in Excel? You have two methods to choose from. In this blog post, we’ll show you how to do it with each method. Read on to learn more! This really is THE most common question I receive my email, or on my website. – How can I create a time stamp on my Excel sheet?. Users get really frustrated that there is not an already created function for this in Excel as it is obviously widely used. There are a few ways to achieve this, one is using an Excel formula, and the second method involves a tiny bit of VBA.

I thought I would consolidate the two best ways into one blog post, and you can choose the best option for yourself either formula route or VBA option.

Create A Time Stamp Using An Excel Formula

We probably all know that we can use the =TODAY() formula or the =NOW() formula to enter a date into Excel, which update automatically. This function is known as “volatile" as the function recalculates when Excel updates.

This is not really any good for a timestamp, as we would want an invoice entry or project update to be static. We therefore can use

CTRL+ ; to enter the current date or

CTRL+SHIFT+: to insert the current time

These both will remain static, but are still very manual having to insert the date and or time with keyboard the above shortcuts. That is all well and good but wouldn’t it be great to just insert your changes to a worksheet and the timestamp appear as you type?. You can but it takes a bit of Excel magic and an understanding of Circular Formulas.

What Is All This About Circular References?

You may have come across them before but if not here’s a quick run down. A circular formula is one that refers back to itself either directly or indirectly. Let’s take a look at an exmaple.

Lets look at a direct example.

If I type =A4+1 in cell A4 I am directly referring to the cell I am writing the formula in. This results a warning from Excel in case you did not create this circular reference intentionally (as we did) and the result of the formula is zero.

Let’s look at an indirect example

If I type 2 into cell A1,

then type =A1 into cell A2

then A2 refers back to A1 and gives a result of 2 which is a normal formula.

excel timestamp indirect circular reference

If I then go back to cell A1 and enter the formula =A2+3, this will create a circular formula as A2 is based on A1 and I am writing the formula in A1 so essentially the formula is referring to itself.

You can see above that the circular reference is indicated by Excel with blue vertical line after selecting OK to it’s warning. Ok so let’s take this one step further.

There is a setting in Excel called ‘iterations’, which tells it how many times to recalculate a formula. Without this function, Excel would go into an infinite loop and eventually stall. By default this is turned off in Excel which is when when you type a circular reference , Excel throws you a warning and doesn’t calculate the formula as it could run infinitely.

Turning On The Iterations Function

1. Office button

2. Excel Options

3. Formulas

4. Iteration

5. Change the default iteration to 1, so it recalculates one time before it stops.

In our case this will result in cell reference A1= 5. So, at the start of this post we were to insert time stamps into Excel. We can exploit this circular referencing and iteration to our full advantage based on the theory above to insert our required timestamps.

Let’s go ahead and do that. In a scenario that we want the Excel timestamp to be in Column A when we are entering data into Column B

1. Type the following formula into A1

=IF(B1<>””,IF(A1=””,NOW(),A1),””)

2. Type something into Column B

3. Your date and time will be stamped.

4. Drag the formula in Column A down to allow time stamping all down your column.

Isn’t that just the bees knees?

What Is The Excel Formula Doing?

The formula is checking cell B1, and if it is not empty it runs the circular formula =IF(A1=””,NOW(),A1) which returns the NOW() function if A1 does not already have a value- thus stamping the time.

Go ahead and give this a go, let me know if you have any other way of creating a time stamp in Excel?

Create A Time Stamp Using VBA

The formula solution, whilst very useful does not suit everyone and has some restrictions in itself. If you are brave enough to take on a small bit of VBA code, we can create and event handler macro which is a good alternative solution to using the formula method which I find is slightly more robust and flexible.

This was a an alternative solution for a subscriber rather than the formula date and time stamp method. He wanted the time stamp to update if he decided to overwrite the contents of the target cells again and again. This I believe gives that solution.

There is no need to insert a module in, or use a module in this piece of code, it is written directly to the worksheet you want the time stamp to appear on.

Step by guide to inserting a timestamp into Excel with VBA.

So, we are assuming that the date time stamp will be entered into Column B when an entry is made into Column A.

Open up your Excel worksheet and hit F11 to open the Visual Basic Editor. ( if you need to enable the Developer Tab you can find out more here)
Double Click on the worksheet where your time stamp is to appear- in this example it is Sheet 1.
In the left hand drop down box select Worksheet and in the right drop down box select Change. (This will create a macro that runs when the worksheet changes), and the Value Target As Range means any cell we choose to apply this to.

We want to then type the following code underneath the First Line that Excel generated for us.

It should look like this-

Let’s talk through the code. It is an If Then Statement with the following meaning.

If The Target Column 1 changes, then we want the cells in the Target Row in the Second Column to change to the value of Date and Time. Easy huh?.There a number of extra lines of code in there as you can see, the Application.EnableEvents.

By adding these lines to the code ensures that the macro does not run on and on. This works by turning off the macro then turning it on as required.

This solution allow overwriting of previous entries, and will update with the latest date time stamp. So, if you find the Formula way of inserting a time stamp into an Excel worksheet then these few little lines of code may just be your solution.

Finally,, the result of this piece of code looks like this.

So there you go, tow methods to create a time stamp in your Excel workbook. Most users have emailed me looking for this type of solution. They are using excel as a sign in sheet for a class or conference. What other reasons have you used the time stamp for? Share in the comments below.

1
2
3
…
42
Next Page »

Privacy Policy