Unable to Unhide Columns or Rows in Excel
There are certain situations when you can’t “unhide” columns on the left and/or rows at the top and it is not a sheet protection issue. (more…)
Relative and Fixed Reference Trick in Excel
When creating ranges of formulas that you want to copy down, you sometimes have a trade off in the use of fixed and relative references. If you need to create a relative reference that acts like a fixed reference you can use a trick. (more…)
Following Links in Excel
Excel has a shortcut that allows you to follow a link to its source but it has a limitation find out how to get around that limitation. (more…)
Converting Text Numbers and Reversing Signs in Excel
Converting multiple text numbers into real numbers or reversing the sign on multiple numbers is easy in Excel if you know how to use Paste Special. (more…)
Reduce Excel File Size
I recently helped a client reduce the size of an Excel file. The file took a while to save which was frustrating and time consuming. I thought I would share this reasonably easy solution. (more…)
Excel Filtering for Blanks or Zeroes
If you want to filter by blanks across multiple columns the standard Filter feature can’t help you. You can use the Advanced Filter but that takes time to set up and most users don’t know how to use Advanced Filter. (more…)
Easier Structures in Excel
What is the best layout when working with months/quarters/half years and full years? There are a few common structures. I prefer the one that lets you create single formulas that can be quickly copied across and down with as few copies as possible. (more…)
Reduce Excel Formula Length
When you create formulas that refer to other sheets Excel typically includes the name of the current sheet when you return to the current sheet and refer to a cell.
Excel Subtotal Sort Magic
Sometimes Excel surprises me. In this case it sorts in a way I didn’t expect, but in a good way. Thanks to Mr Excel for the tip. (more…)
Count Sundays between two dates
We’ve all heard the term “A month of Sundays” to describe a long time. Well what if you wanted to count how many Sundays between two dates? (more…)
Alt Key Shortcuts
I use Alt key shortcuts a lot when I am working and I have found a couple more useful ones. (more…)
Totalling Tip For Excel
I wanted to offer a solution to a common problem I see in Excel. It relates to creating totals in data that isn’t structured that well.
Excel Full Screen
There are times in Excel when you want to see as much of the Excel grid as possible. You may be reviewing or presenting a dashboard or looking at a large PivotTable. Excel has a Full Screen mode that shows just the grid and the sheet tabs.
Excel and pdf
In Excel you can us Save As to save a file as a pdf, but it isn’t quite as effective in Excel as it is for MS Word. Often you only want to save a single sheet or a few sheets to pdf. Try this.
Show Horizontal Axis Entries Below the Chart
I learned about a chart Axis option in Excel during a recent webinar – thanks to one of the attendees. You can show the Axis entries below the chart – this is handy for column charts that display negatives. (more…)
Handling Text and Real Numbers with VLOOKUP
Sometimes data that comes into Excel with code numbers formatted as text. This can stop VLOOKUP functions from working and return the dreaded #N/A error. With a couple of tweaks you can lookup both real numbers and text numbers in the one formula. (more…)
Useful Excel VBA Range Technique
It is common to create headings for data sheets in Excel VBA. There is an array technique that can make this a simple process. (more…)
Excel VBA and Variables
Variables can speed up your code and make maintenance a lot easier. You should always declare or Dim (technical term) your variables, here’s why.
(more…)Adding years or months to a date in Excel
When working with loans or leases, it is common to have to add a number of years to a start date to determine the end date. An Excel function can automate that process. (more…)
Excel VBA Command to Clear the Clipboard
When using copy and paste in a macro it is a good idea to clear the clipboard at the end of the macro. If you don’t, the user could use paste to paste the last thing you had copied in the macro.
(more…)Excel – Convert Text Month to the Month Number
Ever needed to convert the text Sep or September into a 9? Well Excel can convert text months into their respective numbers.
(more…)Quickly and easily hiding and unhiding rows and columns in Excel
Hiding rows and columns is a common process in spreadsheets. Excel’s grouping feature can make the process easier and make it more apparent when rows or columns have been hidden. (more…)