How to Hide all Formulas in Excel in Just 2 Steps

Hide all Formulas in Excel

Sometime you want to share your excel files but you don’t want to share formulas in that excel files. You want to hide particular or all formulas in Excel.

You can hide all formulas in sheet easily. There are two main steps to hide formulas in Excel.

Lets Start –

By Default protection of all cells are Locked and not hidden. But this only work when sheet is protected. Lets understand how it matters.

First Step to hide formula is to change Format of those cells which contain formulas.

To Change Format select all cells in sheet with Ctrl + A and Go to format cell with Right Click. Untick Locked option

Then to hide cells formula and locked cells which contain formula we have to change protection of those particular cells.

To select Formula Cells click on Special option from go to with F5 key or Ctrl + G.

Select Formulas from Go To Special and click on OK. You can see only cells which contains formula are selected.

Now Right Click and Choose option Format Cell. And from Protection Tab tick on both options Locked and Hidden.

 

Now Second and Last step is  to protect Sheet. To protect sheet go to Review Tab and Click on Protect Sheet.

You can set password for sheet and also change rules as per your requirement. This will apply only for cells which contain formulas. Then click on Ok.

Now you can see formulas is not shown also when you try to edit formula then excel will display below Warning.

Hope you understand how to hide all Formulas in Excel

Thank You.

Watch Video -

Have a nice Day...Enjoy Exceling..

How to sort and filter embed objects in Excel

In this tutorial you learn how to sort and filter embed objects in Excel.

Hope you know how to insert object in excel file. Click on below link to know how to insert objects in excel file.

https://goo.gl/AuzNMQ

To Sort or Filter embedded object in Excel first you have to change format of Object.

Select one object and then press Ctrl + A to select all objects. If this is not worked then press F5 key or Ctrl + G and go to Special and select last Objects option to select all objects from sheet.

Then right click and select Format Object Option.

In Format Object window go to properties tab and select option Move and size with cells and hit Ok button.

Now you can see objects also filtered with numbers.

And to sort data select all Data including attachment column (Column B) and sort data as you want. Objects will be sorted with numbers.

 

Note - Make sure files are embed properly into the cells otherwise sorting won't work.

Hope you understand. Thank you.

Watch Video - 

 

Have a nice day... Enjoy Exceling.... 

Hide cell value only without hiding Row and column - quick tips

Hide cell value only without hiding Row and column

Sometimes you want to hide cell value only and not any row or any column. But that hidden value can be used in any formula without any issue.

To hide only cell values first select that particular cell.

Then right click on it and select option Custom from Format Cells

Then in Type field enter three times semicolon (;;;) same as highlighted in below screenshot and hit Ok.

Now you can see cell value is hidden but it is not affected formula and also note it is display only in Insert Formula Bar.

Hope you understand as it is very easy

Watch video

Thank you…Enjoy Excelling

Fill color in every alternate row in Excel - Shortcut

In this tutorial I’ll show you how to fill color in every alternate row in Excel or how to highlight every alternate row in Excel.

If you have small data and you want highlight or fill color in every alternate row then it take less time to do manually. But what if you have more than hundreds or thousands of row then it will be very time consuming. Thus in this post I’ll show you how to highlight every alternate row easily and that too without consuming much time.

Lets Start -

We are going to use Conditional formatting Rule to highlight alternate Row.

First select all Data then go to Conditional Formatting and select New Rule.

We have to create new Formatting rule. Select a last Rule Type “Use a formula to determine which cells to format” and in Edit the Rule Description field type formula “=mod(row(),2)”.

After typing formula click on Format button and select format which you want. Here I have select Bold Font and Fill Orange Color in Format Cells window.

Then click on Ok button. Now you can see all alternate row change in format you choose.

Hope you understand how to Fill color in every alternate row in Excel.

If you have any issue then feel free to comment on Post.

Have a nice day…. Enjoy Excelling …..

Restrict duplicate entries while entering new values in Excel

Sometime you don’t want duplicate entries in your list which you prepare in Excel. There is easy fix to restrict duplicate entries while entering new values. Just follow up below process and you will see error occurred when you try to enter duplicate values in range.

Lets Start -

First Go to Data tab and select first option Data Validation from Data validation menu.

Then below Window will open.

In Allow option select dropdown and choose Custom and enter CountIf formula in Formula Field.

Countif formula should be like as per above. It should include Range where you want to enter data with fixed Reference by putting $ sign in Cell Reference. Shortcut for lock Range is select Range and press F4 Key. Then also put “ =1 ” to enter value in Range only once.

Then click Ok.

After that Drag that Cell till Last Cell in Range. Here I have dragged D5 till D20.

Now you can see below error when you try to enter same value again in Range.

Note – Data Validation will not work if you copy paste value from other location.

You can customize above error message. To customize your own error message go to Data Validation and select Error Alert tab.

Enter Title and Error message what you want to display while entering duplicate values.

Hope you understand how to restrict duplicate entries in Range.

Thank you

Watch Video -

Have a nice day….Enjoy Excelling…

Send whole email from Excel without using Macros

In this post you will learn how to send whole email from Excel without using Macros.

You may know you can send email from excel with using VBA Macros but it is not so easy to understand everyone. Today I’ll show you how to send email from Excel without using Macros. We can send whole email from Excel by using just formula.

Lets Start –

Before starting tutorial make sure you should configure outlook in PC otherwise it won’t work.

I have written subject, CC and email body in column A and B as per below.

To send this email particular person we want link to click and auto create mail in Outlook. To Generate link, we are using HYPERLINK formula.

Now enter Name and Email List in Column A and B below or side of above Mail.

From above data and HYPERLINK formula we are going to generate link which help to create email directly in Outlook.

Formula is looking too big but it is very easy to understand.

Lets Understand Formula and Criteria –

 Hyperlink is required Two Criteria

1.       Link Location – Our Link location is as per below -

mailto – Select Cell where you have person’s Email ID. Here - B11

CC – If you have CC in your mail then select that cells. Here – B2

Subject – Select cell where subject appear. Here B3

Body – Body may contains multiple and blank lines so we are using “%OA” for next line and blank cell for blank lines

               

2.       Friendly Name – You can use any name for above Link which will appear in cell where you enter formula with link.

Make sure you have lock all the cells which use in CC, subject and Body as we are not changing any field. We are only change receiver email id.

Now you get link with Friendly Name. Here send e-mail.

Just Click on Link and you can see Excel will redirect to Outlook with whole email.

Now you are ready to send email from Excel without using Macros.

Note – As Hyperlink only support up to 256 characters so you won’t be able to large mails.

To Download above Template click on Link below –

Download Template 

Hope you understand how to Send whole email from Excel without using Macros 

If you have any issue then feel free to comments on Post.

Thank you

Watch Video - 

 Have a nice Day…. Enjoy Excelling……

Excel Copy Paste Multiple Values only in Blank cells - Shortcut

Excel Copy Paste Multiple Values only in Blank cells

If you have list with blank cells and you want to paste some data in blank cells only without changing other cells then follow below process.

Below I have written few numbers with blank cells and I want to paste remaining numbers in blank cells.

I have also remaining number in other column like below.

If we copy above range and paste into first image then it will give us same data display in 2^nd image. But we don’t want same data. We want to copy above data and paste it into blank cells only.

To copy and paste into blank cells only follow below process. It is very easy.

First Copy the Range you want to paste it. Here Range in column J. Then come to column D1 and Right click and select option Paste Special.

In Paste Special – Select Option Values and check mark on Skip blanks. Then press OK. Now you can see numbers only paste in blank cells an all other cells remain same as per below.

Hope you understand. If you have any issue then feel free to comment.

Thank you

Watch Video -  

Have a nice Day…Enjoy Exceling….

Excel Move Data from Column to Row and Vice-versa - Easy Trick

Excel Move Data from Column to Row and Vice-versa 

If you have data in horizontal format and you want to move the same in vertical format or vice-versa, in other words you want to move data from column to row or vice-versa then follow below simple steps.

It is very easy to follow below steps rather than enter them manually.

In above image you can see date is in horizontal format in column but we want to transfer them in vertical format in Row then copy data and select any cell where you want data.

Right click and select option Paste special then tick on transpose option. After Tick on transpose option hit on OK.

Now you can see all values are transfer to vertical format in Rows.

Hope you understand how to Excel Move Data from Column to Row and Vice-versa.

Thank you.

Watch Video -

Have a nice Day… Enjoy Excelling…