Talk 2 Sabrez!

Problem: You want to show some data in excel pivot table in which you want to fix row headings and column headings of your pivot table.

Let’s have a real world scenario here to understand the pivot table easily.

You need to show the value of Sales Opportunities in a pivot table with Months as column headings and the Sales Managers names as rows.

Where there is data in the underlying database (spreadsheet is linked to a SQL server database) then there is no problem. However if there is no data for a given month then of course by default the Pivot Table doesn't supply that month as a column.

Is there anyway to force a Pivot Table to show column headings in a non-dynamic form. So if there is no data for May the column is still there. This will make readability for my boss a lot easier.

On a similar subject is there also anyway I could fix the rows so that all Sales Managers show up even if there is no data.

Solution:

Locate your data in excel and make a pivot table with it. Select any column heading or row heading of your pivot table and right click on it. Select Field Settings from right click menu after that goto layout & Print and select Show items with no data. That’s it!!

For you help I am also attaching the snap shots. Hope this tutorial will work for you!!

Today, I am going to share with you an interesting set of formula which can be used to find the exact age of a person based on DOB.

You can used below excel formula exactly on your excel sheet to find the age of a person.

=TEXT((NOW()-B2)&""," yy ""Years"" m ""Months"" dd ""Days""")

In above example you can assume that you have mentioned your DOB in B2 column. To enter current date in your excel sheet you can use CTRL+; shortcut key. It will insert current date in system format after that you change this date in your DOB.

1. To format any selected object, press ctrl+1
2. To insert current date, press ctrl+;
3. To insert current time, press ctrl+shift+;
4. To repeat last action, press F4
5. To edit a cell comment, press shift + F2
6. To autosum selected cells, press alt + =
7. To see the suggest drop-down in a cell, press alt + down arrow
8. To enter multiple lines in a cell, press alt+enter
9. To insert a new sheet, press shift + F11
10. To edit active cell, press F2 (places cursor in the end)
11. To hide current row, press ctrl+9
12. To hide current column, press ctrl+0
13. To unhide rows in selected range, press ctrl+shift+9
14. To unhide columns in selected range, press ctrl+shift+0
15. To recalculate formulas, press F9
16. To select data in current region, press ctrl+shift+8
17. To see formulas in the worksheet, press ctrl+shift+` (ctrl+~)
18. While editing formulas to change the reference type from absolute to relative vice versa, press F4
19. To format a number as currency, press ctrl+shift+4 (ctrl+$)
20. To apply outline border around selected cells, press ctrl+shift+7
21. To open the macros dialog box, press alt+F8
22. To copy value from above cell, press ctrl+’
23. To format current cell with comma formats, press ctrl+shift+1
24. To go to the next worksheet, press ctrl+shift+pg down
25. To go to the previous worksheet, press ctrl+shift+pg up