Excel Tips – Technical Forum

How to Convert Individual Sheets in a Workbook Into Separate PDF Files

If you are using Microsoft Excel for work or business, you might find it useful to print the Excel sheets as PDF file for better reporting as the contents of the PDF files cannot be modified easily compared to a raw Excel file. Besides, most computers and mobile devices can read PDF files but cannot read Excel files as MS Excel is a proprietary software. There are basically two (2) ways to produce a PDF file from MS Excel file as described in the succeeding texts. Converting Excel file to… Read More

How to count or calculate “unique” data in Excel (count duplicate data once)

How Tos MS Office Tips Tips & Tricks October 21, 2023

Have you ever faced an Excel situation wherein you need to count or calculate rows and columns of data uniquely? Or in other way of saying — to count duplicate data only once? This can done using “Pivot Table” approach, but Excel also has several formulas that you can exploit to come up with the same results. Suppose you are operating an online store. Your website’s back-end e-commerce system reports sales per item and per day, like the table below: Suppose further that you want to know how many unique… Read More

How to use INDIRECT function to create references to other Excel files

How Tos MS Office Tips Tips & Tricks Windows Tips October 16, 2023

MS Excel has a vast array of built-in functions that you can use in your formulas to manipulate your data. The main Excel function that I will demonstrate in this article is the INDIRECT function. This is a nice piece of tool to easily create references to external Excel files in your formulas. The INDIRECT worksheet function of MS Excel returns the contents of the specified reference and displays its contents. The INDIRECT worksheet function can be used to create “linked references” to other workbooks. Using the INDIRECT function, you… Read More

Microsoft Excel Tips: How to calculate the number of days, months or years between two dates

How Tos MS Office Tips Tips & Tricks October 9, 2023

One of the fundamental questions that Excel users may find themselves asking is “How to calculate the number of years, months, or days between two given dates?” This article will show the ways or methods in calculating days, months or years between 2 given dates. There are certain situations when you want to know the number of days, months or years. For instance, in computing rentals or amortizations, in comparing usage or consumption, and many other scenarios, knowing how to calculate the days, months and even years will be vital…. Read More

Microsoft Excel: How to apply conditional formatting to a cell based on values of another cells or range?

How Tos MS Office Tips Tips & Tricks October 1, 2023

There are times in our spread sheet that we need to format the cells or cell range based on certain conditions. These conditions however pertain to the contents of the cells that you are to format. This is when Conditional Formatting is very useful. For instance, format the cell range G5:G50 if the values in the individual cells is less than 70%. If you are a teacher calculating the students grade in Excel and you want to automatically color the final grade with dark red if they are faling grades, like below… Read More

Microsoft Excel: The imaginary cell borders are gone. How to show them back?

How Tos MS Office Tips Tips & Tricks October 1, 2023

We are used to seeing cells in MS Excel with a light gray borders. These are called “grid lines”. They are just there as a default behavior of MS Excel to provide visual boundaries among cells. These grid lines are NOT actually being printed by default, they are just visual aide to users to visually see the boundaries between cells. When your Excel settings are messed up and you end up with just a plain white worksheets without those cell boundaries or grid lines, here are the steps to show… Read More

Microsoft Excel: How to Send Excel Workbook as Email Attachment

How Tos MS Office Tips Tips & Tricks September 13, 2023

If you are using MS Excel 2007, 2010 or the newer versions from the date of this writing, now you can send your Excel file as an email attachment using the commands found in Excel menus. This is the “Send As Attachment” command in Excel 2010. What this menu command does is actually simple. When you open your workbook in Excel and this workbook is intended to be sent to someone through email, you can click on the Send As Attachment button and then Excel will invoke or launch your… Read More

Microsoft Excel Power Shortcuts: Converting cell formulas to values

How Tos MS Office Tips Tips & Tricks September 11, 2023

Microsoft Excel 2010, like its older version (2003 or 2007) are packed with huge sets of features and shortcuts waiting to be uncovered. In this article, we will discover how to copy the values of a cell excluding the formulas. Normally, when you copy the cells containing a formula, the formula is copied automatically and the cell references are updated dynamically. This is a very essential feature of Excel. Since the formula is being copied and the cell references in the formula are updated, the cells will then show up… Read More

Microsoft Excel: How to extract the “Month”, Day or Year in a DATE entry in a cell?

How Tos MS Office Tips Tips & Tricks September 10, 2023

Suppose you are working in an Excel sheet containing cells with dates. Depending on your actual requirement, one of the things that you might want to do is to extract the day, month, or even the year in a date entry in an Excel cell. For instance, your cell A1 contains the date June 7, 2013. It could have been entered as 6/7/2013 but the cell was format to show the date in a long pattern. But as long as Excel recognizes the data as a DATE, then it stores… Read More