Show only few rows & columns in Excel [Quick tip]
Each new sheet in MS Excel comes up with a 1,048,576 rows and 16,384 columns. While it has a certain binary romantic ring to it (2^20 rows & 2^14 columns), I am yet to meet anyone using even half the number of rows & columns Excel has to offer.
So why leave all those empty rows & columns hanging in your reports?
Would it not look cool if your reports showed only few rows & columns as needed, like this:
Check if a list has duplicate numbers [Quick tip]
A while ago (well more than 3 years ago), I wrote about an array formula based technique to check if a list of values have any duplicates in them.
Today, lets learn a simpler formula to check if a list has duplicate numbers.
Assuming you have some numbers in a range B4:B10 as shown below, we can use MODE + COUNTIF formulas to check if there are any duplicate values in a list.
Use MAX to find latest date in a list [Quick tip]
Here is a quick tip that I learned while conducting training classes in Australia. If you have several dates in a range and you want to find out what the latest date is, just use MAX, like: =MAX(A1:A10) would give you the latest date. A Question…, Assuming you have some dates (not necessarily sorted) in […]
Sort Pivot Tables the way you want [Quick tip]
Ever looked at a Pivot table & wondered how you can sort it differently?
“If only I could show this report of monthly sales such that our best months are on top!”
Well, there is a way to do it without sacrificing 2 goats or pleasing the office Excel god. Just use custom sorting options in Pivot tables.
Compare 2 Excel Files using View side by side Mode [Quick Tip]
Often we have 2 workbooks with same data structure but different data. We want to compare both and see how they differ. Lets talk about view side by side mode in Excel and how we can use it in situations like these.
How to add your own Macros to Excel Ribbon [quick tip]
Do you know that in Excel 2010 you can create your own Ribbon tabs and add anything to them, including your own macros? Today, we are going to take a look at this useful feature and learn how to add your own macros as buttons to Excel Ribbon. Steps to Add your own macros to […]
Use Text Format to Preserve Leading Zeros in Excel [Quick Tip]
Here is a quick tip to add awesome to your Wednesday.
If you want to enter numbers like 00023 or 023.340 or 23.34500 in your Excel sheet, you would notice that Excel magically removes leading zeros and trailing zeros (after decimal point) as the number 23 is same as 00023. But sometime, we want 00023, not 23. Then what?!?
Very simple, we use TEXT format instead of number format. Just select the cells where you are going to enter these numbers, and from Home ribbon > Number area, select “Text” as cell type. This tells Excel to treat any value you enter as Text, not as number. So when you type 00023, it will appear as 00023.