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

Enter Data Into Excel In A Specific Order

March 17, 2023 by Barbara

Entering data into Microsoft Excel can be time-consuming for users. Especially if the cells that require data are spread all over the excel worksheet. The last thing you want is a user to the tabbing or clicking around the worksheet frantically looking for the correct cells to enter data. In particular if you need the user entering data in a specific order.

Excel CAN force the sequence of cells that you want users to select. So how does that work?. This solution relies on defining a named range which essentially is a sequence of cells that you specify. for more information on named ranges, I suggest reading my dedicated articles here

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

Write A Macro Which Highlights Named Ranges. Macro Monday.

See below as an example of the sequence of cells that I want users to type into Excel. Pretty erratic isn’t it?.

So let’s give this a go.

Creating A Named Range

Click in the cell that is second in your required sequence
Hold down the CTRL key and click the cell sequence 3, carry on and select the cells in the correct order
When you have reached your last cell in the sequence, click the first cell in the sequence
Click the cursor into the Name Box (the area to the left of the bar that shows the cells addresses)
Name this area of cells, i.e a named range in this example I am using MyDataSequence – then hit Enter and the name box will return to showing a normal cell reference
Save your file

When you need to get a user to enter data into Excel in that order just select your named range MyDataSequence. Begin typing and press enter. Excel jumps to the next cell sequence ready for data entry

To view the details of the named range you created follow the steps below.

Formula Tab
Defined Names
Name Manager

So, as you can see the named range is just a sequence of cells, that we selected earlier in the exercise. Give it a go.

Formula Friday Using The ROUNDDOWN Function in Excel

March 10, 2023 by Barbara

Welcome, Excellers, to another Excel Formula tip in my #FormulaFriday series. Today let’s look at rounding numbers down in Excel using the ROUNDDOWN function. I want to give a quick disclaimer here. Once you round numbers in Excel (either up or down), you will lose precision in your calculations.

So, if precision is a concern in your calculations, I recommend reducing the number of decimal places in your cells by using the formatting options in Excel.

To make it clear, the ROUNDDOWN function in Excel will CHANGE your data and affect the results of your calculations.

The ROUNDDOWN Function Syntax.

Here is a refresher of the ROUNDDOWN function syntax.

=ROUNDOWN(number,num_digits)

Number – this refers to the value to be rounded. This actual data for rounding can be a cell reference to the location of the data in the worksheet.

Num_digits – the number of decimal places to reduce the above number to.

In the rules for rounding numbers that Excel follows, the number to the right of the rounding digit determines whether the rounding digit is rounded up or down:

If the value of the number to the right of the rounding digit is less than five, the rounding digit is left unchanged.
If the value of the number to the right of the rounding digit is five or higher, the rounding digit is raised by one.

The ROUNDDOWN function however, always leaves the rounding digit unchanged regardless of the value of the number to its right.

An Excel ROUNDDOWN Example.

Click on cell D2 to make it the active cell – this is where the results of the Excel ROUNDDOWN function will be displayed.

Formulas tab
Choose Math & Trig from the ribbon to open the function drop down list.
Click on ROUNDDOWN in the list to bring up the dialog box
In the dialog box, click on the Number line.
Click on cell D1 in the worksheet to enter that cell reference into the dialog box.
Click on the Num_digits line.
Type a one ” 1 “ to reduce the value in D1 to one decimal place.
Click OK.
The answer 27.3 should appear in cell D2.
When you click on cell D2 the complete function = ROUNDDOWN (D1,1) appears in the formula bar above the worksheet

That is it, your number is rounded down. Remember I said that Excel ROUNDOWN could affected your calculations? Let’s have a look at how this works.

You can see our original number in cell D1, and our rounded down number in D2. Both of the numbers then multiplied by 10. The original number result is significantly different. 353.97 in our original number whereas when we ROUNDOWN the number this is reduced to 353.00.

Do you want to see even more Excel tips? Why not bookmark my Formula Friday and Macro Monday Pages.

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.

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 more tips then sign up to my monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

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.

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

1.Hide The Formulas.

2. Lock Cells.

3. Hide The Formula Bar With Some Simple VBA

Creating A Named Range

The ROUNDDOWN Function Syntax.

An Excel ROUNDDOWN Example.

Lock The Excel Cells.

The Syntax Of The CELL Function.

Set The Conditional Formatting To Highlight Locked Cells.

How To Excel At Excel – Formula Friday Blog Posts.

Tags

Recommended Excel Resources