Use of Sequence function to create a dynamic calendar template Download Excel Template from https://bi-analytics.org/files/file/313-dynamic-calendar-excel-template/

Download file https://bi-analytics.org/files/file/312-rank-function-in-excel/ The Excel RANK.AVG function returns the rank of a number against a list of other other numeric values. When values contain duplicates, the RANK.AVG function will assign an average rank to each set of duplicates. The Excel RANK.EQ function returns the rank of a number against a list of other other numeric values. When values contain duplicates, RANK.EQ will assign the higher rank to each set of duplicates.

Download file for practice https://bi-analytics.org/files/file/311-large-and-small-in-excel/

Thank you for your feedback I will consider your suggestion in upcoming videos I also teach one to one and there one will find such value addition

Hi Saurabh, I like your way of teaching really it is helping to enhance my knowledge on Excel. However, when you are using some formula's, you just show the formula but there will not be any explaination. If you could explain the formula while using like how it works, whether we should sum first or divide first it will help us lot.

Learn ways of filling series and copying series in Microsoft Excel #excel #exceltutorial #exceltraining #excelbasics

1. Ctrl + N: To create a new workbook. 2. Ctrl + O: To open a saved workbook. 3. Ctrl + S: To save a workbook. 4. Ctrl + A: To select all the contents in a workbook. 5. Ctrl + B: To turn highlighted cells bold. 6. Ctrl + C To copy cells that are highlighted. 7. Ctrl + D To fill the selected cell with the content of the cell right above. 8. Ctrl + F: To search for anything in a workbook. 9. Ctrl + G: To jump to a certain area with a single command. 10. Ctrl + H: To find and replace cell contents. 11. Ctrl + I: To italicise cell contents. 12. Ctrl + K: To insert a hyperlink in a cell. 13. Ctrl + L: To open the create table dialog box. 14. Ctrl + P: To print a workbook. 15. Ctrl + R: To fill the selected cell with the content of the cell on the left. 16. Ctrl + U: To underline highlighted cells. 17. Ctrl + V: To paste anything that was copied. 18. Ctrl + W: To close your current workbook. 19. Ctrl + Z: To undo the last action. 20. Ctrl + 1: To format the cell contents. 21. Ctrl + 5: To put a strikethrough in a cell. 22. Ctrl + 8: To show the outline symbols. 23. Ctrl + 9: To hide a row. 24. Ctrl + 0: To hide a column. 25. Ctrl + Shift + :: To enter the current time in a cell. 26. Ctrl + ;: To enter the current date in a cell. 27. Ctrl + `: To change the view from displaying cell values to formulas. 28. Ctrl + ‘: To copy the formula from the cell above. 29. Ctrl + -: To delete columns or rows. 30. Ctrl + Shift + =: To insert columns and rows. 31. Ctrl + Shift + ~: To switch between displaying Excel formulas or their values in cell. 32. Ctrl + Shift + @: To apply time formatting. 33. Ctrl + Shift + !: To apply comma formatting. 34. Ctrl + Shift + $: To apply currency formatting. 35. Ctrl + Shift + #: To apply date formatting. 36. Ctrl + Shift + %: To apply percentage formatting. 37. Ctrl + Shift + &: To place borders around the selected cells. 38. Ctrl + Shift + _: To remove a border. 39. Ctrl + -: To delete a selected row or column. 40. Ctrl + Spacebar: To select an entire column. 41. Ctrl + Shift + Spacebar: To select an entire workbook. 42. Ctrl + Home: To redirect to cell A1. 43. Ctrl + Shift + Tab: To switch to the previous workbook. 44. Ctrl + Shift + F: To open the fonts menu under format cells. 45. Ctrl + Shift + O: To select the cells containing comments. 46. Ctrl + Drag: To drag and copy a cell or to a duplicate worksheet. 47. Ctrl + Shift + Drag: To drag and insert copy. 48. Ctrl + Up arrow: To go to the top most cell in a current column. 49. Ctrl + Down arrow: To jump to the last cell in a current column. 50. Ctrl + Right arrow: To go to the last cell in a selected row. 51. Ctrl + Left arrow: To jump back to the first cell in a selected row. 52. Ctrl + End: To go to the last cell in a workbook. 53. Alt + Page down: To move the screen towards the right. 54. Alt + Page Up: To move the screen towards the left. 55. Ctrl + F2: To open the print preview window. 56. Ctrl + F1: To expand or collapse the ribbon. 57. Alt: To open the access keys. 58. Tab: Move to the next cell. 59. Alt + F + T: To open the options. 60. Alt + Down arrow: To activate filters for cells. 61. F2: To edit a cell. 62. F3: To paste a cell name if the cells have been named. 63. Shift + F2: To add or edit a cell comment. 64. Alt + H + H: To select a fill colour. 65. Alt + H + B: To add a border. 66. Ctrl + 9: To hide the selected rows. 67. Ctrl + 0: To hide the selected columns. 68. Esc: To cancel an entry. 69. Enter: To complete the entry in a cell and move to the next one. 70. Shift + Right arrow: To extend the cell selection to the right. 71. Shift + Left arrow: To extend the cell selection to the left. 72. Shift + Space: To select the entire row. 73. Page up/ down: To move the screen up or down. 74. Alt + H: To go to the Home tab in Ribbon. 75. Alt + N: To go to the Insert tab in Ribbon. 76. Alt + P: To go to the Page Layout tab in Ribbon. 77. Alt + M: To go to the Formulas tab in Ribbon. 78. Alt + A: To go to the Data tab in Ribbon. 79. Alt + R: To go to the Review tab in Ribbon. 80. Alt + W: To go to the View tab in Ribbon. 81. Alt + Y: To open the Help tab in Ribbon. 82. Alt + Q: To quickly jump to search. 83. Alt + Enter: To start a new line in a current cell. 84. Shift + F3: To open the Insert function dialog box. 85. F9: To calculate workbooks. 86. Shift + F9: To calculate an active workbook. 87. Ctrl + Alt + F9: To force calculate all workbooks. 88. Ctrl + F3: To open the name manager. 89. Ctrl + Shift + F3: To create names from values in rows and columns. 90. Ctrl + Alt + +: To zoom in inside a workbook. 91. Ctrl + Alt +: To zoom out inside a workbook. 92. Alt + 1: To turn on Autosave. 93. Alt + 2: To save a workbook. 94. Alt + F + E: To export your workbook. 95. Alt + F + Z: To share your workbook. 96. Alt + F + C To close and save your workbook. 97. Alt or F11: To turn key tips on or off. 98. Alt + Y + W: To know what's new in Microsoft Excel. 99. F1: To open Microsoft Excel help. 100. Ctrl + F4: To close Microsoft Excel.

MAP – REDUCE – SCAN MAKEARRAY –BYROW – BYCOL ISOMITTED These are recently announced functions by Microsoft .where we can pass lambdas as arguments Download template files

As-Salamu Alaikum (Peace be upon you), I am Ashrafu Alam, from Chittagong, Bangladesh. I worked at a steel industries as an Accounts officer. Now I am a lecturer at Kadalpur School and College.

Hi team, I am trying to combine two simple excel files just to learn this power query and getting this massage: DataFormat.Error: External table is not in the expected format. Details: cd5hk2xn.XLS can you help please

Download Excel file

Histogram Frequency Mean Mode Median Subtotals large Small Download dataset for practice

This is excellent tool for files in one folder. How can we do combining data from different worksheet in new worksheet within one file ? Is it possible.? I have attached the file. I want only coloured cells from worksheet - call , miss into follow up worksheet. combining data from differennt worksheet.xlsx

hello, I was practicing scenario one - where change cell is unit price. Had a question. If I change the value in one of the unit price, doesnt it change the summary table by itself?

Hello thanks for the idea. i tried this. Extracted postal code with mid( ). When i use vlookup giving reference to this extracted postal code, it gives me error. I cooy postal code and paste as value. Still it gives me an error. Its only when i type the postal code, vlookup gives me data. Is there any way around to overcome this problem. Thanks Kashmirs

Thanks. Will try that.

You need to extract postal code in a different column and then it can be applied

Hi Can XLOOKUP / VLOOKUP be useful for the text in the cell. I have a city and postal code in B1 cell. And based on postal code in B1 cell, I want return - package center name . I have table for postal code and center name associated with that postal code. I tried using vlookup and xlookup, but I am not getting the result. Attached is the file. Thanks Kashmira XLOOKUP.xlsx