Without a doubt ‘Paste Link’ is one of the best time-saving features in Excel. Linking information from one cell to another saves you from having to update data in several places.
Another fabulous time-saving feature is ‘Paste Special Transpose’. This allows you to copy a horizontal range and paste it vertically, or vice versa. An absolute gem.
But have you ever tried to use the two together? It isn’t easy at all. In this post I want to share a wee work-around that will allow you to use the Paste Link and Paste Special Transpose features together. Continue reading Excel – Paste a link and Paste Special transpose your data
Years ago the Training and Development manager of a large organisation invited me to spend some time in their Accounts department in order to observe how the Accounts team dealt with their day-to-day tasks. My job was to report on any process that could be improved using Microsoft® Excel.
I’ll always remember asking one of the team (a “just-out-of-school” new recruit) what he was working on after I’d observed him sitting with a red biro and a ruler, diligently drawing red lines on 2 separate sheets of paper. It turned out he was trying to reconcile a customer’s account. Continue reading Excel – Using VLOOKUP to compare two lists
PROBLEM: When you enter a post code that has a leading 0 (zero), e.g. 0642, you see only the last 4 digits, not the leading zero. This is especially frustrating when using an Excel file in a Mail Merge!
SOLUTION: learn how make the 0 stay!
Continue reading Excel – Retaining a leading 0 (zero) when entering Post Codes – great for mail merging
PROBLEM: having to retype text when your data is in UPPERCASE, lowercase or a Mix oF bOTH.
SOLUTION: quickly change the text back to the proper case without having to retype it. Continue reading Excel – change the text to uppercase, lowercase or first capital
PROBLEM: Generating random numbers.
SOLUTION: Let Excel do it for you! Continue reading Excel – Generating random numbers
PROBLEM: Wasting time removing unwanted spaces in Excel data.
SOLUTION: Get to know the Trim function and save yourself time (and headaches). Continue reading Excel – removing unwanted spaces from a cell
PROBLEM: wasting time manually changing cell references in Excel from relative (e.g. A3) to absolute (e.g. $A$3).
SOLUTION: learn the relative to absolute shortcut key! Continue reading Excel – The relative to absolute shortcut key
PROBLEM: wasting time writing formulas to find the
Average, Count and Sum of a range.
SOLUTION: learn how in Excel you can quickly calculate the
Average, Count and Sum of a range without having to write a formula. Continue reading Excel – Find the Average, Count and Sum of a range without having to write a formula
PROBLEM: Dealing with formulas that result in error messages. For example the #DIV/0 error or the #N/A error.
SOLUTION: Start using the IFERROR function Continue reading Excel – Hiding errors using the IFERROR function
Anyone that regularly imports data into Excel will be familiar with the process of “data cleaning”. This is where you need to remove spaces and irrelevant data in order to get the data fit for use in Excel.
I recently had an excellent question regarding working with data imported into Excel where negative numbers are imported with the negative sign to the right of the number, e.g. 500-. These numbers are commonly referred to as “mirrored negatives”. Continue reading Excel – How do I move the negative sign in front of the number?