Text Functions

Top 10 Time Saving Tips in Using Microsoft Excel

by

If you are a data warrior, chances are that you use Excel extensively in your day to day work. Learn to Analyze Data Quickly With Excel.

And there are many ways to boost productivity within Excel. Use these nifty tricks and improve productivity.

1. View All Formulas in Excel With a Single Click: I wrote about this trick some time back. It allows you to view all the formula with a single click of Control + ` Key. This acts like a toggle key. This can save a lot of time in finding where are the formulas to edit/view them.

2. Clean up Your Data  – Identify the Duplicates, and Remove Duplicates: There is no point in having duplicates in your tables. There are multiple ways to do this. You can identify the duplicates, and then choose to remove them manually,  or you can request Excel to remove the duplicate rows completely, automatically.

3. Colour Alternate Rows and Make it Easier to Read Large Data Sets: Prior to Excel 2007, we used to write formulas to colour alternate rows in Excel. This makes it easier to read the data.

=MOD(ROW(),2)=0

This function would tell us if the row is an even row or an odd row, and then we could colour it.

But since Excel 2007, we can do this in multiple ways. We can convert the data into a table. And in the table options, you can then colour alternate rows or columns as bands.

And then you can also use the formula above, and use conditional formatting.

4. Learn The In-Built Functions in Excel: Many people are amazed at the plethora of in-built functions available in Excel. But you must know that they exist, and also know the syntax to make a good use of them.

5. Learn and Use Pivot Tables Effectively: Pivot tables summarize data quickly. Learn to use them effectively.  Simple techniques like displaying both values and percentages in a Pivot Table adds extra value.

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!


Similarly, learn to show different grouping for different pivot tables, even though it is based on the same data source. Also, creating a calculated field in a pivot table adds extra value.

If you are new to pivot tables, this introduction on how to analyze data using Excel pivots will be useful.

6. Charting Techniques Can Make People Notice Your Work: Creating nifty charts like the 2 Axis chart, or the PivotChart can be useful in the board room. Learn these techniques. Similarly, using Slicers in Pivot Charts can make dynamic Charts that can wow the audience. They are simple to use.

7. Get Back to Your Selection: Many times you will select or highlight some cells, but then move about the Excel file to find something. Then your are lost. Now you don’t remember where exactly is your selection. Simply click Control + Backspace. It will take you to the highlighted area in a blink!

8. Use Range Names to Refer to Cells: Instead of using G3, C3 or F3, you can give each cell a name. Not only single cells, you can actually define a name for a group of cells too. Then you can refer this group of cells by a particular name.

So instead of saying =G3+C3-F3, you can now calculate Salary as

=Basic_Pay + Allowance – Tax

This is much easier to read, and comprehend. No need to look up where the formulas are coming from, as the names are self evident.

9. Learn Excel Shortcuts: There are hundreds of shortcut combinations in Excel. It is impossible to remember all of them. But you must know a few to really speed up your work. Some of my favorites are:

  • Control N: New workbook
  • Control `: View All Formulas
  • Control Backspace: Jump to selected Cells
  • Alt + F1: Creates a inserts a chart based on the currently selected data as an embedded chart object.
  • Alt + =: Auto sum formula is inserted
  • Ctrl + Shift + Enter: Enter an Array Formula
  • F9: Calculates all the worksheets in all open workbooks

10. Learn to Use Dates Effectively: In Excel, dates are stored as numbers. They can be displayed in a wide variety of Formats. Understand these formats to display them in the way that suits the applications.

Also, you can calculate using dates, for example, you can find the difference between any two dates. You can add a fixed number of days to a date.

Hope you enjoyed these tips, and will use them to improve your productivity in using Excel. Do post a comment below to talk about your favorite tips in Excel.

If you wish, you can get a good book on Excel from the Excel BookStore. These books are usually quite inexpensive, and will give you many ideas to improve too.

Cheers,
Vinai Prakash, PMP, MBA, ITIL, GAP, Six Sigma

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!

P.S.  : You can contact us to conduct a workshop on Excel Tips and Tricks at your office.

And I hope you subscribed to the ExcelChamp Excel Tips Newsletter already. 🙂

How to Replace Blanks With Any Value or Zeros in Excel

by

This is a common problem. You import some text file, and half of it seems blank. The problem is that if it is showing blanks, you can’t sum the values.

To replace each blank with a zero is time consuming, boring and stupid. We want a fast method that works in just a few clicks, and saves us loads of time for our families 🙂

There are many ways to achieve this task of replacing Blank with Zeroes, or Replacing Blanks with Any Other Values in Excel.

Method 1: Use the GoTo Method to Find & Replace

Click on Home > Find & Select > GoTo Special. This will open up a new popup window.

Go To Special Popup in Excel
GoTo Special menu – Select Blanks

Choose Blanks Radio Button, and Click OK.

All the Blank Cells will get highlighted. Do not worry. Simply type a Zero or any other value that you want to put in the blank cells.

Do this in the currently active cell. Press

together.

Voila! The keyed in Values is showing in all the Blank Cells now.

Method 2: Use an IF condition

For example, if you want to selectively key in 0 for blanks, key in this formula below.=IF(A2=””,0,A2)

This formula checks if the cell A2 is blank or not. IF it is blank, it will replace it with a 0, or else it will not change it.

Once done, you can drag the formula to the selected cells. Works beautifully!

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!


Method 3: Variation of the IF Method, using the ISBLANK function

Now we have an in-built function that can check if the cell ISBLANK or not.

=IF(ISBLANK(A2),0,A2)

Same Magic!

If you have any other favorite method, do share it with us below in the comments!

If you’d like to learn more Excel Shortcuts, Tips &  Tricks, here are some additional articles:

Questions About Any Feature in Excel?

If you have any questions regarding any feature in Excel, simply post a comment below and I’ll help you with it. All the best!

And Subscribe to our Weekly Excel Tips Newsletter, so we can send you more Short cuts & some Pivot Table Techniques by email. Only useful stuff, without the hype. And we won’t spam you. So don’t worry. Just subscribe!

Cheers,
Vinai Prakash, PMP, ITIL, GAP, Six Sigma
Founder of ExcelChamp.Net

Vinai conducts 1-2 day seminars and Training Sessions on Microsoft Excel all over the world, helping companies maximize their productivity in using Microsoft Excel. If you would like to find out more, simply contact us.

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!

Do You Use These Features of Microsoft Excel?

by

Most people hardly use the most useful features available in Microsoft Excel. Many are just using Excel as a calculator to do basic calculations like adding or subtracting numbers. This is a gross under use of Excel’s vast potential and feature rich functionality.

Do a quick check, and see if you use these advanced features of Microsoft Excel in your day to day work to improve your productivity and efficiency.

  1. Finding the Top 10 Customers or Finding the Bottom 5 Performers in the organization
  2. Give Names to Range of Cells, and then use them in formulas for easy referencing and decoding – like =Salary – CPF + Allowance
  3. Exploit Pivot Tables to Summarize the data and slice & dice it in any way – like finding sales by product groups, or calculating productivity by department, or calculating sales by department by product by area.
  4. Write Macros to automate routine things that save you a huge amount of time – example creating different pivots, charts, tables, and doing complex calculations, or compiling the cost benefit ration, cash flow statement automatically.
  5. Highlight values that are above or below a certain threshold – like all sales above $25,000 to be highlighted, or all products with a net profit margin of less than 5% be highlighted.
  6. Sort the values in Ascending, Descending or any Customized sorting order – like sorting in order of Manufacturing, Accounts, Sales departments.
  7. Use advanced filtering conditions, and be able to filter data using multiple different criteria – like salary > 50,000 and sales <100,000 or Zone in East, West.
  8. Create fantastic charts that portray the given business situation perfectly. There are over 50 different types of charts to choose from, and each has its edge, advantages and a reason. Do you know when to use which type of chart – like two axis charts, stacked column and line charts, bubble charts, pie, slice of a pie etc.
  9. Create management dashboard that are dynamic, and provide a complete snapshot of the key business KPIs in the company – change the chart values at the click of a checkbox or change in a dropdown value
  10. Use Excel’s advanced What-If analysis to do projections for future, forecasting, trend analysis etc. with ease
  11. Use Lookup tables to find any value or corresponding value from a table using advanced functions and formulas – find values that in the left of the looked up value
  12. Protect certain cells from data entry, and hide the formulas from other cells. Ensure that only certain values are keyed in the cells to avoid data discrepancies.

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!


This is just the tip of the iceberg… These are common things that can be done easily with Excel’s in-built features.

So how many of these did you know? And how many would you like to learn more about? Even if you learn one new trick in Excel, it will save you immense time and improve your productivity. Investing in Microsoft Excel Training bears fruit in no time, and is a no brainer.

Microsoft Excel is really extremely powerful. Each version of Microsoft Excel – be it Excel 2007, or Excel 2010 or Excel 2013, Excel 2016 or Office 365 adds more and more features to the already powerful dynamite of a package.

At ExcelChamp, we teach people how to leverage the maximum power out of Microsoft Excel in short training courses.

So what are you waiting for? If you would like to learn any one or more of such useful features of Microsoft Excel, just let us know. We provide online training through videos, which will show you each click, step by step, so you can master the new techniques easily, with exercises, and practical examples.

Go ahead, equip your team with the right skills. Get everyone on board to learn the basic and advanced features of Microsoft Excel, and Be Awesome in Excel!

Email to us to know more about Online Excel Training at ExcelChamp.Net.

Cheers,
VInai Prakash, PMP, ITIL, GAP, Six Sigma, Excel Guru

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!

Display the File Name & File Path in Excel (PC & Mac)

by

Many a time, it is good to Display the location of the file in Excel.

Not just the file name, but the entire path of the file. It is a good reference to make sure you are working on the correct file and is also good if you print out the Excel. With this option, the file path always gets printed on paper..

How to Write a Formula that will Display the File Path in Excel, automatically. 

One useful formula that can assist us is the CELL formula. I bet you didn’t know about this in-built Excel Function…

The CELL function can help us to display the address, colour, format, type or width of any cell.

The CELL Function takes a maximum of 2 arguments, and only the first one is mandatory.

=CELL(info_type, [reference])

So, if you write =CELL(“filename”) in any cell, you will get the Full Pathname of this Excel file, along with the Sheet Name.

For example, I got the filename displayed as below:

D:\[email-blasting-migration.xlsx]Sheet1

According to the Excel Help, the CELL function  has the following arguments:

  • info_type Required. A text value that specifies what type of cell information you want to return. The following list shows some of the possible values of the info_type argument and the corresponding results.
    info_type Returns
    “address” Reference of the first cell in reference, as text.
    “col” Column number of the cell in reference.
    “color” The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).
    “contents” Value of the upper-left cell in reference; not a formula.
    “filename” Filename (including full path) of the file that contains reference, as text. Returns empty text (“”) if the worksheet that contains reference has not yet been saved.
    “format” Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns “-” at the end of the text value if the cell is formatted in color for negative values. Returns “()” at the end of the text value if the cell is formatted with parentheses for positive or all values.

Display only the Excel File Name, without the Sheet Name

To only print the File name, you can use this long formula:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Keep in mind that the file should be saved somewhere. If you just open a new workbook, and try these formulas, you will get a #VALUE ERROR.  This error will disappear automatically once the file is saved, and the error will be replaced with the correct full file path name, and file name.

This formula to display the filename in Excel also works on the Mac. Any Excel for Mac version, including Excel X for Mac, to Excel 2004, 2008, 2011 for Mac also work fine in displaying the filename.

For example, for a file on Mac with the path HD:Users:local:Desktop, the formula returns
HD:Users:local:Desktop:(filename)Sheet1

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!


Displaying the FilePath only

While writing macros in Excel, you may need the file’s path only, to pick up other files, or for any other reason. Just use this to get only the file’s path in Excel

=LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1),1)-1)

In this formula, we remove the Sheet Name, which starts with the [ character.

How To Display Filename & File Path in Excel Worksheet Header or Footer

If you wish to display the file name and/or file path in the Excel sheet’s header or footer, then it is extremely simple. Simply go to the Ribbon. Choose Insert > Header & Footer

Excel File Name, File Path

All you need to do is click on the File Path, File Name icons.

Alternatively, if you want to type it yourself, simply type the following in the header or the footer in Excel:

&[Path]&[File]

Use &[Path] for only the File Path, and &[File] for only the File’s name.

Hope it helps you in your life with Excel.

Now you can confidently display the filename and the file path in Excel

With the File Name of Excel files visible in the header, with the File Path, you won’t  lose a file in the wrong location & find what you are looking for easily!

Do let me know if you need any other functions in Excel, or if you are facing a problem in Excel. We can help you to solve everyday issues in Excel easily at ExcelChamp.Net.

And subscribe to the Excel Tips Newsletter for more such tips to improve productivity 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!


Cheers,
Vinai Prakash

Vinai is the founder and principal trainer for Microsoft Excel Tips and Techniques seminars all over the world. To book Vinai to conduct an Excel training at your office for your company, do email to vinai@excelchamp.net.

Counting Text Columns like Employee Names in Excel

by

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!