<\/p>\n
Editing & Shortcuts in Excel<\/h2>\n
1. New Workbook:<\/strong> Doing data entry, and edit existing values is pretty straightforward. Simply open a New Workbook (Control N<\/em><\/strong>), and begin creating Column Headings, and then enter the corresponding data in Rows.<\/p>\n Add data you’d like to record – to keep or to do some quick calculations.<\/p>\n 2. Editing:<\/strong> Once keyed in, you can edit any value by simply overwriting it. For Formulas, you can edit by clicking in the Formula bar below the Ribbon, or by clicking the Function key F2<\/strong><\/em>. The F2 key invokes the edit mode for formulas. Pressing Enter or moving to another cell commits the formula, and the resulting value is recalculated and displayed in the cell.<\/p>\n You can see a million+ rows (1,048,576 to be exact) & up to 16,384 columns in a single Excel Sheet.<\/p>\n And you can load upto 255 sheets\u00a0 in a single Excel workbook… which is more than sufficient in most cases.<\/p>\n But don’t stretch everything to the limit. Excel will slow down and might even crash… so plan your data acquisition in Excel.<\/p>\n 3. Keyboard Shortcuts: <\/strong>There are numerous shortcuts to the most popular features… more than what you can mostly remember… But you must remember these few essential shortcuts of Excel.<\/p>\n Control A:<\/strong> Select All Cells in a range or entire sheet.<\/p>\n Control B:<\/strong> Bold<\/p>\n Control C:<\/strong> Copy<\/p>\n Control V:<\/strong> Paste<\/p>\n Control X:<\/strong> Cut<\/p>\n Control S:<\/strong> Save<\/p>\n Control `<\/strong>\u00a0View All Excel Formulas in a Click<\/a><\/p>\n F4:<\/strong> Change from Relative to Absolute Referencing. For example, a reference to the C4 cell changes to $C$4 upon clicking F4 key<\/em>.<\/p>\n F7:<\/strong> Spell Check<\/p><\/blockquote>\n For a complete list of shortcuts in Excel<\/a>, visit Bastien Mensink’s mega list of shortcuts.<\/p>\n And for the Mac, you can also visit CFI’s Excel shortcuts.<\/a><\/p>\n 4. Find & Select:<\/strong> This often unused gem is available on the far right of the Home Tab<\/strong><\/em> of Excel.<\/p>\n You can do multiple things with the sub options available under the Find & Select<\/em><\/strong> dropdown:<\/p>\n There are even more options hidden under the Go To Special<\/strong><\/em> setting, which allows you to even detect row and column differences, identify blanks, and even find precedent or dependent values<\/em>.<\/p>\n The Find & Select feature<\/strong> has helped me several times in a tight spot to find things not so apparent.<\/p><\/blockquote>\n 5. Paste Special: <\/strong>While Cut, Copy, and Paste are fairly common use in Windows or Mac, the Paste Special<\/em> deserves a specific mention, for it is really a nifty tool that saves time.<\/p>\n Paste Special allows you to paste only Formulas, only Values, only Formats, or only Data Validation<\/strong><\/em> to name a few.<\/p>\n It can also Transpose<\/strong> <\/em>cells from rows to columns and vice versa. You need to explore\u00a0 Paste Special<\/em> to see its goodness and value.<\/p>\n 6. Creating & Using Range Names:<\/strong> A Range is a group of cells that is given a name. Now it becomes a named range, and it can be be used to reference multiple cells within Excel formulas. They make more sense, and are an absolute fun to read too and get the meaning instantly.<\/p>\n Instead of referring to a formulas, such as:<\/p>\n =C5+P5-Z5*F3-AA5, <\/em><\/strong><\/p>\n with range names defined, the same formula could now read as:<\/p>\n = basic+Overtime_Allowance-total_salary*Tax_Rate-loan_installment<\/em><\/strong><\/p>\n Ranges can be created with the Name Box<\/em>, or by going to the Name Manager<\/em> (Formula > Name Manager<\/em><\/strong>).<\/p>\n Once defined, you can edit, rename, apply or delete a named range. And name ranges work across worksheets and workbooks too.<\/strong><\/p>\n Range Names are an absolute must know features that ranks among my Top 5 Things in Excel that I use the most often…<\/p><\/blockquote>\n 7. Templates:<\/strong> If you’d like to save time doing some step repeatedly, you can use Templates. There are literally thousands of ready made templates available… some from Microsoft’s own site, and you can also create your own templates and reuse them.<\/p>\n Ready Templates for Invoices, Billing Statements, Calendars, Flow charts, monthly budget, attendance tracking, inventory management, Gantt charts, to-do lists, schedules and trackers<\/strong><\/em> are aplenty. Simply click and download a new one anytime you need. And once you have customized it to your use, you can save it on your computer, to reuse at a late time too.<\/p>\n 8. Info:<\/strong> The Info tab for Excel is available from File > Info<\/em><\/strong>. It can help you to inspect your workbook, set a password to protect it, check the version history, and Manage versions or recover an unsaved workbook.<\/p>\n You can also use the Info button to key in the Keywords, Author, Title and Categories<\/strong><\/em> for your Excel files. It can come in handy when you are searching for a lost Excel file, but can’t remember its filename.<\/p>\n Advanced Properties<\/em><\/strong> can even show the total time taken to edit. You can <\/em>add specific custom attributes to track in the Excel file, and edit them in the future.<\/p>\n 9. Drawing Tools: <\/strong>If you use a touch screen laptop, a tablet or a phone, you can use the draw tools to draw shapes, key in data, convert from ink to text pretty easily.<\/p>\n Now there is support for the Apple Pencil, and the Surface Pens. So touch users are taken care of. There are additional accessibility options available too.<\/p>\n <\/p>\n 10. Conditional Formatting:<\/strong> A handy tool to selectively find and color cells that meet a certain criteria. It can be useful to identify high values, low values, cells equal to a certain value, find duplicates<\/em> in a jiffy. You can specify your own rules too, and edit or manage them anytime.<\/p>\n Conditional formatting also allows you to display data bars, gradient colors like a traffic light, or display icons that make it apparent what you are looking for. A great management dashboard utility is hiding here.<\/p>\n Conditional Formatting made its way with Excel 2007, and in Microsoft 365 it is even better. I am surprised that people are still unaware of Conditional Formatting after 15 years.<\/p>\n Conditional Formatting is my Must Use tool when creating management dashboards and Reports that can highlight stuff on auto pilot.<\/p><\/blockquote>\n 11. Custom Formatting:<\/strong> Before Conditional Formatting, all you could do to identify cells based on simpler rules was to color them using Custom Formatting. It had 4 basic sets – colors for positive numbers, negative numbers, zero formatting, and a specific formatting for Text cells<\/em>.<\/p>\n A good many people use Custom Formatting to add a Prefix or a Suffix to data values. It is worth exploring custom formatting. A great resource for learning Custom Formatting<\/a> is the video tutorial by Leila Gharani, MVP<\/em>.<\/p>\n 12. Sorting:<\/strong> With quick buttons to sort in the ascending or descending order, you can click on A-Z or Z-A icons. Excel now automatically requests you to expand the selection, to avoid getting all your rows and columns messed up after a sort.<\/p>\n With Custom Sorting<\/em>, you can specify a particular sorting order. For example, you want to sort departments by Finance, Sales, Accounts, Manufacturing & Services, in that order. In such cases, neither ascending sort, nor a descending sort will be useful. Custom sort can do this quickly. Previously it was hidden under File > Tools > Options<\/strong><\/em>. Now the custom sort is available from the Data > Sort<\/strong><\/em> menu. Advanced Sorting<\/strong> can allow you to up your game, and sort as well as sub-sort on a plethora of columns. So multi-level sorting by Country, then by Department, then by Division, and finally by Cost center is no problem<\/em> at all.<\/p>\n Excel on the web now has the option to sort by conditional Formatting<\/a> too. This is really awesome! Thanks Danielle<\/a> for adding this!<\/p>\n 13. Filtering<\/strong> adds a quick filter all the columns on the header row. Then you can filter a particular value, above or below a particular value, Top N or Bottom N results<\/em> quickly.<\/p>\n Advanced Filtering takes your filtering options to the next level. Now you can filter the list, but get the filtered results copied to another area of your Excel sheet.<\/p>\n Specifying multiple criteria, and keeping the criteria visible are the two plus points in the Advanced Filter.<\/p><\/blockquote>\n <\/p>\n There are over 500+ functions in Microsoft Excel<\/strong>. You don’t have to learn all of them, but it is good to know as many as you can learn, and some are good to know, in case you need… The actual syntax can be looked up when needed.<\/p>\n Before we jump to functions, an important concept to learn is cell referencing.<\/p>\n 14. Relative & Absolute Referencing:<\/strong> No formulas or functions can be useful if you don’t know when and how to convert a relative reference<\/strong> to a cell (C1<\/em><\/strong>) to an absolute reference<\/strong>, like ($C$1<\/strong><\/em>).<\/p>\n The $ sign fixes the row or the column from changing. When both the row and the column are fixed, it is considered Absolute Reference.<\/p><\/blockquote>\n And you can gain additional traction by using Mixed Cell Referencing – using $C1 or C$1.<\/em> <\/strong>This fixes only the row or the column.<\/p>\n And now for the most useful Excel functions. I had to divide them into further groups for easier understanding. I’ll go through them one by one.<\/strong><\/p>\n On the Home<\/strong> Tab are the Auto sum functions, hidden under the Sigma symbol.<\/p>\n 15. SUM:\u00a0<\/strong> Provides you with a Sum of values is the most common function used in Excel, to add a set of numbers. Behind the dropdown, you can use other functions to Count Values, Average Values, Find the Minimum or Maximum Value. Clicking on the More Functions option will pop-up the Insert Function dialog box. Now you can pick from any of the other available functions in Excel.<\/p>\n\n
\n
![\"Excel](\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2021\/10\/ExcelChamp_find_and_select.png?resize=180%2C384&ssl=1\")
<\/li>\n![\"Paste](\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2021\/10\/ExcelChamp_paste_special.png?resize=436%2C318&ssl=1\")
<\/p>\n![\"Create](\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2021\/10\/Excel_ready_templates-ExcelChamp-1024x811.png?resize=1024%2C811&ssl=1\")
![\"Dram](\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2021\/10\/Draw-in-Microsoft-Excel-With-Hand-or-Pencil.png?resize=958%2C161&ssl=1\")
<\/p>\nFormatting in Excel<\/h2>\n
![\"Conditional](\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2021\/10\/excel_conditional_formatting-excelchamp.net_.png?resize=447%2C768&ssl=1\")
<\/p>\n![\"Custom](\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2021\/10\/custom_formatting_in_Excel.jpg?resize=532%2C484&ssl=1\")
![\"Advanced](\"https:\/\/i0.wp.com\/www.excelchamp.net\/wp-content\/uploads\/2021\/10\/advanced-sorting-in-Excel.jpg?resize=586%2C268&ssl=1\")
<\/p>\nExcel’s Mega List of Amazing Functions<\/h2>\n
\n
Auto Sum Functions of Excel<\/strong><\/a><\/h3>\n