Excel Formulas Archives

Formula Friday – Resize The Excel Formula Bar.

April 28, 2023 by Barbara

Hello Excellers, welcome to another #formulafriday blog post in my #ExcelTips series. Excel has a wide range of features, and the formula bar is one of the most important of them. The formula bar is where you can see and edit the formulas that you have used in your spreadsheet. But sometimes, the default size of the formula bar might be too small, making it difficult to see the entire formula. In this article, we will discuss how to resize the Excel formula bar to see more of your Excel formulas.

Why Resize the Formula Bar?

The default size of the formula bar is small, and sometimes, the formulas you use can be long, making it difficult to see the entire formula. This can be problematic, as you may not be able to edit the formula or check for errors. Resizing the formula bar can make it easier to see the entire formula, and make it more convenient to edit it.

Does This Work In All Versions Of Excel?.

In versions of Excel earlier than 2007, editing a cell that has a lengthy formula or a cell containing lots of text is not easy. It can often block part of your worksheet. In Excel 2007 onwards, it addressed in more recent versions with a resizable formula bar!!.

Resizing the Formula Bar. Method 1.

Resizing the formula bar in Excel is a simple process. Follow the steps below to resize the formula bar.

Step 1: Click on the Formula Bar

The formula bar is located above the worksheet and below the ribbon. Click on the formula bar to select it.

Step 2: Move the Mouse Cursor to the Bottom of the Formula Bar

Move the mouse cursor to the bottom of the formula bar. The cursor should change to a double-headed arrow.

Step 3: Drag the Formula Bar Down

Click and hold the left mouse button, and drag the formula bar down to resize it. You can drag it down as much as you want until you get the desired size.

Step 4: Release the Mouse Button
Once you have resized the formula bar, release the mouse button.

Resizing the Formula Bar. Method 2.

Click the arrow at the right side of the formula bar. This expands the formula bar. Easy!

Both methods work equally as well to adjust the formula bar for Excel.

Want To Use The Keyboard Shortcut To Resize The Formula Bar?

Of course there is an Excel shortcut to do this. Its is CTRL+SHIFT+U. To restore the bar again just hit the shortcut again. Why not give this a go and practice resizing the bar.

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.

Formula Friday – 3 Ways To Protect Your Excel Formulas

March 31, 2023 by Barbara

Hi Excellers, this is a different #formulafriday in my Excel 2020 series. I want to share with you 3 ways to protect your Excel formulas.

So, you have spent a long time preparing your Excel spreadsheet solution, and I bet you don’t want anyone to mess up it up. So there are a few ways to protect some of those most vulnerable parts of your Excel spreadsheet.

Here are my top 3 ways to protect your Excel Formulas!!

Hide them
Lock Cells
Hide The Formula Bar With Some Simple VBA.

1.Hide The Formulas.

This method will temporarily hide your formulas, but you will be able to use them again if you need to. It’s simple and straightforward. Here we go.

Select all of the cells that contain formulas that you want to hide.
Home Tab – Cells Group – Format – Format Cells
Navigate to the Protection Tab
Check the Hidden option and hit Ok

This doesn’t in itself hide your formulas, you need to then protect your worksheet to ensure these settings work.

Select Review Tab
Changes Group
Select Protect Sheet
Enter a password and confirm password when prompted
That’s all you need to do.

Try selecting a cell that contains a formula. The formula will not be visible in the formula bar. If you want to see the formulas again simply unprotect your worksheet.

2. Lock Cells.

The second method is to just lock the cells that contain formulas so they cannot be selected or edited by users. By default all cells in a workbook are locked, so you will need to unlock them all to start with.

Hit CTRL+A to select all of the cells on the worksheet
Home Tab – Cells Group -Format – Format Cells
Untick Locked, to unlock all of the cells on the worksheet
Hit Ok

Now all we need to do find all of the cells that contain formulas..

Hit F5 to bring up the GoTo Dialog Box
Select Special – Formulas – Hit OK
All of the cells that contain formulas will be highlighted

Then we need to lock those highlighted cells.

Home Tab – Cells Group -Format – Format Cells
Navigate to the Protection Tab
Check the Locked option and hit Ok

This doesn’t in itself lock your formulas, you need to then protect your worksheet to ensure these settings work.

Select Review Tab
Changes Group
Select Protect Sheet
Enter a password and confirm password when prompted
That’s all you need to do.

3. Hide The Formula Bar With Some Simple VBA

My third method of hiding your formulas is to actually hide the formula bar on the Excel worksheet. This is easily achieved by a very small piece of VBA coding or an Excel Macro.

This macro uses the Application Object and we are looking to use the DisplayFormulaBar property of it.

To use this small piece of coding, you need to insert it into a module in your Excel workbook.

Open Visual Basic – by hitting F11 or Developer Tab – Visual Basic – Click Modules, and Add New Module.

Here is the VBA code if you want to copy it. Just paste it into a module you have created as per the instructions above.

Sub HideFormulaBar()
Application.DisplayFormulaBar = False

End Sub

Just as we have hidden the formul bar we can easily write some VBA to show the formula bar again

Sub ShowFormulaBar()
Application.DisplayFormulaBar = True

End Sub

In this instance we set the Application.DisplayFormulaBar to TRUE to display the formula bar.

That’s it. Hope you enjoyed Formula Friday.

Don’t forget to sign up to the Excel at Excel Newsletter for 3 free Excel tips monthly. Just click on the Sign Up Form to the right or use the link below.

Learn Excel With Our Excel Online Courses

Highlight Locked Excel Cells With The CELL Function. Formula Friday

February 17, 2023 by Barbara

Hello Excellers, and welcome back to another #FormulaFriday #Excel tip in my Excel series. Todays article walks you through highlighting cells on an Excel worksheet that are locked. As an Excel user, you may have encountered a situation where some of your cells are locked. Users lock cells for various reasons, such as protecting formulas from being edited or preventing users from accidentally entering data into the cell.. One way to do this is by using the CELL function. This function will return a number that corresponds to the locked status of a cell. Let;s learn some Excel!.

So, if you have created a worksheet a long time ago and can’t remember which cells are locked, or if you inherit a worksheet and need to do the same, then this tip is very useful. Let’s go ahead and lock all of the UnitCost cells first.

Lock The Excel Cells.

Follow the steps below to lock your cells.

Select the full worksheet by Ctrl +A+A.
Home Tab | Cells Group | Format | Format Cells | Protection.
Deselect or remove the tick from the locked option.
Select the cells you want to lock on your Excel worksheet.
Repeat the process above by selecting the tick option.
Review Tab | Protect Group | Protect Sheet

With locked cells set, use conditional formatting in conjunction with the CELL function. The CELL function returns information about a cell.

The Syntax Of The CELL Function.

Here is a quick recap of the CELL Function syntax.

=CELL(info_type, [reference])

The CELL function syntax has the following arguments:

Info_type This is a required argument. A text value that specifies what type of cell information you want to return.

The following list shows the possible values of the Info_type argument and the corresponding results.

Reference This is an optional argument relating to the cell that you want information about. If omitted, 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.

The Protect Info type is required. Excel returns the value 0 when the cell is not locked; otherwise, it returns 1 if the cell is locked. So, this information can be used with conditional formatting to identify locked cellsin the Excel worksheet.

Set The Conditional Formatting To Highlight Locked Cells.

First, select the range to be formatted.
Next, select Home Tab | Conditional Formatting | New Rule | Use A Formula To Determine Which Cells To Format. Enter the following formula.

=CELL(“Protect”,A1)=1

Select the type of formatting to be applied if the result of the formula is true. Blue is selected in this example.

That’s it. Easy as that. In this blog post, we identified locked cells in Excel with the CELL Formula and conditional formatting. Locked cells appear highlighted with the formation chosen by the user. What an easy way to highlight locked cells. Do you have an alternative?. Please share in the comments below so other Excellers can learn.

I hope you found this information helpful! If you’re looking for more help with Excel, be sure to check out our other posts or contact us for assistance. Don’t forgot to check out my You Tube Channel for more Excel skills.

Finnally, for more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips every month. You will receive my free Ebook, 50 Excel Tips.

Likewise, if you want to see all of the blog posts in the Formula Friday Series Click The Link Below

How To Excel At Excel – Formula Friday Blog Posts.

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more monthly Excel tip. Simply click on the link and enter your email. No spam. Just Excel tips.

Generate Unique ID Numbers From Your Excel Data.

February 10, 2023 by Barbara

Hello Excellers and welcome back to another #formulafriday #exceltip in my Excel tip series. Today I want to share with you a question I was asked by a subscriber. They need to generate unique ID numbers or references, from their data set. Or to explain more take the first three letters of the location of the account (in this case the Country) name, and then incrementally generate numbers for the accounts.

You can see a sample of a similar data set and the expected outcome that is required in the screenshot below.

Generate unique id in Excel — Generate A Unique User ID In Excel

So, just how can we achieve this result. It’s rather easy. We are going to use three formulas or methods to do do this. W. start with the CONCATENATE Function, the LEFT Function, and the COUNTIF Function. By combining these three awesome methods the unique ID is a breeze. Let’s get started.

Let’s Begin The Formula!

generate a unique ID with Excel data set

PART 1. We start off using the CONCATENATE.

This will join several strings together into one text string. This sounds like what we need. The syntax of CONCATENATE is simple and has only one required argument.

The subsequent arguments are optional, hence the [ square] brackets.

=CONCATENATE(text1, [text2], …)

So, the first part of the formula will join the first three letters of the City contained in cells in column B. How do we get those letters?.

PART 2. We can use the LEFT Function.

Here is a quick recap of LEFT and it’s syntax.

The LEFT Function in Excel extracts a given number of characters from the left side of a supplied text string.

The syntax for this formula is

=LEFT(text, [num_chars])
Where

text is the string you want to extract from (that is the cell reference that holds your text string)

num_chars is the number of letters you want to extract from the full string. Note the [ ] brackets. This means that this is an optional argument, which if left out default the argument to 1.

So, in our formula, we want to take the first three (3) letters of the City Names in Column B with the header ‘City’. That is straightforward. we already have the first part of our formula, and the LEFT function becomes the first argument as below

=CONCATENATE(LEFT(B3,3),

PART 3. Add a ‘dash’ (-) to the Unique ID.

We can easily do this by inserting another argument in the CONCATENATE function.

=CONCATENATE(LEFT(B3,3),”-“

PART 4. Generate A Unique Count Of City

We now need to generate a unique last number for the end of the ID. So the fourth argument we use in the CONCATENATE formula is COUNTIF. We will use this to count how many times incrementally the City names in Column B appear. Cool huh?. COUNTIF uses a very simple syntax as there are only two arguments.

=COUNTIF(range, criteria)

The two arguments are:-

range – defines one or several cells to count. You put the range in a formula like you usually do in Excel.

criteria – defines the condition that tells the function which cells to count. This argument can be a number, text string, cell reference or expression.

So, in this example, we want Excel to count as it travels down the column of data how many times each city appears in the data. For example the first time the City London appears we want Excel to place 1 in our cell. The second time it appears we it to count and put 2 in the cell and so on and so forth. How do we do this?. Easy. We make the criteria that Excel uses dynamic.

To complete the formula for our first cell, we use the formula

=CONCATENATE(LEFT(B3,3),”-“,COUNTIF($B$3:B3,B3))

Where the range of the COUNTIF formula is $B$3:B3,B3. By anchoring the first cell of the range, the range which Excel analyses is expanded as we drag the formula down the column of cells. So, the first time Excel looks for LONDON the range of cells it is referencing is just the one, hence it generates 1. By the time it finds the second entry of LONDON the range it is looking in has been expanded to $B$3:B7,B7, and so on and so forth.

You can see this demonstrated clearly in the screencast below. The green arrows indicate the expanding range of cells.

generate a unique ID in Excel — An Expanding Range In Excel Formula

So, now we have the unique ID for each customer record by combining CONCATENATE, LEFT and COUNTIF Functions. A nice solution don’t you think?.

Formula Friday- Don’t Update My Calculations…..Just Yet. Using Manual Mode In Excel

February 3, 2023 by Barbara

Have you ever wished there was an easier way to work with formulas in Excel? Well, there is. With Excel manual mode, you can control when formulas are recalculated, making it much easier to work with complex spreadsheets. In this post, we’ll show you how to use manual mode in Excel. Stay tuned!

By default, Excel updates any changes you make to a workbook automatically. Results of updates are seen immediately. So that’s cool Excel will always be up to date. But, if you have a very large workbook with a significant number of formulas that keep updating, or a slow computer ( we have all had them haven’t we) then it may save time to actualy postpone any updates until you decide when Excel updates it’s calculations

What is manual mode in Excel and how do you access it?

Manual mode in Excel is one of the calculation modes available for users. This calculation mode allows users to choose when or if calculations are done within the workbook, rather than having formulas automatically updated on change. To access manual calculation mode, simply navigate to the Formulas tab and click on calculation options. Once this is done, you can select ‘Manual’ from the list of calculation modes, meaning that no calculations will take place until you press the calculation button. Manual calculation mode is great for those who need their formulas to remain unchanged while they are forming a certain model or report steps.

Once you turn of automatic calculations you can instruct Excel to refresh by hitting the F9 key.

Add The Calculation Mode To Quick Access Toolbar

Sometimes I have had Excel users not realising that their Workbook is set to calculate manually. This can happen if they have not realise they have changed this or more than likely it is because they have opened an Excel file prior to their current one that is is manual calculation mode. Excel will apply the same calculation mode to all of your open workbooks. So if they have opened a manual calcuating workbook earlier then any subsequent ones will be in that mode.

A quick and easy way to check as well as turn on and off the calculation mode is to add it to the Quick Access Toolbar (QAT) so it is esily seen.

Right click the QAT
Select More Commands
Choose Commands Not In The Ribbon

Add Manual and Automatic

Your calculation methods is now easily seen in the QAT.

By now you should have a good understanding of how to use manual mode in Excel. If you found this information helpful, be sure to sign up for my newsletter and YouTube channel where I share even more tips and tricks for using Microsoft Excel. Thanks for reading!

Formula Friday – Resize The Excel Formula Bar.

Why Resize the Formula Bar?

Does This Work In All Versions Of Excel?.

Resizing the Formula Bar. Method 1.

Resizing the Formula Bar. Method 2.

Want To Use The Keyboard Shortcut To Resize The Formula Bar?

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.

Formula Friday – 3 Ways To Protect Your Excel Formulas