Formulas Archives

Formula Friday – Use The Search Function To Create A Search Bar In An Excel Cell.

January 14, 2022 by Barbara —Leave a Comment

Happy Friday Excellers Welcome to another blog post in my Excel #formulafriday series. . Today let’s look at creating a search bar right in an Excel cell in our workbook. Yes, you can use the search function in Excel to create a dynamic search bar right in an Excel cell on your worksheet.so.

So, let’s work through an example. Here is my sample data set.

I have a shortened list of Sales Invoices. I want to use the Cell C3 as a search cell. Subsequently, I want Excel to highlight all of the cells in the data set that match the value that is in Cell C3.

We do this with Conditional Formatting, and using a formula to determine which cells to format.

Conditional Formatting Process.

Home Tab
Styles Group
Conditional Formatting
Use A Formula To Determine Which Cells To Format

SEARCH FUNCTION CREATE SEARCH BAR IN CELL

The Search Function In Excel. Syntax Recap.

The formula is simple. THE SEARCH Function has 3 arguments

find_text – The text to find.
within_text – The text to search within.
start_num – [optional] Starting position in the text to search. Optional, defaults to 1

So in our example, we are searching for the text in $C$3 in range starting in E4, we can leave the optional argument start_num so it defaults to 1. If the text in $C$3 is found in E4 then the condition is TRUE, the formatting is applied. Simple.

Finally, if you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.

How To Excel At Excel – Formula Friday Blog Posts.

Learn Excel With Our Excel Online Courses

Formula Friday – How To Count Cells That Contain Text Using COUNTIF

October 15, 2021 by Barbara

Hello Excellers, welcome to another #Exceltips blog post in my 2021 series. recently I have been counting cells with text in my Excel worksheet. So, I thought it would be a great Excel tip to share.

So, do you need to count how many cells that contain text?. I have used this technique to how many rows in my Excel data set contain any text. For example, the text references in my data represent a particular type of customer, which I can easily count with the formula COUNTIF and a wildcard!

So, here is a speedy way to count how many of your cells contain text. (That is not numbers, not errors, not blanks).

Let’s work through an extract of my data set.

counting cells that contain text in Excel

I want to see how many of my customers have text in their customer_id. The syntax of the COUNTIF formula is

=COUNTIF(range, criteria)

Where

range= the range of cells from which you want to count non blank cells

criteria = the condition, i.e a number, expression or text that determines which cells should be counted.

We can use the wildcard * as the criteria argument in our formula, to search for ANY text in our customer_id data range. The wildcard is surrounded by quotation marks (” “).

Excel has correctly identified that I have 2 customers in my list that contain some type of text characters. Job Done!. Easy as that. A simple way of counting cells with text in Excel.

Do you want even more Excel Tips?

Add Emojis To Your Excel Formulas. Excel Tip.

October 8, 2021 by Barbara

Hello, Excellers. Welcome back to another #FormulaFriday Excel tip. Today I will show you how to add emojis to your Excel formulas. Yes, emojis are everywhere. Even in Excel formulas if you want them to be there. So if emojis are your thing, follow the instructions below to use them in your Excel formulas. Let’s take an example from start to finish.

My data set is below. Let’s use the 👍 emoji if sales are on or ahead of target. Alternatively, we can display the 👎 emoji. The result should be like the data in the screengrab below. Emojis are shown as black and white in Excel on desktop, but Excel online is full colour.

The IF Formula is used to create this result. The syntax of the IF function is below if you need a refresher.

=IF (logical_test, [value_if_true], [value_if_false])

So, in this example, the logical test is if the Actual Sales are greater than or equal to Sales Target. If the logical test is true (or met), then insert the 👍emoji. If the test is false,e then 👎 will be inserted into the cell.

So, to insert the emojis, hit the Windows key and the period key (.) or the semi-colon key (;). Then, select the emoji you need.

The full formula looks like the screen- grab below.

Follow the steps below to write the formula. To access the Emoji hit the Windows plus period or full stop, or semi colon.

Do you want more Excel Tips?.

Finally. if you like free Excel tips then why not sign up for my monthly newleteer. I share three FREE Excel tips direct to your email box. No spam. I also share some great Excel course discounts when I can. I hope you can join thousands of other Excellers in this great community. See you over there.

Formula Friday- Using Concatenate

October 2, 2021 by Barbara

Hi again, it’s Formula Friday yet again. This time, let’s look at CONCATENATE in Excel. Now, I always say to users who ask me about concatenate, do not be put off by the long word. It, in essence, simply means to join the contents of two cells. However, there is another way to achieve the same result. So, I will discuss this as an alternative at the end of this post.

Concatenate Syntax. A Reminder.

The syntax of this formula is

=CONCATENATE(TEXT1,TEXT2…)

Up to 255 text entries can be added to the function and each one of them should be separated by a comma.

One point to note, CONCATENATE in Excel does NOT add in extra spaces between text, in order to do this you need to accommodate these extra spaces within the formula. Let’s take a look at an example.

The First Name and Surname are in Columns B and C in Excel. The aim is to join them so both names in are in one Column- D.

=CONCATENATE(B2,C2)

In most cases with names you will want to show a space between the first and last name. All we need to do is change the concatenate formula to include space character is quotation marks like this.

=CONCATENATE(B2,” “,C2)

Bit of a long winded way to join a few pieces of text isn’t it. Well, yes I agree and you can achieve the same result with waaaay less typing, always a good thing in my world. All we need to do is use the & sign. Really? Yes really easy as that.

Let’s give the same example

=B2&C2

Again if you want to include spaces between your joined text then all you need to do is amend your formula to also include a space character in quotes. See below.

=B2&” “&C2

What About Combining Text and a Date?

Yes we can do that too. Not a problem, well one small mini problem-the number and dates lose their formatting so if I add in a date column to add to the Fist Name and Surname , it ends up looking like this using the same formula as before but just adding in ell D2. Excel does not take into account the formatting of a date here – see below.

But as ever we can put this right in Excel just by including the TEXT function into our formula to re format our dates Let’s give it a go.

=B2& ” ” &C2&” “& TEXT(D2,”dd-mmm-yyyy”)

Job Done! It is that easy to use concatenate in Excel.

Do you want even more Excel tips?.

Can’t Remember Where Your File Is Saved?. Cell Function To The Rescue

September 24, 2021 by Barbara

Hello, Excellers, welcome to another #formulafriday blog post in my Excel Series. Today, let’s look at a simple way to find out where you have saved your file. If you cannot remember the exact file path, using the CELL function in Excel will solve this problem.

So, did you know that this is one of the FREE tips I shared directly with my subscribers‘ inboxes every month?. So, why not sign up for my Newsletter and get fresh Excel tips every month?

Why Don’t You Use File | Save As?

Well, yes, you probably are saying I could use the File | Save As process to save the file again. Yes, that is correct, but using the CELL function on your worksheet can act as a permanent reminder on your Excel worksheet of the file save location you have chosen for your Excel file. So, use the CELL function as a once-off or have a reminder at the top of your Excel worksheet.

Introducing the CELL Function.

The CELL function returns information about a cell’s formatting, location, or contents, and its syntax is straightforward.

=CELL(info_type, [reference])

Where

info_type – this is a text value that specifies what type of cell information is required. This is a required argument. The list below shows the possible values of the argument and corresponding results.

reference – the cell that you want information about. This is optional. If left out, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range.

In this example today, I will use the “filename” value highlighted in the above graphic. Excel returns the full file path of the current workbook as text in the cell that contains the function. Empty text is returned if the file has not been saved previously.

So, let’s take a look at an example with my current file. The first argument of “filename” is used, returning the full file path. The name is New Worksheet, and I am currently working on Sheet4. How easy was that?.

If you want more Excel and VBA tips, then sign up for my FREE monthly newsletter where I share three free Excel tips. When you sign up you will get my FREE Top 30 Excel Tips E Book.

More Excel Tips.

Finally, don’t forget to bookmark my Macro Mondays and Formula Friday pages. They contain all of my next Excel tips and are updated every week.