Home » Tutorials

Excel Formatting Trick To Merge without Merging.

by

Hello Excellers, and welcome back to another #Excel blog post in my 2021 series. Today I will show you how to achieve the same effect in Excel, without actually merging cells in Excel. Confused?. Don’t be. Do you use merged cells in your Excel worksheets?. Have you come across issues when you try to write formulas such as XLOOKUPS and VLOOKUPS. Well, those merged cells are probably causing the issues. We can use Center Across Selection as a great alternative. This method has the same visual effect, but you can ‘merge’ cells without losing any data.

Excel Macro

So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook, 30 Excel Tips. Just click the link to get immediate access to my download. Keep Excelling.

Some while users love merging cells, some don’t. I admit I am the latter, in fact, I do not like them with a passion. They can be useful sometimes for example in a template or two, to make them look and flow better, but when it comes to functionality I just do not use them.

Problems With Merged Cells.

The risk I find with merging cells are

  • Losing the ability to sort your data.
  • Affecting the ability to copy and paste.
  • You lose the ability to run VBA programming code as it just does not handle the merged well at all. Usually, more code is needed to deal with the issue.

Looks Like Merged Cells.

But, the can add to the look and feel of a spreadsheet solution. So, here is a great alternative. We do not need to compromise on the merged look. There is an alternative.  Same look and feel with NO effect on functionality.

Normally a merged cell looks like the screen shot below.  The cells B2 to E2 are merged.

Merged Cell Excel

Use Center Across Selection.

So, to get the same effect all we need to do is firstly UNMERGE the cells.

  • Select the cells.
  • Home Tab | Alignment Group.
  • Unmerge cells.
Merge cell Excel

You now have normal cell formatting.

  • Select the cells that you want to appear ‘merged’.  I will select B2 to E2 again.
  • Home Tab | Alignment Group | Expand Alignment  Settings.
  • Select the Alignment Tab | Text Alignment.
  • Horizontal | Center Across Selection.
  • Hit ok.
merged cell centre across selection

Now, see that the text ‘appears’ to be centred.  You can still click into the individual cells, however.  This means we retain all of the worksheet functionality.  Result!.

centre across selection Excel merge cell

How To Excel At Excel – Formula Friday Blog Posts.

Table of contents

How To Prevent Users Printing An Excel Workbook

by

Welcome back Excellers to another #MacroMonday in my Excel 2020 series. Today, I will show you how to prevent users from printing an Excel workbook. This blog post is in response to a request for this Excel solution from a subscriber. The solution to this was to write a very basic and simple Excel VBA Macro. I thought it would be a useful share with you all. Don’t get scared off, its a little Visual Basic (VB) for Excel. It is straightforward. Why not give it a go?.

The request was to prevent users from printing a certain Excel workbook. What a good idea.  You may not always want your workbooks to be printable especially if they contain some sensitive information that you would not want to be left around in the office or in someone else’s office!.

So, the easiest way to do this is to create a Macro to cancel the print job before it even starts.

Where Is The VBA Code Stored?.

This VBA code is saved in the workbook that you want to prevent users from printing.

Unlike normal VBA macros and functions, event-triggered Macros need to be stored in a Worksheet Object Module or the Workbook Object Module.

A lot of Excel Macros can be used over and over again. These Macros can be store in your Personal Macro Workbook. This will allow you to access these Macros every time you open Excel. very handy. But in this case, we are working just with this one Excel worksheet. It, therefore, makes sense to store the code in the workbook.

Starting The VBA Macro.

First, you will need to open the Visual Basic Editor.  There are two ways to do this.

  • Either by hitting ALT +F11 or
  • Selecting the Developer Tab | Code Group | Visual Basic.  Both methods have the same result.

If you cannot see the Developer Tab, then you may need to enable it. Feel free to watch my YouTube video on enabling the Developer Tab.

  • You will now see the This Workbook code module- double click to open it
  • The code will be written into This Workbook code module.

Using The Before Print Event.

The event that will trigger our code or macro to run is BeforePrint. This is exactly what is it. Before Printing!. Notice that once we enter the first line of code, Excel will automatically insert End Sub for us. The first part of the

This first line of code will literally change the status of the Cancel button to true to cancel user printing of the workbook. Cool huh?.

[stextbox id=’info’]
Private Sub Workbook_BeforePrint(Canel As Boolean)
End Sub
[/stextbox]

Using The Message Box.

The second part of the code is the Message Box. This will notify the user that they cannot print the workbook.

[stextbox id=’info’]
MsgBox “You can’t print this worksheet”, vbOKOnly, “Error”
[/stextbox]

If a user does try to print the workbook then the macro will automatically cancel it and display a message that says “You can’t print this workbook” A bit like this below.

So, the addition of the message box is helpful to keep the user informed that printing the Excel workbook is not an option.

Ending The Macro.

Finally, the code ends once the message box has been displayed with the “End Sub" piece of code. This was already entered into the module when the name of the macro was set.

prevent users printing an Excel workbook VBA Macro

Full Code To Stop A Workbook Being Printed.

Here is he full codes to stop user printing a An Excel workbook.

[stextbox id=’info’]
Private Sub Workbook_BeforePrint(Canel As Boolean)
MsgBox “You can’t print this worksheet”, vbOKOnly, “Error”
End Sub
[/stextbox]

More Excel Tips.

How To Close All Excel Workbooks At Once.

What Are the Three Visual Basic Editor For?

Highlight Cells With Data Validation.

Read All Of My Macro Monday Blog Posts.

Turn Of The Double Click Drill Down In Your Pivot Table.

by

Hello, Excellers welcome back to another blog post with an Excel Pivot Table Tip. In today’s blog post let’stake a look at how to turn OFF the option of a user Double Clicking in your Excel Pivot Table to drill down into the data. 

Drill Down Into A Pivot Table.

By double-clicking on the data in the Values Area of your Pivot Table you can extract the underlying records These are the records that make up that data.  See the screencast below for an example.  (You can also Rick- Click and hit Show Details – whatever method seems more comfortable to you).

The result of the Double – Click or Show Details will result in a new Excel Worksheet being inserted into your workbook. The workbook will contain the details of the data you drilled into.  In this example, it is the sales values of the North Region in January 2019.

Prevent double click drill down into an Excel Pivot Table


However, you may want a user NOT to be able to accidentally or otherwise drill down into the data. There is a small setting we can change to prevent this.

  • Select your Pivot Table
  • Go to the Analyze tab in the ribbon
  • Press the Options button in the Pivot Table section to open the options menu
  • Go to the Data tab and un-check the Enable show details box to disable this feature
Prevent Double Click Drill Down into A Pivot Table In Excel.

Now, this is NOT an ultra-secure or really effective way of preventing data drill down if the user knows what they are doing, but it can help to prevent generating extra worksheets in error. 

If you now try to Double Click or Rick Click to Show Details then you will simply get the warning below.

Recommended Pivot Table Course.

If you want to find out more about Pivot Tables and looking for a course. This is my personal recommendation from my training resources.

Want To Watch The YouTube Video?.

Download The Example File.

Icon

Prevent Drill Down On A Pivot Table

1 file(s) 44.03 KB
Download

IF Statement For Last Day Of The Month.

by

Hello, Excellers and welcome back to another #FormulaFriday #Excel blog post in my 2020 Excel series.  Today I want to answer a question that a subscriber sent to me.  If you want to ask any questions or comment feel free to leave a comment below.  The question was, how to use the IF Function in Excel to identify if a cell contained the last day of the month. 

So, why did they want to know this?.  Well, on the last day of the month, They Wanted to automatically insert 100 Euros into a budget column as their allowance.   This is represented as by a very basic version of the Budget Tracker below in my screengrab.

You can see that on the last day of the month 100 Euros is set to automatically be inserted into the Payment In Allowance Column in my Excel worksheet.

So, I suggested that we use the EOMONTH Function along with the IF Function. The logic behind this is to test the Date in Column C. If the date in Column B is the last day of the month, then 100 Euros is entered into the cell in Column C. If the date is not the end of the month then nothing is entered. Let’s write the formula!.

EOMONTH Function.

Let’s take the EOMONTH Function first and look at the syntax.

=EOMONTH(start_date, months) this function returns the serial number of the last day of the month before or after a specific number of months.

where

start_date – this is a serial date number that is your start date

months – this is the number of months before or after the start date

If we type this into our example in Column C, the start date is B4 and the month argument I have set at 0 which indicates which represents the current month of the start date in B4. The result returned is the last date of the month.

Now I have that part of the formula I can now build my logic into it with the If Function. Sounds good right?.

The IF Function.

Let’s take a quick recap of the IF Function which checks whether a condition is met. It will return one value if the condition is TRUE and another if it is false. The syntax is as follows:-

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

Where

logical_test – the value or logical expression to be evaluated as TRUE or FALSE

value_if_true – the value returned if the logical test is TRUE

value_if_false – the value returned if the logical test is FALSE

Using EOMONTH With The IF Function.

So, this sounds like we can use this along with our EOMONTH and test if the date in Column B is the last day of the month in Excel. I already have the EOMONTH Function in my cell. I just need to wrap the IF Function around it.

the last day of the month in excel IF statement with EOMONTH

The result of the function maybe looks a bit weird. We just need to reformat the cell as Currency.

I can then drag the formula down the column. You can see that Excel correctly inserts 100 Euros in the correct cell relating to the last day of the month. This function will work over and over again as more dates are added to the data set.

The Last Day Of The Month In Excel.

Click on the link below to download the sample file used in this blog post.

Icon

If Function - End Of The Month

1 file(s) 11.06 KB
Download

Excel Tip. Automatically Add New Items To Drop Down Lists

by

Hi Excellers, welcome back to another #Excel tip. Today I want to show you how to set up a data drop-down list that is automatically updated when we add new items or options to the list.  Dropdown or data validation lists are really useful when you want to control data entry into your Excel workbook.  By giving the user limited options, you control the quality of the data that the user enters.


You can follow these simple steps to make your drop-down lists dynamic, so they update automatically when you add new items to our source data.  Here is my example.  I have a list of activities that a user can select as part of the data entry process.

Automatically add data to a drop down list in Excel

Step 1.  Create A Data Table

  • Enter your options into Excel.
  • Convert the list to a data table.  Select your data then Hit Ctrl+T.  Confirm if your data table has header and hit Ok.

Step 2.  Create A Named Range.

  • Formula Tab | Name Manager | New.
  • Name your range of cells. In this example it is rngProducts.
  • In the Refers to field select the range you want to include (your data table).
  • Hit Ok and close Name Manager.

Step 3. Set The Range As A Data Source For Validation

  • Select the cells to apply Conditional Formatting to using Data Tab | Data Tools | Data Validation.
  • Settings | Allow| List
  • In Source type the named of the created Named Range.  In this case rngProducts
  • Tick Apply these changes to all other cells with the same settings
Set Data Range As Source For A Drop Down List

Now it is the time the test our solution.  Let’s add another product into our data table and see it appear as a new option in our data validation list.  I will add Top Hat to the list.

It works!. How cool is that?. I can now add new items to my list and it’s automatically updated.

Finally, do you have you an Excel question?. So, why not leave it in the comments below.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

 

th

Learn Excel Dashboard Course

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar