Slicers to Tables

Do you know that now slicers can be created even with tables.

What is a slicer, in a group of data if you want slice and see a filtered data, you use slicer.

How to use

a)      Create a data Sample

b)      Make that as a table

c)      You will find it in Insert Tab > Table

d)      After the table is done(ensure headers are there)

e)      Place the cursor on the Table and insert slicer(see at the last of the image), select the required fields

f)       You will now be able to slice and see the information.

How to Get count of Unique Data

Here is the formula, you can get the count of unique numbers in a given data

 

If the data is numbers :

=SUM(IF(FREQUENCY(D6:D44,D6:D44)>0,1))

 

If the data is Text :

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

ATO 2014 Feed Back

Hi,

How did you enjoy the ATO 2014. Here is the blog, i have created for you all to share your views and comments.....

Venu

Find Last Record

Do you know if you have to find the last record value in a column

a)      Use CountA(column)

a.      It would give the number of items in that column

b)      Use offset Function

a.      Use the Offset refering to row increment of the above value

 

Frequently used Excel Short Cuts

Now, let us see Top 10 shortcut keys we use on day-to-day basis. Of course we will use more than 10 shortcuts but these are the most required shortcut keys while working on Excel.
  

1. To select the data in a sheet - - > CTRL + A (active cell should be within the data)
    
• To select the complete sheet - - > CTRL + A ('once' if the active cell  is outside the data, 'twice' if the selection is within the data)
   
• After selecting data if we want to see corner cells of each side - - >CTRL + . (dot)
   
2. To filter the data set - - >CTRL + SHIFT + L (hold CTRL and SHIFT then press L)(To activate filters and to deactivate filters)
    
• To activate the filters we can also do ALT + D + F  + F (this option is imported from Excel 2003)
   
3. To auto adjust the cell width and height - - >ALT + H + O + I and ALT + H + O + A
    
• To auto adjust we can also use double click from mouse.
   
4. To use Format Painter - - >ALT + H + F + P (select the format you want to add to clipboard and then select the cell or data where the format to be applied)
    
5. To select the appropriate formula, type the first letter in the cell after '=' and choose one among the list by using down arrow and then press TAB to auto select the formula. This will actually avoid typo error and also time. eg: To select Vlookup, type =VL and then TAB.
    
• To refresh the formulas in the work sheet and work book  - - > Shift + F9 (function key) and F9.
6. While working on formulas take example above as =VLOOKUP( 'after coming here press CTRL + A will get formula arguments. This will help the new bees to know how to import the data to the arguments.
    
• To select the name manager - - > CTRL + F3 (function key).
7. To convert the data into a Table format use - - > CTRL + T (then ENTER).
    
8. To get data validation list - - > ALT + A + V + V + TAB + L + ENTER (looks complicated but very useful).
    
9. To apply 'all borders' for the data selection - - > ALT + H + B + A
    
10. To edit and copy the above cell content  - - > CTRL + ' (apostrophe)

The bonus shortcut 1 is - - > CTRL + 1 (one) gives the format cell window and this is very good features in Excel. We can use this shortcut for data format, table format and graph format.

Source 

http://www exceltoxl com/index php?p=50&more=1&c=1&tb=1&pb=1