This document contains tips and shortcuts for Excel. It discusses how to select cells and ranges, add and show sheets, edit cells, format values, protect and zip workbooks, customize the quick access toolbar, navigate between tabs and windows, use formulas and functions, sort and filter data, create and format pivot tables, and more. The tips are numbered and range from basic tasks to more advanced data analysis and reporting features.
This document contains tips and shortcuts for Excel. It discusses how to select cells and ranges, add and show sheets, edit cells, format values, protect and zip workbooks, customize the quick access toolbar, navigate between tabs and windows, use formulas and functions, sort and filter data, create and format pivot tables, and more. The tips are numbered and range from basic tasks to more advanced data analysis and reporting features.
This document contains tips and shortcuts for Excel. It discusses how to select cells and ranges, add and show sheets, edit cells, format values, protect and zip workbooks, customize the quick access toolbar, navigate between tabs and windows, use formulas and functions, sort and filter data, create and format pivot tables, and more. The tips are numbered and range from basic tasks to more advanced data analysis and reporting features.
This document contains tips and shortcuts for Excel. It discusses how to select cells and ranges, add and show sheets, edit cells, format values, protect and zip workbooks, customize the quick access toolbar, navigate between tabs and windows, use formulas and functions, sort and filter data, create and format pivot tables, and more. The tips are numbered and range from basic tasks to more advanced data analysis and reporting features.
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
You are on page 1/ 6
1.
Range of cells:
D6:G9
D6,G9
2. To find the total column numbers.
3. To find the sheet level search:
Right arrow, right click you will find the sheets.
4. To add sheet (short cut) Shft+F11 5. To increase/decrease decimal values: HOME general down / increase or decrease the decimal values.
6. F2 Write down and edit on same cell. /double click on the same cell also will work. 7. To show sheet tabs: 8. To strike the words in a cell ctrl+shft+F /Alt+K /ctrl+5
9. Remove gridlines:
View -> gridlines
10. To Zip the excel file: Save as -> Excel binary workbook.
11. Quick access tool bar:
View -> gridlines -> right click -> Add quick access tool bar Options -> Quick access tool bar -> Add 12. Add a new tab to your favourites: Options -> customized ribbon -> add new tab -> add favourites 13. Ctrl+space bar == select Row 14. Shft+spacebar == select sheet 15. Windows+left arrow == Two screens 16. Windows +1 == tabs 17. Windows +2 == outlook 18. Ctrl+tab == each tab in internet explorer 19. Format painter double click on the painter and paste no of times. 20. Formula bar -> number -> account 21. Ctrl+; date
22. Ctrl+shft+3 -> date format
Ctrl+shft+2 -> time format Ctrl+shft+; -> current time 23. One command to give thhe both date and time: =now()
24. To protect sheets from third user:
Review -> protect sheet -> select which cell to be protected.
25. To fill the blank entries:
HOME -> find&select -> Go to special -> blanks
=+uparrow+ctrl+enter
26. Ctrl+enter -> will give you the same number for selected cells.
1 1 1
1 1
Ex:
27. To find the formula cells in the entire excel.
HOME->find&select -> Go to formula -> select formula
28. Select first row of blank data:
Find&select -> goto special ->blanks ->ok – Ctrl+ - entire column 29. Sort key: Options -> advance -> web options -> edit custom list Import all selected items. 30. Aligning data example:
31. Exercise: every five rows one blank data.
32. Formula’s $A $1 -> Row & column will not move. $A 1 -> only column will move A $1 -> only row will move A1 -> relative reference. 33. Two sheets compare values in reports: =May!A1 Namebox save as MAY_CLOSE (select all entries) First sheet give formula (=sum(MAY_CLOSE)) will give the total count of second sheet. -> formaulas -> name manager.
34. F4 -> ‘$ ‘ symbol shortcut
35. F3 -> Functions name shortcut 36. Formulas -> trace 37. Ctrl+shft+5 -> converts to percentage 38. Formula -> evaluate formula 39. Insert -> table -> (alt+=) to do auto sum of all tables. 40. Data -> data validation -> add normal table entries 41. Insert -> pivot table (use only reporting filter) 42. Pivot table -> design -> report layout -> show in tabular form 43. To check the latest modified file: select pivot table -> right click -> pivot table -> data -> select refresh data when opening the file . 44. Analyze -> change data source -> change the row numbers if you added any additional data. 45. Analyze -> group selection 46. To filter the particular data in the entire sheets: Goto data -> custom filter -> select customer -> text filter -> custom filter-> begins with
