Home » Business News » 40+ Microsoft Excel Formulas and Shortcut Keys

40+ Microsoft Excel Formulas and Shortcut Keys

by James
40+ Microsoft Excel Formulas and Shortcut Keys
Spread the love

40+ Microsoft Excel Formulas and Shortcut Keys: Microsoft Excel is an application package that is used mainly for calculations.

Excel is a very vast software that can be used for calculating, analyzing and manipulation of data in the workbook.

RECOMMENDED: Powerful Microsoft Excel Shortcuts for Business Owners

40+ Microsoft Excel Formulas and Shortcut Keys.

There are many functions in Microsoft Excel which can be used for so many activities in a workbook. It can also boost your intelligence quotient (I.Q).

This is because you will be needing to crack that brain of yours to put it into practices for good works and recommendations.

READ: Truck Driver Jobs in Canada For Foreigners 2023 (Application Guide)

We know what you will like and study for your personal benefit or for an organization you work for. Below are 40+ Microsoft Excel Formulas and Shortcut Keys that may interest you.

  1. Quick Analysis Tool: This tool works after you must have highlighted a table. It is always at the bottom of your table for formatting purposes.
  2. Filter: Filter is found under the Data menu. It is used to sort and filter either in an ascending order, descending order or to pick out a set of data visibly.
  3. Drop Down List: It can be created through data validation in Data menu. All you need to do is to click on the list then select your traces in the source.
  4. Auto Fit Column Width: Just double click on the column to fit in. In cases of multiple columns, hold down your control + shift key while you select all the columns before double clicking.

    SEE: NOUN Virtual Exam Tips & Requirements For Undergraduates

  5. Transpose: To transpose, right click on a table to copy, then go to a different location and right click again to paste special, then click transpose and choose okay.
  6. Removing Duplicates: this is done by selecting your list, go to data menu and click on remove duplicates, then a dialogue box will appear. It will automatically highlight all the duplicate for you to deselect.
  7. Goal Seek: is done to meet up a goal or an objective. First click on the target cell, go to data menu and click on What if analysis then choose goal seek, then type in the amount expected on the value box. Add the cell and allow it to calculate automatically.
  8. V Lookup: This is use to fine ID data, click on the cell you want to calculate on, then click on the insert function (fx), search for V Lookup, find and select it on the Lookup Value, then select the name of the person, click on table array, select all other names and I.D,Then the column index number is the column that you want to return the index number from, choose the column number and type it in. Under the range, type FALSE, select lookup and okay it.All you have to do is to drag down the node. In cases of N/A result, use H Lookup or change the value of the cell before that of the errors.

    READ ALSO: NOUN Part-Payment of School Fees Guidelines for Students

  9. Flash & Auto Fill: in cases where you have email addresses, just start by typing the names of the individuals, then type out the email address for the first person, select the others and choose flash fill to fill automatically.See also Auto fill, when you click and drag the node, it copies automatically, but when you type numerically, like 1,2 and select both numbers, it fill all other column serially.The same thing is applicable to dates, days of the months and months of the year. Double clicking on the column fills automatically too’
  10. Paste Special Value: Just like you transpose, click on Value instead, This works without any formula.
  11. Images in Charts: This occurs after you must have created a chart then click on the chart a couple of time to bring out the format data point. Then click on fill in line, click fill and select picture or texture fill, click on file and choose an image.
  12. IF Function: Want to see the greater than or less than values, just click on function and type in IF and follow suit with function analysis.
  13. Screenshot: you can find this under the insert menu.
  14. Relative Referencing: This formula is use to reference more than one sheet or workbook.
  15. Show Formula: Go to file menu, click on advanced and scroll through to formulas in cell. Short cut key for this is your control key + ~.

    SEE ALSO: What to Expect from Your New Job as an Intern

  16. Text to Column: After you copying your data, go to data menu and click on text ti column. Choose unlimited, comma then next and finish.
  17. Conditional Formatting: It works when you highlight your cells and click on conditional formatting menu then perform your tax.
  18. Power Pivot: to enable this, go to file or office button, click on Add-ins via manage at the bottom, then select MS P.P for Excel.
  19. Freeze Panes: Click on the columns you want to be locked, then select freeze pane in view menu and click on freeze panes.This freezes the selected values and allow you to see the cells as you scroll further.
  20.  To navigate or scroll easily on Excel: Use control + right arrow, control + left arrow, control + up arrow, control + down arrow interchangeably.
  21. 3D Referencing: Use your control key to drag bellow sheets to create another, use shift key while dragging to select all sheet.Then click on insert on the second row to create or add a heading. Do Auto sum in other sheets and drag down in total sheet.
  22. Fore Cast Sheet: Highlight your cells first and go to Data menu, select forecast sheet, then type a data on forecast end.
  23. Sum Ifs Function: This is used to sum numbers in cells together.
  24. If Error Function: Add it to the beginning of your formula the (.0) and close your bracket.
  25. Filled Maps: Select your cells and go to insert menu and choose maps, all your data will be added to the map automatically. You can add charts as well in this format.
  26. X Lookup: This is use to return values.
  27. Ideas: This works after selection of cells, press plus to add charts to your sheet.
  28. Remove Blanks: Go to find and select, choose go to special and check the box for blanks then press okay, all the blank cells will be highlighted. Then right click on anyone of them and choose delete.
  29. Absolute References: This works when you press + 4 or dollar sign in between your cell name.
  30. To Combine Names: To do this press = A1 $” ‘$B1. Note that flash fill needs to be enabled in options first.
  31. Data Types: Go to data tab, click on geography, then an icon appears giving room to add other functions such as locations.
  32. Recommended Pivot Table: These are options of different formats excel recommends for users.
  33. Slicers: Go to insert menu and click on tables, make sure the box is checked for table headers. Then under insert, click on slicer and select the columns to be sliced. Hold down the control key to select multiples.
  34. Control Arrow Keys: These are keys used to scroll faster in Excel. Note that you can also use – sign to change data from negative to positive.
  35. Advanced Select: in find and select, use find to find all and select the ones you want to format.
  36. Name Range:  Go to formulas, choose create from selection and select the top rows and press okay. You can reference by just typing top names too.
  37. Count Blanks: Use COUNT BLANK (and select the range of cells you want to count) and enter. Use = COUNT A (select the areas again to count the filled cells).
  38. Natural Query Language: This also works in Excel.
  39. 3D Maps: it gives the world pictures at large. You can also flag the maps, find locations and choose to fitness. After which can be played and watched on the screen.
  40. Is Blank: Open bracket ( = IS BLANK ( Cell number) press enter, add, “Missing”, ” “).
  41. Convert: use = CONVERT (Choose the original data function, and choose the options below.
  42. Get Data from Web: Go to a site, copy the URL for the analysis, go to excel, go to data menu and click on get data from web, paste the link and click okay. Click on refresh menu to refresh tables automatically.
  43. People Graph: Go to insert and click on people graph to add.
  44. Sort: type = SORT (select the cells) and press enter.
  45. Status Bar Info: Right click on the status line below your sheet for more hidden information.
  46. Add Multiple Rows: Right click on multiple rows to insert rows, same with columns, just right click after selecting multiple cells.
  47. CHOOSE: type  = CHOOSE then open bracket ( to choose.
  48. UNIQUE: open bracket ( and select all the cells. Their uniqueness will be seen.

Spread the love

Related Posts

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.