Transpose Excel Rows and Columns [Quick Tip + Video]
Transposing rows and columns in a table is one of the most useful tricks when you are pasting data. Yet, it always surprises me that very few people actually know this. So here it is…
How to get tick marks in Excel? [custom cell formatting]
Here is tricky scenario, faced by Basil, our forum member, “I want to have Excel display a wing ding check mark when a user types “y” in a cell. I have been trying to do a substitute formula but putting the symbol in an unused portion of the spreadsheet and calling it to the selected cell but I can’t get it to work. Any thoughts?”
We can use either cell formatting or conditional formatting to achieve this.
Pin Frequently Used Documents to File Menu to Save Time
Here is a quick productivity tip if you work with same set of excel (or word or powerpoint) documents everyday. Just pin the documents to recent documents list in the Office Menu in MS Office 2007. See this: More excel 2007 productivity tips & double click tricks.
Using Combo Charts to Group Related Time Events [Charting Goodness]
In his latest book, Now You See It, on pages 165 and 166 of the book, Stephen Few discusses how grouping related time intervals can facilitate analysis of data. As an illustration he explains that when viewing data of daily website visits, it helps in separating weekdays and weekends to differentiate expected traffic during these periods. The use of this technique would make it easier for the analyst to identify any anomalous movement in ether the weekend or the week day.
Fortunately excel combo charts can help you do that. In this guest post, Paresh explains to us how to do this.
Use ROWS() and COLUMNS() formulas to generate numbers in a sequence [quick tip]
Here is a quick excel formula tip to start your week. Use ROWS() and COLUMNS() formulas next time you need sequential numbers. What does ROWS() excel formula do? ROWS excel formula takes a range as an argument and tells you how many rows are there in that range. For. eg. ROWS(A1:A10) gives 10. How can you […]
Create a number sequence for each change in a column in excel [Quick Tip]
Here is a quick formula trick you can use to generate sequence numbers that only increment when there is a change. Assuming the sequence of values are in column C from C3, you can write the following formula in B4 onwards (B3 will be 1, wake up…) =IF(C4=C3,B3,B3+1) Now just copy paste the formula over […]
Do you know these Double-click Tricks in Excel?
Most of us think of mastering formulas, learning macros and being supergood with charts when we think of being productive with spreadsheets. But often learning simple stuff like keyboard shortcuts, using mouse and working with menus and ribbons can be a huge productivity booster for us. So as part of this installment of spreadcheats we […]