Converting numbers in Excel to text is a common practice and has many benefits. Let’s look at two very common scenarios: Scenario #1 – Preserving leading zeroes If you are entering part numbers, employee IDs, postal codes, etc… there is a high likelihood that the sequence of numbers may have leading zeroes. Part # – […]
Excel – Entering Fractions
When you think about entering a fractional value in Excel, you may be tempted to convert the fraction from its original base value to a Base 100 value. THE LONG, HARD WAY (most people’s way) Suppose you measure the height of your child on a door every birthday and you want to calculate the average […]
The F4 Key – Microsoft Office’s “Swiss Army Knife”
One of the lesser-known keyboard shortcuts is also one of the most versatile. I am speaking of the enigmatic F4 key. At its core, the F4 key simply repeats the last action you performed in the program. Whether invoking a simple bold action in Word, deleting a row in Excel, or inserting a shape object […]
Microsoft Office – Quick Access Toolbar (Awesome “Hidden” Feature)
When Microsoft Office 2007 switched from the traditional dropdown menus and toolbars method of delivering program features to the now widely used Tabs and Ribbon delivery system, one of the abilities they revoked from the users was the ability to customize the program controls. Knowing that they (Microsoft) would have angry hoards with torches and […]
Excel – Performing Mathematical Operations Using Copy/Paste
There are a LOT of different ways to manipulate numbers in Excel. After all, manipulating numbers is Excel’s specialty. There are times when you have a data set that you need to manipulate against another data set but you don’t want to create a third data set that contains the manipulation results. Example #1 In […]
Write Shorter Excel Formulas (Trick)
Writing formulas in Excel is a fundamental task performed millions of times per day by people all over the world. Unfortunately, many of those formulas are longer and thus more complex than necessary. When you are referencing cells from different sheets, Excel must recognize when a cell is on a different sheet as the formula […]
Excel – The BEST Dynamic Data Validation Dropdown List
Excel Data Validation lists are a great way to expedite data entry ensure consistent data entry reject undesirable information One of the few and far between shortcoming of Data Validation lists is the fact that with long lists, they have to exist somewhere in the workbook. The existing list supplies the entries displayed when the […]
Excel – Create Named Ranges Quickly and Use Retroactively
Excel Named Ranges are a great way to turn ugly, confusing formulas into easy to read statements. Consider the following formula: =($J$44 – $M$213) * $H$16 Without any context given to the formula, the viewer has no idea what this is supposed to produce. The only obvious logic is that there is some subtraction […]
Office 2016 Template Library – Worth a Revisit
If you have been a user of Microsoft Office going back to the “old days” of Office 95 thru Office 2003, one of the features you probably held in least regard were the templates. Templates suffered several major disadvantages: Templates were stored on the local PC’s hard drive, which took up much needed storage space […]
Excel – Consolidate Tool
Suppose you have data stored in multiple locations but you wish to create a chart from all of the data sources as if they were stored as a single data source. You could spend time manually copying and pasting the data into a single, unified list, but this may prove difficult if the data is […]