Faster At Excel

  • Home
  • Book: Excel Shortcut Bible

  • Shortcuts
  • Blog
  • Archives
  • Links
  • My Account
  • Login
  • About
  • Contact

Basic Excel Macros: Part 2 Of 3, Editing & Deleting Excel Macros

October 18, 2016 by Terp Leave a Comment

(This is Part 2 of a 3-part series on Basic Excel Macros. Part 1 is Creating & Saving Excel Macros.  Part 3 will cove some issues with Personal.xlsb and how to add a macro to your Quick Access Toolbar.)

How To Edit & Delete Basic Excel Macros

If you want to edit or delete the macro, you first need to Unhide PERSONAL.XLSB by clicking on the item below in the View tab:

unhide-personal-xlsb

Then make sure PERSONAL is selected in the window below, and hit OK:

unhide-personal-xlsb-2

Once you hit OK, you’ll be in the PERSONAL.XLSB workbook.

Then click on the Macros icon below: [Read more…]

Filed Under: Excel Macros Tagged With: Excel macros

Basic Excel Macros: Part 1 Of 3, Creating & Saving Excel Macros

October 11, 2016 by Terp Leave a Comment

What Is An Excel Macro?

Macros are pieces of code written in VBA (or Visual Basic) usually designed to automate repetitive tasks.

First, A Warning About Excel Macros

At one point I built macros for things like my number formatting, and assigned a convenient keyboard shortcut.   I also did the same for some basic data cleanup and formatting for reports coming out of the General Ledger.  I thought I was brilliant. I was sadly mistaken as Excel macros (for me) have a major flaw: you lose the ability to Undo (on all open workbooks).  You can imagine how I felt, having 10 workbooks open, in the middle of experimenting with several of them, when I realized I couldn’t undo the experiments and destructive mistakes I had just made.  Sure, I had saved a few minutes by formatting my new file with the macro, and it sure looked pretty.  But I was in deep trouble with my other files.  I couldn’t get back to a working version of those files.  It’s because of stories like this that I am very hesitant to embrace macros. It would be great if Microsoft were to fix this limitation.

I do make one exception.  If I have zero workbooks open (or I’m not concerned about Undoing anything), and then I export a big data sheet into Excel, then I have a special macro that saves me time.  I use Ctrl+Q to add data filters and then bold and freeze the top pane.  This is how I like my data formatted and set up.  In this scenario I’m not risking anything – no other open workbooks, and nothing that I would want to undo in my open data sheet.

You may decide to use macros more often than me.  They can certainly save you a lot of time.  And they can be used for tasks that are far more complex than my simple example below.  Just be fully aware of the risk you are taking — and take the necessary precautions (for example, always check your file before you run your macro).

How To Create a Basic Excel Macro

How do you create a macro?  Pretty easy.   In the Developers tab, click on the “Record Macro” icon that you see below: [Read more…]

Filed Under: Excel Macros Tagged With: Excel macros

Excel Sum Shortcuts

September 27, 2016 by Terp Leave a Comment

Here are two quick ways to do a sum in Excel:

Excel AutoSum

Excel’s AutoSum automatically inserts a SUM formula.  Just type Alt+= (‘Alt’ and ‘=’ simultaneously) under (or to the right of) the numbers you want to sum.

AutoSum: Alt+=

Excel will guess what range you want to sum.  It usually guesses the range correctly.  If not, you can adjust it manually by resizing the colored box (just drag one of the corners).  See the image below:

excel-autosum

You can just manually edit the sum range to be anything you want.  So even if Excel guesses incorrectly, at least you have a good shell of a formula.

There’s an even faster way, though, if you don’t need a permanent formula.

[Read more…]

Filed Under: Uncategorized

Excel Magic: 4 Clever Shortcuts To Copy And Paste Simultaneously

September 20, 2016 by terp Leave a Comment

Ready For Some Magical Excel Shortcuts?

Did you know you can copy and paste simultaneously in Excel?

Just use Ctrl+D, Ctrl+R, Ctrl+Enter or Autofill.

excel-copy-paste-ctrl-d-r-enter-autofill

These magic tricks don’t fit every situation.  But when they do fit, they’re lightning fast.

Below is a magic show — and after that the magic tricks are discussed. [Read more…]

Filed Under: Copy & Paste, Double Click, Mouse Shortcuts Tagged With: cut copy paste, Double Click, Excel Keyboard Shortcuts, Excel Mouse Shortcuts, Excel Shortcuts

12 Basic Excel Data Analysis Shortcuts

September 14, 2016 by terp Leave a Comment

Easy Shortcuts For Excel Data Analysis

Below are 17 steps I take with fresh data, along with the shortcuts I use.

12 deal directly with data analysis; the other 5 are generic.

If you do a lot of Excel data analysis, these shortcuts will help you fly through your work much faster.  Consider them arrows in your quiver.

 

excel-data-analysis-shortcuts

[Read more…]

Filed Under: Alt Shortcuts, Charts, Find & Replace, Keyboard Shortcuts, Save, Sort Tagged With: Alt Shortcuts, Excel Keyboard Shortcuts, Excel Shortcuts, Filter, Find & Replace, freeze panes

  • « Previous Page
  • 1
  • 2
  • 3
  • 4
  • …
  • 11
  • Next Page »

Post Categories

Tags

Alt Shortcuts Books challenges Charts comments cut copy paste data Double Click Excel Formulas Excel Keyboards Excel Keyboard Shortcuts Excel macros Excel Mouse Shortcuts Excel Shortcuts Excel Speed Tricks F-Key Shortcuts Filter Find & Replace Font format Format Cells Format Painter Formatting freeze panes Hybrid Shortcuts Menu Key Open Paste Formats Paste Special Paste Values Products Quick Access Toolbar - QAT save Selection and Navigation Sort

Recent Posts

  • 3 Ways To Convert Text To Numbers In Excel
  • 5 Quick Data Set Up Excel Shortcuts
  • Excel Shortcut Bible: Comprehensive Excel Shortcut Book
  • 7 Easy Excel Speed Tips
  • Basic Excel Macros: Part 3 Of 3, Personal.xlsb & Adding A Macro To Your Quick Access Toolbar

© 2016 Faster At Excel