Search

# All articles with 'tricks' Tag

## How to use Date & Time values in Excel – 10 + 3 tips

Published on Jul 28, 2021 in Excel Howtos, Learn Excel

Excel date time features are very handy and knowing how to use Excel date values can help you save a ton of time in your day to day spreadsheet chores. Let us prepare for your date with the sheet using these 10 handy tips.

Before jumping on to the tips, it helps to know how excel represents the date and time.

Microsoft Excel stores dates as sequential numbers … January 1, 1900 is serial number 1, and 28 July, 2021 is serial number 44405 because it is 44,405 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.

Continue »

## Quarterly & Half-Yearly Profit Loss Reports [Part 5 of 6]

Published on Mar 24, 2010 in Learn Excel, Pivot Tables & Charts

This is continuation of our earlier post Exploring Pivot Table P&L Reports.

We have learned how to change our P&L report on various data elements. We have seen how the P&L report can be changed with just few clicks.

In this post we will be learning some grouping tricks in PivotTables. We will cover grouping of dates, text fields and numeric fields. You will need to start with Monthly P&L report prepared in previous post. We will also learn some really clever tricks and hacks on how to group data in Pivot Tables. So read on…,

Continue »

## 31 Excel Tutorials – Learn and Be Awesome in Excel

Published on Mar 11, 2010 in Charts and Graphs, Featured, Learn Excel

In October 2008, I have started an ambitious series of posts on this blog called – Spreadcheats. These are little tricks, nuggets, tutorials on using Excel that would make anyone a spreadsheet guru.

The spreadcheats series has been wildly successful. I am compiling all this useful information and articles in to one big post so that anyone can follow the links and become good in Excel. Read on,

[Note: This is not for beginners. If you know what a formula is, you would enjoy this 31 articles]

Continue »

## How to Print Excel Comments

Published on Dec 16, 2009 in Excel Howtos, Learn Excel

We all know how to make comments on excel documents, just select the cell where you want a comment, press SHIFT+F2 and make the comment. But the comments are only visible when you open the workbook. What if you want to print out an excel file, but include all the comments as well?

Turns out you can do that with a hidden option in excel page setup.

Continue »

## Use Shapes and Images to make Prettier Charts [Dashboard Tricks]

Published on Dec 3, 2009 in Charts and Graphs

One of the annoyances of charts is that they all look like boxes (except for pie charts, they just look wrong). Boxes might be ok when you are making 1 or 2 charts. But a whole dashboard of boxes can look little rigid. So how can we make the charts peppy without loosing any effect? Like these charts below:

Very simple, we use drawing shapes in MS Excel to draw whatever we want and overlay the chart on top.

Continue »

## Group Smaller Slices in Pie Charts to Improve Readability

Published on Dec 2, 2009 in Charts and Graphs

Jon Peltier can stand on his roof and shout in to a megaphone “Use Bar Charts, Not Pies”, but the fact remains that most of us use pie charts sometime or other. In fact I will go ahead and say that pie charts are actually the most widely used charts in business contexts.

Today I want to teach you a simple pie chart hack that can improve readability of the chart while retaining most of the critical information intact.

Continue »

## Findout Thanksgiving Day’s Date for Any Year [Excel Formulas]

Published on Nov 25, 2009 in Learn Excel

Every year, on 4th Thursday of November, folks in US celebrate Thanksgiving day. A similar holiday exists in Canada too, they celebrate it on Second Monday of Every October.We will celebrate thanksgiving in PHD style, by sharing a wacky formula tip.

Today, we are going to learn how to use excel formulas to find out thanksgiving day’s date for any year.

Continue »

## Transpose Excel Rows and Columns [Quick Tip + Video]

Published on Nov 18, 2009 in Excel Howtos, Learn Excel

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…

Continue »

## How to get tick marks in Excel? [custom cell formatting]

Published on Sep 15, 2009 in Excel Howtos, Learn Excel

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.

Continue »

## Pin Frequently Used Documents to File Menu to Save Time

Published on Sep 4, 2009 in Learn Excel

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.

Continue »

## Using Combo Charts to Group Related Time Events [Charting Goodness]

Published on Aug 26, 2009 in Charts and Graphs

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.

Continue »

## Use ROWS() and COLUMNS() formulas to generate numbers in a sequence [quick tip]

Published on Aug 17, 2009 in Learn Excel

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 […]

Continue »

## Create a number sequence for each change in a column in excel [Quick Tip]

Published on Jun 29, 2009 in Excel Howtos, Learn Excel

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 […]

Continue »

## Do you know these Double-click Tricks in Excel?

Published on Jun 12, 2009 in Excel Howtos, Featured, Learn 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 […]

Continue »

## Array formula to check if a number is prime [just for fun]

Published on May 29, 2009 in Learn Excel

I am math-geek-wannabe, if there ever is such a category. During my 3rd year of graduation I went and purchased the volume 2 of Donald Knuth’s Art of Computer Programming and thus began my love with all things random and prime. I never really became the math-geek I always wanted to, instead I became an insurance expert with tons of passion for data and visualization. But when I get a chance to poke with randomness or numbers, I always lap it up with joy. And that brings us to an interesting array formula trick to check if a number is prime or not.

(assuming the number is in the cell B2) type the below formula and

=IF(MIN(MOD(\$B\$5,ROW(INDIRECT(“2:”&INT(SQRT(\$B\$5))))))=0,”not prime”,”prime”)

hit ctrl+shift+enter and bingo, it tells you if the number is prime or not. Read on to find how it works

Continue »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.