View All Formulas in Excel with a Single Click

When someone sends you an Excel file, you may want to find out where the formulas are, and where are the values.

But with everything showing as a number, it is difficult… and you need to move from cell to cell, to find out if it is indeed a number, or a calculation based on a formula.

Fortunately, there is a simple way to find out where the formulas are, and see them all, at a glance.

Simply press the Control Key and hold it. Then press the ` symbol. On most keyboards, it is just above the Tab Key on the Left of the keyboard.

Tilde Key to View Formulas in Excel

This is a toggle shortcut. Each time you press the keystroke., it will turn itself on or off. So press CTRL + ` and you will see all the formulas, press the same keystroke one more time, and you are back to the values.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Give it a try… it is really fun, and cool too!

You may find these resources useful:

Cheers,
Vinai Prakash – Providing you simple tips and tricks in Excel

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Additional Resources for Learning Excel & Become A Pro

  1. Top 12 Most Important Excel Functions You Absolutely Must Know
  2. Excel Beginners Guide
  3. Excel Essentials: From Beginner To Pro in Just 6 Weeks

Counting Text Columns like Employee Names in Excel

Excel is great and summing things up. And then count the number of rows easily. You can watch the step by step video on how to use simple functions in Excel.

In fact, most versions of Excel can even show the SUM, COUNT of a block of cells, in the bottom status bar automatically.

However, there is an issue, if you want to count number of employees, or number of departments, or number of students…

Text values, by default can not be summed by the SUM function, and can not be COUNTED by the Count function in Excel

That’s because Excel is good at adding and counting numbers, and not Text. If you try, you’ll get a ZERO.

=COUNT(Employee_Names) will result in a ZERO answer.

Fortunately, the workaround is pretty simple, straight forward and easy to use in Microsoft Excel. And it counts text values with ease.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Instead of using the COUNT function, you can use the COUNTA function of Excel. COUNTA is to count Textual data.

So if you have characters, alphabets and number combinations (like employee numbers),  you can count such text items using the COUNTA function of Excel.

This COUNTA Function works in all the versions of Excel – from Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, and even Microsoft Office Excel 365 (the cloud version.)

So to count the number of employees, you only need to say =COUNTA(A5:A15).

And this nifty function will count the number of employees promptly. COUNTA is to count Alphanumeric values – text, strings… or even numbers.

Try it out, and let me know if this helps you… 🙂

You may find these resources useful:

Cheers,
Vinai Prakash
Founder of ExcelChamp.Net

 P.S. – Are you an Expert in using Excel ? Try solving this – find a way to find duplicates in Excel 2010 quickly. And post your solution. We are looking for simple and innovative ways to do so…

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Adding Line Breaks in a CSV File

A CSV file is a Comma Separated Values file. And it can be opened very easily in Microsoft Excel.

Most people use data files coming from main frame computers, or other external applications, and export the data in the CSV format, as it is easy to create, access and use. The other alternative is to create a Excel file (ending with .xls or .xlsx extension), but it is quite difficult to achieve in most legacy applications.

Thus, the CSV format has come to stick, and is one of the most popular methods to transfer data from one application to another.

Microsoft Excel has the capability to read and create CSV files easily.

In Excel, you can write text in a any worksheet cell, and it can span into many lines. Sometimes, if you have a long comment, you can split it into different row lines also.

a long comment in excel, keyed in multiple lines, but in the same single cell

 

 

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


How do you create a CSV file, which contains a long line, split into multiple rows?

Simply use  Quotation marks to enclose the data which spills into multiple lines. see below:

Department, Status
Manufacturing,“No new issues.
Previous issues need to
be addressed”
Sales, “No issues”.

Once you load such a file into Excel, it will automatically show the complete data, in a single cell.

That’s it… it is such a simple and neat trick.. and helped me numerous times..

Additional Resources:

Enjoy,

Vinai Prakash

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

How to Analyze Data using Excel Pivot Tables

Do you know how easy it is to use the Pivot Tables in Excel, for Data Analysis?

To demonstrate this, I went to the Internet, and  found some travel data. It belongs to an unnamed Travel Company. The travel company collects the amount of ticket sales, the age of the passenger, and their gender only.

Based on this, how do we analyze the data, and find some hidden gems of information that can make the data come alive, and aid us in doing some real life Marketing.

Watch this step by step video on analyzing this data, using the Pivot Table technique in Microsoft Excel. I am using Excel 2010 version for this demonstration.

As you can see, many hidden insights can be found by using the Pivot Tables in Excel. So get started… Use them in your day to day work, and become a Data Analyst, rather than a Data Gatherer or Data Reporter…

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Have you subscribed to the ExcelChamp.Net Email Newsletter, where we send you such tips by email?

Additional Resources

Cheers,
Vinai Prakash, PMP, ITIL, MBA, GAP
Founder: ExcelChamp.Net – Providing Everyday Tips for Improving Productivity, Using Excel!

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

<div class=”ml-form-embed”
data-account=”3112195:l6l5i4i1x0″
data-form=”5497493:o9q9a8″>
</div>

Additional Resources for Learning Excel & Become A Pro

  1. Top 12 Most Important Excel Functions You Absolutely Must Know
  2. Excel Beginners Guide
  3. Excel Essentials: From Beginner To Pro in Just 6 Weeks

Removing Grid lines from a Section in Microsoft Excel

You can switch off Gridlines completely, or you can have them on. But many people are amazed to learn that you can turn them off or on from a certain section of their Excel spreadsheet. And this trick works in Microsoft Excel 2003, Excel 2007 as well as Excel 2010.

How do you achieve this… Very simple trick that doesn’t take a minute to do… Watch this step-by-step video on how to remove gridlines from a section of the Excel Sheet.

Let me know if this works for you…

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Do you have any other problem in using Microsoft Excel? Simply write to me using the comments box below, and I will assist you.

And check out my other Microsoft Excel Tips & Tricks videos on YouTube.

Additional Resources:

Cheers,
Vinai

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!