formula friday Archives

Delete The First Two Digits In My Cells.

November 25, 2022 by Barbara —1 Comment

Hello Excellers and welcome back to another blog post in my series of #FormulaFiday #ExcelTips. Today, let’s answer an Excel question from one of my subscribers. They want to be able to delete the first two digits of a string in a column of data. No other instances instances of the digits in other parts of the cells are to be deleted. With about 5000 rows of data, only the first two digits of the string which are ’98’ need to be removed. No other instances of these digits should be deleted from the data set. Here is a hint how to solve this. We will use both the RIGHT function and the LEN function.

So, we cannot use the Find and Replace process as this would replace all of the instances of 98 in the cells. A data sample is below. We will be using the RIGHT function and the LEN function in this solution.

Delete the first two digits in my Excel cells. use Right Function. Use LEN Function.

Using Right and LEN Functions.

So, as already stated, the solution to this problem is to use both the RIGHT and the LEN Functions in Excel. The first part of the solution is to use the RIGHT function. The purpose of this function is to extract text from the right of a string. This sounds like this would be useful for this solution. The syntax of the RIGHT function as a quick recap is:

=RIGHT (text, [num_chars])

Arguments are as follows-

text – The text from which to extract characters on the right.
num_chars – [optional] The number of characters to extract, starting on the right. Optional, default = 1.

So, let’s now move onto the second function we are using in this solution. THe LEN function, which returns the length of a given text string as the number of characters. LEN counts characters in numbers, but number formatting is not included. The syntax of the LEN function as another quick recap is:

=LEN (text)

Arguments are as follows –

text – The text for which to calculate length.

Writing The Excel Formula To Delete The First Two Digits In Excel.

Finally, it is time to bring all of this together and delete the first two digits of our cells. As already discussed, we cannot use Find and Replace as it would replace all instances of 98 in the cells. So, let’s build the formula and walk it through.

The RIGHT function takes the string in A2, this is the text to extract the characters from the right. The number of characters that need to be extracted (the second argument in the RIGHT function), is calculated by the LEN function. This is calculated as 7 minus 2 which leaves us with the five digits from the right.

delete first two digits using RIGHT and LEN function

So, how cool is that?. This leaves any other instances of the 98 combination in place. A simple but clever way to delete the first two digits in my Excel cells.

Formula Friday – Find And Hide All Formulas On An Excel Worksheet

November 18, 2022 by Barbara —Leave a Comment

Happy Formula Friday, Excellers. Formulas are a huge part of Excel, and they can be very helpful in organizing and analyzing data. However, sometimes you may need to hide them from view so that your worksheet is easier to read. In this blog post, we will show you how to find and hide all formulas on an Excel worksheet. Stay tuned!

This tutorial is a response to a question asked by a colleague recently. How to hide all Excel formulas in an Excel workbook?. The workbook due to be shared contained a lot of formulas that the end-users did not need to view. So, let’s hide any Excel formulas found in the workbook.

Why Hide Excel Formulas?.

I often hide Excel formulas. There are a few reasons behind this.

I do not want other users to see the formulas.
Protection of the Excel formulas. I do not want users to change them in any way, maliciously or by accident.

How To Hide Excel Formulas.

So, here is a quick and efficient way to identify the cells with formulas and hide them. There are a couple of (easy) steps to it.

Step 1.

Select all of your data on your worksheet.
Hit F5
Click Special
Select Formulas

learn how to find and hide Excel formulas

Now all cells with formulas are selected. I have formulas in several areas in the example I am using, which are all highlighted.

Step 2.

Now to hide the formulas. All Excel cells have two attributes. Locked and hidden. Locked cells are just that, locked and cannot be changed by an end-user, whereas the hidden attribute means that formulas contained in those cells will not be displayed in the formula bar at the top of the worksheet.

We do however need to also remember that none of these attributes are in force until a worksheet is protected. So, onto to the detail of Step 2. We already have our cells with formulas selected –

Home Tab – Cells Group
Format – Protection – Protect Sheet
Ensure that Protect Worksheet and Contents are selected
Enter a Password when promtped
Job Done!

Now, if we click or select one of the cells that contained a formula, see if you can see your Excel formulas now?…..nope…thought not……!

In this blog post, we have discussed how to find and hide Excel formulas. The steps are quite simple if you follow the article’s instructions. We hope that this information was useful for you!

So, how do YOU prevent users seeing or accessing your Excel formulas? Leave a comment below.

More Excel Tips.

Formula Friday – Why is my formula showing in a cell and not the result??

November 11, 2022 by Barbara —Leave a Comment

Hello Excellers. Welcome to another blog post article in my #Excel series. When you create a formula in Excel, you may expect to see the result of that formula immediately show up in the cell where you typed it in. But sometimes, instead of seeing the result, you see the actual formula displayed in the cell. So what’s going on, and how can you fix it? In this blog post, we’ll look at why formulas appear in cells instead of the results and how to fix them. Stay tuned! You can also watch the video to accompany this article. Why not subscribe to my YouTube channel and get immediate access to all of my playlists.

So, you may have already come across this issue in Excel. If you have not, you probably will at some point. Yes, the mysterious case of Excel shows the formula not the result. Your Excel worksheet cell will look something like this below.

Let’s work through the possible solutions to this problem and the reasons Excel is behaving like this.

Reason 1. Show Formulas Option Is Enabled.

The first reason Excel shows formulas, not results, is that you have Show Formula selected. Yes, that is correct. Have you accidentally set the Show Formula Option? It may be the reason Excel is showing formulas rather than the result of the formulas. You may have accidentally hit the Excel shortcut CTRL+’. To return this to normal view try hotting CTRL+’ again. Show formulas are available on the Formula Tab | Formula Auditing | Show Formulas.

Reason 2. Something In Front Of The Equals Sign.

The second reason Excel maybe shows formulas instead of results is that there is a space or some other symbol before the equals sign. If this is the case, then Excel will treat the formula as text and not evaluate it as a formula. So, Excel displays the actual cell contents.

Reason 3. Cells Are Formatted As Text.

So, if the formula looks good and you have checked for reasons one and two, there is another reason. Check that your cells’ formatting is not set as text. If your cell formatting is set to text then Excel will treat the formula as a text string. So, instead of evaluating the formula and generating the result. A text string of the formula is displayed. This however is a really easy fix, select the cell and reset the formatting. First, set the format to General, try changing the cell mode to edit (click in the formula bar or use F2, then enter. Excel will recognise the formatting change, resolving the text formatting issue

We hope this article was helpful in explaining why your Excel formula is not displaying the result you expect. If you have any further questions, please don’t hesitate to leave a comment and ask questions.

Need An Excel Problem Solved?. Try Excel Rescue.

Dont forget to sign up to the Excel at Excel Newsletter for THREE FREE Excel tips every month. Just click on the Sign Up Form to the right or use the link below.

Formula Friday – How To Use RANK In Excel But Ignore Zeros

October 28, 2022 by Barbara

Happy Friday Excellers. Today I want to share with you how to RANK a data set with the rank formula in Excel, but ignore zero values. I use RANK a LOT, and sometimes you just do not want to include zero values in your results. When you are working with large data sets in Excel, sorting and ranking your data can be a helpful way to narrow down your focus. However, when you have zero values in your data set, it can be tricky to determine where your data should be ranked. In this blog post, we will show you how to use the RANK function in Excel to ignore zeros and rank your data accordingly. Let’s get started!

Rank Excel Formula Ignoring Zeros

We actually are going to make use of the IF function as well as RANK Excel function in this solution. Here is sample data set I want to rank.

I have 10 lines of data and 2 of them are zero. I do not want zeros to be included in the ranking values or calculations. This is the formula I am using to achieve this.

=IF(B4=0,””,RANK(B4,$B$4:$B$13,0))

Let’s Walk Through The Formula.

If B4 contains a zero then Excel returns a blank cell “”. If the value in in cell B4 is not equal to zero then the value is ranked in the cell range of B4 to B13. This returns the ranking of 2. We can then drag the formula down the length of the data range to populate the whole data set.

I hope you found this tip on how to use the Rank Excel Formula. See you next time for more #FormulaFriday fun.

Dont forget to sign up to the Excel at Excel Newsletter for 3 free Excel tips . I send out these tips every month. Just click on the Sign Up Form to the right or use the link below.

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

September 23, 2022 by Barbara

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.