Silahkan baca: Fungsi Filter Pada Excel Dan Cara Membuatnya. Dalam menu delete cell dan format cell ini ada beberapa kategori pilihan yang bisa kita gunakan dalam membuat data di Ms Excel, untuk lebih jelasnya mari kita bahas satu persatu fungsi apa saja yang bisa kita gunakan dalam kedua menu ini.
Excel Shortcuts
- Some say “Mylar” when they mean polyester film or plastic sheet. Really, Mylar is a trademark registered by Dupont Tejjin Films for specific plastic sheet products made from Polyethylene Terephthalate (PET) resin. Grafix sells many types, grades, finishes and thicknesses. Contact us to learn about Mylar applications.
- Beberapa Icon dan fungsi di Group Editing: a. AutoSum (Alt+=): Untuk menjumlah isi sel yang dipilih. Fill: Untuk membuat deret data dalam suatu range, menyalin isi data suatu sel atau range. Clear: Untuk menghapus semuanya dari sel, nilai hingga format.
Below are 215 of the best Excel shortcuts (tested on Excel 2010, but 95% useable in Excel 2013 as well).
Download them in Excel – FREE!
Terminology:
Fungsi Clear Formatting Tool
| Sign | Meaning |
|---|---|
| + | Press keys simultaneously |
| – | Press keys in succession |
| , | Begin a new sequence |
| RC | Right-Click |
| RD | Right-Drag |
| <###> | Click on ### in context menu |
| MK | Menu Key |
| DC | Double Click |
Fungsi Dari Clear Formatting
Fungsi Clear Formatting Definition
| Category | Task | Excel Shortcut |
|---|---|---|
| Alt-A | Clear Filter | Alt-A-C |
| Alt-A | Filter | Alt-A-T |
| Alt-A | Refresh Connections | Alt-A-R |
| Alt-A | Advanced Filter | Alt-A-Q |
| Alt-A | Group | Alt-A-G-G |
| Alt-A | Sort Ascending | Alt-A-S-A |
| Alt-A | Sort Descending | Alt-A-S-D |
| Alt-A | Text To Columns | Alt-A-E |
| Alt-A | Custom Sort | Alt-A-S-S |
| Alt-A | Data Validation | Alt-A-V |
| Alt-A | Get External Data Existing Connections | Alt-A-X |
| Alt-A | Subtotal | Alt-A-B |
| Alt-A | What-If Analysis | Alt-A-W |
| Alt-A | Get External Data From Access | Alt-A-F-A |
| Alt-A | Get External Data From Text | Alt-A-F-T |
| Alt-A | Get External Data From Web | Alt-A-F-W |
| Alt-E | Delete (#1) | Alt-E-D |
| Alt-E | Find/Replace | Alt-E-E |
| Alt-E | Clear All | Alt-E-A-A |
| Alt-E | Clear Contents (#1) | Alt-E-A-C |
| Alt-E | Clear Formats | Alt-E-A-F |
| Alt-E | Delete Worksheet | Alt-E-L |
| Alt-E | Paste Special Pop-Up Menu (#1) | Alt-E-S |
| Alt-F | Recent Files (To Open) | Alt-F-R |
| Alt-F | Save (#2) | Alt-F-S |
| Alt-F | Close Workbook (#2) | Alt-F-C |
| Alt-F | Exit Excel (#1) | Alt-F-X |
| Alt-F | Save As (#1) | Alt-F-A |
| Alt-F | Options | Alt-F-T |
| Alt-F | Save & Send | Alt-F-D-A |
| Alt-R | New Comment (#1) | Alt-R-C |
| Alt-R | Delete Comment | Alt-R-D |
| Alt-R | Edit Comment (#1) | Alt-R-T |
| Alt-R | New Comment (#2) | Shift+F2 |
| Alt-R | Research (#2) | Alt+Click |
| Alt-R | Spelling (#1) | Alt-R-S |
| Alt-R | Spelling (#2) | F7 |
| Alt-R | Edit Comment (#2) | Shift+F2 |
| Alt-R | Previous Comment | Alt-R-V |
| Alt-R | Research (#1) | Alt-R-R |
| Alt-R | Share Workbook | Alt-R-W |
| Alt-R | Show All Comments | Alt-R-A |
| Alt-R | Thesaurus | Alt-R-E |
| Alt-R | Track Changes | Alt-R-G |
| Alt-W | Switch Windows | Alt-W-W |
| Alt-W | Freeze Panes | Alt-W-F-F |
| Alt-W | Arrange All | Alt-W-A |
| Alt-W | Custom Views | Alt-W-C |
| Alt-W | Full Screen | Alt-W-E |
| Alt-W | Split | Alt-W-S |
| Alt-W | View Side By Side | Alt-W-B |
| Alt-W | Zoom (With Presets) | Alt-W-Q |
| Alt-W | Zoom To Selection | Alt-W-G |
| Alt-W | Hide/Show Formula Bar | Alt-W-V-F |
| Alt-W | View Gridlines | Alt-W-V-G |
| Basic Formatting | Bold (#1) | Ctrl+B |
| Basic Formatting | Bold (#2) | Ctrl+2 |
| Basic Formatting | Copy & Paste Formats | Format Painter (double-click for repeated pastes) |
| Basic Formatting | Italics (#1) | Ctrl+I |
| Basic Formatting | Underline (#2) | Ctrl+4 |
| Basic Formatting | Italics (#2) | Ctrl+3 |
| Basic Formatting | Underline (#1) | Ctrl+U |
| Basic Navigation & Selection | Extend Selection By One Cell/Row/Column | Shift+Arrow |
| Basic Navigation & Selection | Jump To Edge Of Blank(S) | Ctrl+Arrow |
| Basic Navigation & Selection | Move One Cell | Arrow |
| Basic Navigation & Selection | Select Region | Ctrl+A |
| Basic Navigation & Selection | Jump To & Select Through Edge Of Region | Ctrl+Shift+Arrow |
| Columns & Rows | Delete Column(S) Or Row(S) Once Selected | RC-D |
| Columns & Rows | Extend Selection To A Range Of Columns/Rows | Ctrl+Spacebar (or Shift+Spacebar), then Shift+Arrow until you’re at end of range |
| Columns & Rows | Select A Range Of Columns/Rows (#1) | click on first header & hold while moving mouse across to end |
| Columns & Rows | Select A Range Of Columns/Rows (#2) | click on first header, then click on end header while holding down Shift |
| Columns & Rows | Select Entire Column / Row | Ctrl+Spacebar / Shift+Spacebar |
| Columns & Rows | Select Entire Column/Row | click on column letter / row number |
| Columns & Rows | Delete Selected Column(S)/Row(S) | Ctrl+- (yes, Ctrl, followed by ‘-‘) |
| Columns & Rows | Insert Column(S) Or Row(S) Once Selected (#1) | Ctrl++ (or Ctrl+Shift+=) |
| Columns & Rows | Insert Column(S) Or Row(S) Once Selected (#2) | RC-I |
| Context 1 & 2 | Delete (#2) | RC-D |
| Context 1 & 2 | Format Cells Dialog Box | RC-F (or RC-F-F-Spacebar depending on context) |
| Context 1 & 2 | Paste Values (#2) | RC-V |
| Context 1 & 2 | Insert Copied Cells | RC-E Spacebar (or RC-E depending on context) |
| Context 1 & 2 | Paste Formatting (#1) | RC-R (or RC-R-Spacebar depending on context) |
| Context 1 & 2 | Paste Special Sub-Menu (#2) | RC-S |
| Context 1 & 2 | Row Height | RC-R (or RC-R-R-Spacebar depending on context) |
| Context 1 & 2 | Clear Contents (#2) | Click on Context Menu option |
| Context 1 & 2 | Hide And Unhide | Click on Context Menu option |
| Context 1 & 2 | Insert | Click on Context Menu option |
| Cut, Copy & Paste | Copy | Ctrl+C |
| Cut, Copy & Paste | Paste | Ctrl+V |
| Cut, Copy & Paste | Cut | Ctrl+X |
| Find & Replace | Find | Ctrl+F |
| Find & Replace | Replace (#2) | Alt-E-E |
| Find & Replace | Replace (#1) | Ctrl+H |
| Formatting II | Paste Formatting (#1) | RC-R (F4 for repeated pastes) |
| Formatting III | Format Cells Dialog Box | Ctrl+1 |
| Formatting III | Create Borders | Ctrl+Shift+7 |
| Formatting III | Currency Format | Ctrl+Shift+4 |
| Formatting III | Percent Format | Ctrl+Shift+5 |
| Formatting III | Remove Borders | Ctrl+Shift+- |
| Formatting III | Font Size | Ctrl+Shift+P |
| Formatting III | Font Style | Ctrl+Shift+F |
| Formatting III | Comma Format (W/ 2 Decimals) | Ctrl+Shift+1 |
| Formatting III | Date Format | Ctrl+Shift+3 |
| Formatting III | General Format | Ctrl+Shift+` (accent grave) |
| Formatting III | Time Format | Ctrl+Shift+2 |
| Formatting III | Exponential Format | Ctrl+Shift+6 |
| Jumping To Cells | Go Back To Most Recent Cell (#1) | Ctrl+G, then Enter |
| Jumping To Cells | Go Back To Most Recent Cell (#2) | F5, then Enter |
| Jumping To Cells | Go To Specific Cell (#2) | F5, input cell reference, Enter |
| Jumping To Cells | Go To Direct Precedents / Dependents | Ctrl+[ / Ctrl+] |
| Jumping To Cells | Go To Specific Cell (#1) | Ctrl+G, input cell reference, Enter |
| Jumping To Cells | Go To Direct & Indirect Precedents / Dependents | Ctrl+Shift+[ / Ctrl+Shift+] |
| Jumping To Cells | Select Everything Between Current and Specified Cell (#1) | Ctrl+G, input cell reference, Shift+Enter |
| Jumping To Cells | Select Everything Between Current and Specified Cell (#2) | F5, input cell reference, Shift+Enter |
| Miscellaneous | Close Workbook (#1) | Ctrl+W |
| Miscellaneous | Delete Worksheet | Alt-E-L |
| Miscellaneous | Autofit Column Width (Or Row Height) | DC right/bottom border of column/row |
| Miscellaneous | Create Chart On Same Tab | Alt+F1 |
| Miscellaneous | Exit Excel (#2) | Alt+F4 |
| Miscellaneous | Explode Pivot Detail | DC pivot data |
| Miscellaneous | Hide Columns | Ctrl+0 |
| Miscellaneous | Hide Rows | Ctrl+9 |
| Miscellaneous | New Workbook | Ctrl+N |
| Miscellaneous | New Worksheet | Shift+F11 |
| Miscellaneous | Open Workbook | Ctrl+O |
| Miscellaneous | Save As (#2) | F12 |
| Miscellaneous | Unhide Columns | Ctrl+Shift+0 |
| Miscellaneous | Unhide Rows | Ctrl+Shift+9 |
| Miscellaneous | Autofill / Automatically Extend Sequence | Drag or DC fill handle of selected sequence |
| Miscellaneous | Create Chart On New Tab | F11 |
| Miscellaneous | Create Table (#1) | Ctrl+T |
| Miscellaneous | Create Table (#2) | Ctrl+L |
| Miscellaneous | Entering Characters In A Row (Vs. A Column) | Hit Tab instead of Enter |
| Miscellaneous | Help | F1 |
| Miscellaneous | Paste Name Dialog Box | F3 |
| Miscellaneous | Ctrl+P | |
| Miscellaneous | Rename Worksheet | DC tab name, then change name |
| Miscellaneous | Copy Formula From Above | Ctrl+’ (apostrophe) |
| Miscellaneous | Copy Only Visible Cells | Alt+; (after selecting, but before copying ) |
| Miscellaneous | Copy Value From Above | Ctrl+Shift+’ (apostrophe) |
| Miscellaneous | Hide Ribbon | Ctrl+F1 |
| Miscellaneous | Insert Current Date | Ctrl+; |
| Miscellaneous | Insert Current Time | Ctrl+Shift+; |
| Miscellaneous | Insert Current Date And Time | Ctrl+; , Spacebar, Ctrl+Shift+; |
| Moving Between Wkshts/Wkbks/Win | Go To Most Recent Workbook | Alt+Tab+Tab if in Excel; Alt+Tab if not in Excel, but Excel was most recent window |
| Moving Between Wkshts/Wkbks/Win | Open Switch Windows Drop-Down | Alt-W-W |
| Moving Between Wkshts/Wkbks/Win | Rotate Fwd/Bkwd Between Open Programs/Windows | Alt+Tab / Alt+Shift+Tab (keep tapping Tab key) |
| Moving Between Wkshts/Wkbks/Win | Go To Next/Previous Open Excel File | Ctrl+Tab / Ctrl+Shift+Tab |
| Moving Between Wkshts/Wkbks/Win | Move Right /Left to Next Tab | Ctrl+PgDn/PgUp |
| Moving Between Wkshts/Wkbks/Win | Select Multiple Adjacent / Non-Adjacent Worksheets | Shift+Click / Ctrl+Click on tabs |
| Moving Between Wkshts/Wkbks/Win | Jump To Tab (Good When Many Tabs) | RC on tab navigation arrows, then select tab |
| Navigating Dialog Boxes | Enter A Tab | Tab |
| Navigating Dialog Boxes | Go To Next / Previous Option In Tab | Tab / Shift+Tab |
| Navigating Dialog Boxes | Check/Unchecks Box | Spacebar |
| Navigating Dialog Boxes | Finalize Selections | Enter |
| Navigating Dialog Boxes | Go To Next / Previous Tab (#1) | Ctrl+Tab / Ctrl+Shift+Tab |
| Navigating Dialog Boxes | Go To Next / Previous Tab (#2) | Ctrl+Letter (if underlined letter) |
| Navigating Dialog Boxes | Go To Next / Previous Tab (#3) | First letter (if no underlined letter) |
| Navigating Dialog Boxes | Go To Next / Previous Tab (#4) | ←→ (if no underlined letter) |
| Navigating Dialog Boxes | Go To Next/Previous Option | ←→ (…but can get stuck) |
| Navigating Dialog Boxes | Navigate Dropdowns | ↑↓ or first letter (Enter or Tab selects option; ESC exits dropdown with no change) |
| Navigating Dialog Boxes | Go To Option w/ Underlined Letter | Alt+Letter (Checkbox items will be checked; Alt+Letter again will uncheck) |
| Navigating Dialog Boxes | Go To Radio Button W/Underlined Letter | Letter |
| Navigation & Selection For Big Data | Select region | Ctrl+A |
| Navigation & Selection For Big Data | Select Region & Move Viewpoint To Last Cell | Ctrl+A (from 1st cell in region) |
| Navigation & Selection For Big Data | Zoom In/Out (For Inspection) | Ctrl+spin mouse wheel |
| Navigation & Selection For Big Data | Go To Cell A1 | Ctrl+Home |
| Navigation & Selection For Big Data | Select Entire Sheet | Ctrl+(A-A) |
| Navigation & Selection For Big Data | Select Through “Last Used Cell” | Ctrl+Shift+End |
| Navigation & Selection For Big Data | Go To Cell A1, Select Region & View Last Cell | Ctrl+(Home-A) |
| Navigation & Selection For Big Data | Select Through Next Border | Ctrl+Shift+Arrow |
| Navigation & Selection For Big Data | Select (Remote) Region | Ctrl+* (or Ctrl+Shift+8) |
| Navigation & Selection For Big Data | Select (Remote) Region & View Last Cell | Ctrl+ *-↑-↓, Ctrl+A |
| Navigation & Selection For Small Data | Select Contiguous Region | Click on first cell, then Shift-Click on end cell |
| Navigation & Selection For Small Data | Select Non-Contiguous Cells Or Region | Select first region; press Ctrl while selectiong add’l regions |
| Paste Special | Paste Values (#1) | RC-V |
| Paste Special | Paste Formatting (#1) | RC-R |
| Paste Special | Paste Special Sub-Menu (#1) | RC-S |
| Paste Special | Paste Special – Values & Number Formats | RC-S-A |
| Paste Special | Paste Special Pop-Up Menu (#1) | Alt-E-S |
| Paste Special | Paste Special Pop-Up Menu (#2) | RC-S-S |
| Paste Special | Paste Formula (#1) | RC-F-spacebar |
| Paste Special | Paste Formula (#2) | RC-S-F |
| Paste Special | Transpose | RC-S-T |
| Paste Special | Paste Special Sub-Menu (W/ Screen Tips) | Alt-H-V |
| Paste Special | Add Zero For Text –> Number Format | RC-S-S-V-D- |
| Paste Special | Paste Link (#1) | RC- |
| Paste Special | Paste Link (#2) | RC-N-Spacebar |
| Paste Special | Copy And Paste Column Widths | RC-S-S-W-Enter |
| Paste Special | Multiply Cells By 1000 | RC-S-S-V– |
| Paste Special | Paste Special — Picture (#1) | RC-S-U |
| Paste Special | Paste Special — Picture (#1) | RC-S- |
| Right-Drag | Copy And Paste | Drag selection, release, all while pressing Ctrl |
| Right-Drag | Cut And Paste | Drag selection, release at destination |
| Right-Drag | Copy & Paste | RD-C |
| Right-Drag | Convert To Values “Wiggle” | RD-V (back onto copied selection) |
| Right-Drag | Copy & Paste Values | RD-V |
| Right-Drag | Insert Copied Cells & Shift Down | RD-S |
| Right-Drag | Insert Copied Cells & Shift Right | RD-T |
| Right-Drag | Paste Formatting (#2) | RD-F |
| Right-Drag | Insert Cut Cells & Shift Down | RD-D |
| Right-Drag | Insert Cut Cells & Shift Right | RD-R |
| Save | Save (#1) | Ctrl+S |
| Undo & Redo | Undo | Ctrl+Z |
| Undo & Redo | Redo (#2) | F4 |
| Undo & Redo | Redo (#1) | Ctrl+Y |
| Working With Formulas | Copy Formula One Cell Across | Ctrl+R |
| Working With Formulas | Copy Formula One Cell Down | Ctrl+D (will copy cell just above it) |
| Working With Formulas | Edit Formula, Get Visual On Formula References (#2) | Double-click formula cell |
| Working With Formulas | Copy Down Formula To End Of Adjacent Column | Double-Click “Fill Handle” (bottom right corner of cell) |
| Working With Formulas | Copy Down/Across Formula (#1) | Drag cell by Fill Handle |
| Working With Formulas | Edit Formula, Get Visual On Formula References (#1) | F2 on formula cell |
| Working With Formulas | Autosum | Alt+= |
| Working With Formulas | Cancel Formula Editing | ESC |
| Working With Formulas | Lock Cell References / Rotate Through Options | F4 (while in Edit Mode) / repeat F4 as needed |
| Working With Formulas | Copy Down/Across Formula (#3) | Select range, type formula, hit Ctrl+Enter |
| Working With Formulas | Faster Formula Entry | Begin with ‘+’ instead of ‘=’ |
| Working With Formulas | Faster Formula Entry (With Functions) | Type first letters of function, Tab, enter arguments, Enter |
| Working With Formulas | Copy Down/Across Formula (#2) | Ctrl+C, select target cells, Ctrl+V |
| Working With Formulas | Copy Down/Across Formula (#4) | Select range (first cell must be the one to be copied), then Ctrl+D / Ctrl+R |
| Working With Formulas | Display All Formulas Instead Of Values | Ctrl+` |
| Working With Formulas | Find Bottom Of Column, Then Copy Down Formula | Go to bottom, Ctrl+Shift+↑,Ctrl+D |
| Working With Formulas | Recalculate All Formulas In Workbook | F9 |
| Working With Formulas | Recalculate Formulas In Open Worksheet Only | Shift+F9 |