Here are some of my favorite keyboard shortcuts in Excel.
|ALT + Enter||When you are in a cell, this will add a new line
|ALT + =||SUM() formula
|CTRL + F||Find text on worksheet
|CTRL + H||Find text, and replace with something
|CTRL + B||Format bold
|CTRL + I||Format italics
|CTRL + U||Format underline
|CTRL + `||This shows formulas. Also to remove.
|CTRL + Shift + $||Format cell in currency format
|CTRL + Shift + &||Add borders to selected cells
|CTRL + Spacebar||Selects the whole column
|CTRL + ;||Inserts the current date in cell
|CTRL + TAB||Switch between open workbooks
|F2||Probably my most used shortcut. This is to edit a cell
Since about 1995, I’ve been creating random websites. Mostly for my own enjoyment. Some are still useful today, and some I just keep around for nostalgic purposes, and to clutter up the internet. They’ve been on lokey.com, lokeys.com, and lokey.biz. Now, their home is lokey.co. OK. I like my last name…
One of my more useful sites is a loan calculator. It shows the principal+interest, and total paid. At one time, I was into browser bookmarks. Some include getting the URL, and title of a web page. And a cool one can create a QR code of the URL so you can transfer it to a smart phone. Most recently, I’ve been playing around with Google Sites. I have a test page, and a page I created for a property I’m selling locally. Also created a stand alone page for it as well.
Check out the full list here.
These macros can be helpful in data clean-up. An example might be exporting large amounts of general ledger data with tons of extra lines in between the good data.
The key is to select the area that has the data. This might be column that has GL account amounts. The lines you want to delete do not have anything in this area.
In this screenshot, we want to delete the rows that are red in column N.
Here is the macro name “BlanksRows_Delete”- used for deleting:
In the above example, select the N column, then run the macro.
If you only want to hide the rows, you can use this macro “BlanksRows_Hide”
Selection.EntireRow.Hidden = True
Good tip in the August 2017, Journal of Accountancy By J. Carlton Collins, CPA
Microsoft Excel: Modify Excel’s default blank workbook
…a quick tip that you may find useful in your applications…
I have spreadsheets that produce journal entries that I can then import into the general ledger. I reuse these spreadsheets every month. I always make the effective date the last day of the month. To make it easy on myself (why not), I just enter the first day of the month in the cell above, and use this handy formula:
Results of formula
How it works
It takes the date (6/1/2017), then makes it the first day of the next month (7/1/2017), then subtracts one day, making it 6/30/2017.