Any time you print a spreadsheet you should review and edit the Page Breaks before printing.

Page Break View Mode

Because page breaks are not shown by default in Excel, you will want to switch to Page Break Preview view mode in order to work effectively with them.

To switch from Normal View to Page Break Preview, activate the View Ribbon Menu. Then select Page Break Preview (ALT > W > I).

Below is an example of what the view looks like. A dashed line represents an automatic page break whereas solid lines stand for manual page breaks.

Tip: To go back to Normal View, go to View Ribbon > Normal (directly next to Page Break Preview) (ALT > W > L).

<!–Ads3–>

Insert Page Breaks

To insert a row (horizontal) page break, highlight the row directly below where you want to place the break. Then navigate to the Layout Ribbon Menu and select Breaks > Insert Page Break (ALT > P > B > I).

Result:

Tip: The SHIFT + SPACE shortcut allows you to conveniently select an entire row.

Follow the same steps to insert a column (vertical) Page Break. Select the column directly to the right of where you want to place your break. Then go to Page Layout > Breaks > Insert Page Break (ALT > P > B > I).

Tip: The CTRL + SPACE shortcut allows you to conveniently select an entire column.

Delete Page Break

To delete page breaks you can use almost exact same steps as if you were to insert one. Select the row below or column to the right of the page break and perform these steps:

Under the Page Layout Ribbon Menu, select Breaks then Remove Page Break (ALT > P > B > R)

Note: Automatic page breaks cannot be deleted

Move Page Breaks

If you happen to mess up where you placed your breaks, don’t fret. You can simply move them to the desired location.

Activate Page Break Preview (ALT > W > I), and simply hover over any page break lines until the <-> shows up then drag it to the desired location.

Warning: If you happen to move an automatic page break, it will turn into its manual counterpart.

<!–Ads1–>

Reset Page Breaks

Sometimes, you want to reset all of the Page Breaks in a worksheet..

On the ribbon, head back to where you would insert a page break and select the reset option (ALT > P > B > A)

Hide Page Breaks in Normal View

Page breaks may appear in Normal View Mode; they can be useful or an eye sore. To hide them, follow these steps:

Click the File tab on the ribbon then select Options on the left menu:

Then navigate to the Advanced subtab in the left menu. Scroll down to Display options for this worksheet and unselect Show Page Breaks.

<!–Ads2–>

Page Breaks – VBA

Everything mentioned above can also be accomplished using VBA using these code examples:

‘Add Row Page Break
Worksheets("Sheet1").Rows(40).PageBreak = xlPageBreakManual

‘Add Column Page Break
Worksheets("Sheet1").Columns("Z").PageBreak = xlPageBreakManual

‘Clear Row Page Break
Worksheets("Sheet1").Rows(40).PageBreak = xlPageBreakNone 

‘Clear Column Page Break’
Worksheets("Sheet1").Columns("Z").PageBreak = xlPageBreakNone

‘Set Activesheet to Page Break Preview Mode
ActiveWindow.View = xlPageBreakPreview

‘Restore Activesheet to Normal View Mode
ActiveWindow.View = xlNormalView

The post Working with Page Breaks in Excel & VBA appeared first on Automate Excel.

Spell Check in Excel

Microsoft Excel offers a basic spelling function that can help you check your spelling on any spreadsheet that you produce. This function is very limited, in relation to the spell check function in Microsoft Word or Microsoft PowerPoint. The Microsoft Word and Microsoft PowerPoint’s spell check function underlines the misspelled words as you write, while the Microsoft Excel spell check function does not. The function in Microsoft Excel will only notify you of misspelled words if you run the spellcheck feature. Also, it does not identify grammatical errors.

How to use spell check in Microsoft Excel

It is very easy to check your spelling within Microsoft Excel:

1. Go to the ‘Review’ tab in the ribbon in the Excel spreadsheet.
2. In the ‘Review’ tab, activate the option ‘Spelling’ (found on the left hand side) to spell check the worksheet (or use the shortcuts ALT > R > S or F7 to activate Spelling).

3. Once the spell check is initiated, a Spell Check dialogue box will appear (shown below).

It is really important to understand the different options available in this dialogue box. Here are brief definitions of each of the available options:

Ignore Once: This option allows you to disregard a spell check error suggestion for a word once.
Ignore All: This option allows you to disregard a spell check error suggestion for a word indefinitely on the spreadsheet.
Add: This option allows you to add a word that spell check regards as an error to the Microsoft Excel dictionary, as long as the word is used correctly. This will make the word acceptable within Microsoft Excel and will no longer be deemed as a spell check error.
Change: This option allows you to select a suggestion for a spell check error in order to automatically correct the misspelled word. There may be more than one suggestion for the misspelt word. Select the suggestion that you would like to apply.
Change All: This option allows you to change all of the spell check errors that apply to the same misspelled word with the selected suggestion of your choice.

AutoCorrect: This option changes a misspelled word with your selected suggestion and adds it to the autocorrect list. This allows excel to automatically correct that same misspelled word to the selected suggestion.

The Spell Check dialogue box shows you suggestions to misspelled words. Clicking the ‘Change’ button will automatically correct the spelling.

You can also disregard the suggestions provided to you by simply clicking ‘Ignore Once’ or ‘Ignore All’ button.
Once the spell check is complete, an alert will notify you:

Keyboard Shortcuts to Spell Check in Microsoft Excel

Press F7 to initiate the spell check feature. Alternatively, you can use the ALT Shortcut Alt > R > S.

How to Spell Check Multiple (or All) Worksheets at Once

The spell check feature only applies to the spreadsheet that is active. However, there is a method that you can follow in order to apply the spell check function to multiple sheets at once:

Right-click on the name of your active spreadsheet.
A menu will appear with an option ‘Select All Sheets’.
Click ‘Select All Sheets’

By clicking ‘Select All Sheets’, you group the spreadsheets together as one in order to run the spell check function on all of the worksheets in the workbook.

Instead of using ‘Select All Sheets’, you can also hold down CTRL and / or SHIFT to manually select multiple worksheet tabs to spell check multiple sheets at once. Then activating Spell Check will address the spell check errors identified in the first sheet going from left to right.

Once finished, you will be notified:

How to Spell check using VBA

While Excel’s main function is not word processing, many spreadsheets can accumulate a large amount of text, and spell checking a worksheet is usually the last thing people think to of doing.

If your spreadsheets fall in this category, you might try some creative ways to launch Excel’s “SpellChecker” from code as a gentle reminder.

Launch Spellcheck from VBA

The syntax to launch the Spelling dialog box using the default settings:

sheet1.cells.CheckSpelling

Spell Check Before Closing Workbook (VBA BeforeClose)

The following is example code to launch the Spelling dialog box before a workbook closes, but before asking to save. The expression tells VBA to check the spelling of all the cells in Sheet1. For the code to work you must place it in the ThisWorkbook code window in VBA.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet1.Cells.CheckSpelling
End Sub

Highlight Misspelled Words with a VBA Macro

At the beginning of this tutorial we mentioned that, unlike Word & PowerPoint, Excel does not underline misspelled words while you work. Instead you must run the spell check manually. However, you can create a macro that highlights all the cells containing misspelled words:

Sub Highlight_Misspelled_Words()
Dim cell as Range
For Each cell In ActiveSheet.UsedRange
  If Not Application.CheckSpelling(Word:=cell.Text) Then cell.Interior.ColorIndex = 6
Next
End Sub

This won’t automatically highlight misspelled cells as you work. However, you could program it to run based on a timer or before closing the workbook.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Stop searching for VBA code online. Try the Code VBA Add-in…

[convertkit form=4869294]

<<Return to VBA Examples
Did you find this tutorial useful? Then share it with your friends and colleagues:

The post How to Spell Check in Excel & VBA appeared first on Automate Excel.

There are several common keyboard layout shortcomings that are especially limiting:

1. A fn key directly to the left of the CTRL key on the bottom-left of the keyboard.                              
2. Small or unusually shaped arrow keys.
3. Out of place Page Up, Page Down, End, or Home keys.

If your keyboard has any of these layout flaws (especially #1!), you should consider buying a new external keyboard.

Keyboard Recommendations

There are plenty of good keyboards available, but we’ve picked out 4 representing the different price points. They range from $10 for the cheapest to $89 for an unusually ergonomically designed keyboard.

The Cheapest

This is the HP K1500 Wired Keyboard. It’s cheap and functional. The keyboard layout is great, but it’s not ergonomically designed, and it’s a wired keyboard. If you won’t use this keyboard often, this might be the best choice, simply because it’s the cheapest. Price: $10

Wireless Keyboard

This is the Logitech Wireless Combo MK270 with Keyboard. This is a definite upgrade over the entry-level keyboards. The keyboard is wireless, has media keys to quickly adjust the volume (among other things), and the typing is quieter. The only downside is that this keyboard isn’t very ergonomic. Price: $27

Small Wireless Keyboard

This is the Logitech Wireless Keyboard K360. The key selling point of this keyboard is that it’s very compact. It travels very well. If you travel frequently this might be the best keyboard for you. Price: $18

Ergonomic Keyboard

Logitech Wireless Wave Mk550 Keyboard. This keyboard is a good choice for those desiring an ergonomic keyboard. Its only downside is it’s a very large keyboard, so it is not ideal for traveling or for small spaces. Price: $41

The Ergonomic Superstar Keyboard

Kinesis Freestyle2. This ergonomic keyboard is a little unusual. It splits in half, allowing you to position the keyboard pieces in the ideal position for both your hands. You can also buy this accessory to angle the keyboard for a more natural wrist angle. Kinesis goes into more detail on the benefits here. You will also want to buy a numeric keypad accessory for easier entering of numbers. With all the accessories this keyboard becomes very expensive, but many people claim it’s worth it for the ergonomic benefits. Besides price, the other downside to this keyboard is the key layout. It’s unusual, and slightly worse than a typical keyboard for using Excel keyboard shortcuts. Price: $89

[inline][script class=”skimlinks_ref_script” type=”text/javascript” src=”//s.skimresources.com/api/ref.js?p=90629&d=1544154&t=5″][/script][/inline]

The post The Best External Keyboards for Laptops appeared first on Automate Excel.

Instead, we suggest the following response:

1. Qualify your response by indicating that while you think you’re good at Excel, that you want to become much better.

It really depends on who you compare me to.  Compared to my classmates I think I’m very advanced, but being a student there’s only so much experience with Excel I can have. I’m happy with where I’m at, but I know I have much to learn.

2. Name drop some Excel features.

I know vlookups, PivotTables, text functions, and I have a little experience with macros.

3. Mention areas that you are working to improve.

Recently, I’ve really been concentrating on keyboard shortcuts and not using the mouse.  I’ve seen people work ridiculously fast with just the keyboard and want to get to that level.

4. Going along with item #3, mention how you’re working to improve.

I’ve actually been working through this Excel Tutorial <Insert Name here> and I’ve really been learning a lot.

Rememeber Spreadsheet Boot Camp has a free Efficiency Trainer that you can download. Try referencing that!

I’ve really been concentrating recently on keyboard shortcuts and not using the mouse. You can work so much faster that way. I’ve been going through an “Efficiency Trainer” by Spreadsheet Boot Camp to help learn the techniques I need.

From my own experience, I’ve noticed interns with weak Excel skills be “cast aside” and not given work or attention during their internship. Training them takes too much effort. So you can bet employers want to weed out potential hires without strong Excel skills. Not because Excel skills = competence, but because if you have weak Excel skills you’re going to waste other people’s time while they bring you up to speed. So, go out and learn Excel!

The post How to Respond to the Interview Question, “How would you describe your Excel abilities?” appeared first on Automate Excel.

At Spreadsheet Boot Camp we’ve given tons of presentations, and the most frequent lines we hear from Excel users are “wow! I didn’t know you could do that!” and “wow! I didn’t know you could do that that way!” Both of these comments are generally followed up with “I’ve been wasting so much time!” These sentiments persist across every type of user in almost every industry.

The problem is one of awareness. If you’ve never seen another person swim a lap, and you discover that you can float in water, you might think you’re swimming. If you watch the Olympics, and you pantomime the strokes to push yourself across a pool, you might think you’re pretty good. It’s not until we get into the pool and race against other people that we learn how we stack up, and then we nurse our diminished pride or grow our confidence accordingly. This is true of most things we do: we do them inefficiently until we’re compelled to be efficient, and it is often a socially- or ego-driven pressure that compels us. This is not to say that humans are naturally lazy or complacent – we might be – but that we like to work smart rather than work needlessly hard. That doesn’t mean avoiding hard work, but your putting in 80 hours a week sounds a lot less impressive when I can get the same results working nine-to-five.

Excel is like any tool in that we tend to feel like we’re being efficient just by using it. But think about it this way: if you’re paying your lawyer, maid, or blogger (ahem) by the hour, wouldn’t you rather they do more work in less time? If you use Excel to complete repeatable tasks at work, wouldn’t you rather spend fewer hours and expend less energy in performing those tasks? We think the modern worker is smart, but we don’t think she works particularly smart. We think a good percentage of Excel users could cut the time they spend working in Excel in half simply by learning efficient techniques.

While it’s very easy to open Excel and “use” it, to doggy paddle (continuing with the swimming reference), it takes some effort to recognize that we’re floundering. It’s especially difficult to audit ourselves when we’re creating spreadsheets, a process which is generally informal and fluid, spurred by an urgent need for information. Managers expend extra effort to make sure these processes evolve efficiently, but rarely do they think about baseline improvement: training users on the use of shortcuts, efficient navigation, and easy, understandable formatting.

To that end, Spreadsheet Boot Camp is proud to introduce the Excel Efficiency Trainer. Our aim with the Efficiency Trainer is to help the user recognize his or her inefficient techniques and permanently correct bad habits. Beginning with a time trial in navigation and formatting, the user then progresses into a tutorial that will teach and reinforce the use of shortcuts with which the user might not be familiar. Finally, the user runs through the time trials again applying the newly learned techniques, cutting down time expended with needless mouse usage and sloppy methods. Each time the user runs through the tutorial and time trials, she’ll see her time cut down until basic tasks are second nature.

If you’re a fan of “busy work,” if you hunt-and-peck, or if you enjoy wasting time, the Excel Efficiency Trainer might not be for you. For the rest of you, download the FREE Trainer and start working more efficiently!

The post How Much Time Are You Wasting in Excel? appeared first on Automate Excel.

In the article below you can see how D. Casey Flaherty, corporate counsel at Kia Motors America, conducts a technology audit for law firms and cuts billing rates when a firm fails.

 Big Law Whipped for Poor Tech Training

Frustrated by ridiculous bills for routine “commodity” matters, Flaherty decided to strike back, and recently launched his technology audit program, where firms bidding for Kia’s business must bring a top associate for a live test of their skills using basic, generic business tech tools such as Microsoft Word and Excel, for simple, rudimentary tasks.

So far, the track record is zero. Nine firms have taken the test, and all failed. One firm flunked twice.

“The audit should take one hour,” said Flaherty, “but the average pace is five hours.” In real life, that adds up to a whole lot of wasted money, he said.

As a lawyer, you want to make your billable hours count, adding value for the client and your firm. Having a good base of Excel knowledge is an easy starting point toward that end.

Spreadsheet Boot Camp is perfect for busy law professionals or students. The ability to learn at your own pace, track your progress, and easily jump to different topics allows you to learn Excel around a busy work or class schedule. The fully immersive “boot camp” approach allows you to learn as efficiently as possible, making you Excel proficient in as little as 10 hours. Download the free trial and see for yourself.

The post Do Lawyers Need to Know Excel? appeared first on Automate Excel.

The internet is full of advice lists on how to succeed at your internship. Most of them include items that should be common sense: don’t show up late, don’t drink excessively at work outings, don’t wear sandals to work, don’t text while your boss is working with you. Even though these should be common sense, I’ve personally witnessed all of those work faux paus in the corporate world (shockingly the person who texted while working with her boss did not receive a full-time offer). We don’t want to regurgitate information, so we’re taking a fresh approach and helping you understand the mindset of hiring committees.

In our experience, hiring committees look at three main areas. Fit, Engagement, and Competency. Keep in mind, while first impressions are important, showing growth is more important.  Your company will understand there can be some awkwardness and a learning curve when you start. It’s how you adapt that matters, so if you start off your internship on rocky footing know you have plenty of time to turn it around.  With that said, let’s get to the three main factors.

Fit – How well do you fit with this career/company

Questions hiring committee will ask:

• Would they be comfortable with you interacting with a client?
• Do you fit with the corporate culture?
• How much do your coworkers like you?  More importantly, do they dislike you? (This won’t actually be asked, but you can be sure it will play a factor.)

Tips for how to influence the discussion:

• Understand your office dress code.  Before you start, reach out to your contact and ask for some clarification. Business casual can mean vastly different things. You probably know that you shouldn’t dress down relative to the office, but you also shouldn’t dress up too much.  If your office is a very casual business casual and you wear a suit everyday, you will stand out negatively.  If your office has casual Fridays (and people participate) then you should dress down on Fridays.
• As an intern your job is to make everyone else’s life easier. You will be expected to do boring and tedious projects. Always keep a positive attitude. When approaching these projects ask yourself if there is away to make the process more efficient and less painful: document your work clearly, look for ways to automate the process, or clean up existing spreadsheets. Remember: Spreadsheet Boot Camp’s best practice  sections are a great way to understand how to create well functioning spreadsheets. Volunteer to clean up or improve an existing spreadsheet and you’ll create a memorable accomplishment and demonstrate you’re a self-starter.
• Make sure people enjoy talking to you. Keep your conversations positive and nonconfrontational. Try to participate in office “water cooler” talk.
• Don’t annoy your coworkers with questions. I’ve seen quite a bit of intern advice that encourages readers to ask lots of questions.  Asking questions is good to a point, but no one wants to be pestered with question after question. Before asking a question, go back one last time and see if you can figure it out on your own, search the internet for answers, or try asking a fellow intern. Keep in mind that the more experienced your coworker is, the more valuable his or her time is. When you ask questions try asking less experienced full-timers, even if you aren’t working directly with them.

Engagement – How well does this career/company fit with you

Questions hiring committee will ask:

• Did you enjoy your internship, the work you did, and the company?
• Is this still the career for you?
• If you received an offer would you accept?

Tips for how to influence the discussion:

• Act like you enjoy the work you are doing. Yes, you are an intern doing intern level work and no it’s probably not the most exciting work, but you need to find the positives. Keep a specific project (or projects) in the back of your mind at all times that you are  prepared to talk intelligently and positively about. Make sure you understand the end goal of the project you assisted on, not just your piece of it. Chances are your boss (or other important people) will ask you  casual questions about projects you’ve worked on. Don’t confuse these questions with simple small talk. Your boss is making mental notes on your engagement. Also, don’t be afraid to request certain projects from your boss. This will make you appear more engaged.
• Engage your coworkers. Make sure you interact with full-timers and interns. Get along with your coworkers. Not only is this something hiring committees will look at, but you can use that network.

Competence – Your capabilities

We’ve saved competence for last, not only because it’s the most important (if you don’t demonstrate competence then nothing else matters), but because it’s also the hardest perception to control.

Questions hiring committee will ask:

• Do you have the analytical skills necessary?
• Did you show growth over the course of your internship?  Did you learn quickly?
• Do you have the people skills necessary?
• Do you have the creative/outside-the-box problem solving skills necessary?

Tips for how to influence the discussion:

As we mentioned above, this is the hardest perception to control. To address how to control it, let’s break out competency into three main areas.

1. What you can naturally do (i.e. IQ) : At this point in your life there is not much you can do to change this.
2. What you have learned to do (i.e. Accounting Skills): You can change this, but it takes time.
3. What people think you can do: This is people’s perception of your competencies.  There are a few simple things you can do to appear as smart as possible:

• Learn the tools of the trade. In a highly technical work environment, the faster you learn the tools of the trade, the smarter you will appear.
• Focus on efficiency. The best way to appear competent is by working efficiently. If you use Excel, make sure the master the shortcuts. I’ve witnessed interns who were immediately dismissed from consideration for receiving a job offer because their Excel skills were poor and they took too long to complete tasks. It was impossible to distinguish if they were not competent or if they simply didn’t know Excel. No one wanted to waste their time working with them or helping them. Conversely, I’ve witnessed some people who were Excel shortcut whizzes. They were initially thought of as rock stars when it turned out they were merely average employees who took the time to master Excel shortcuts. Download our Free Excel Shortcuts PDFs, hang them up at your desk and start incorporating them. Resist the temptation to use the mouse and by the time you end your internship you’ll be flying around the screen, impressing your coworkers.  They will associate that skill with your entire skill set.
• Check your work. Complete a project, do something else, then return to the project to review it. Try to put yourself in your reviewers shoes and do high-level reasonability checks. If you’re using Excel, create built in reasonability checks. Learn more about them with Spreadsheet Boot Camp’s best practice sections.
• Use resources to answer questions. As we mentioned earlier, instead of asking your boss questions, try researching online, asking other interns, or less experienced full-timers.
• Carry a notepad. Keep notes when someone gives you instructions. You don’t want to force him or her repeat instructions.
• Keep an ongoing list of questions. Don’t ask them one at a time. Instead ask them when you can progress no further without answers. Try to set up your work so it’s easy to make changes based on the answers you receive.
• Look for ways to stand out. Volunteer to improve spreadsheets or processes. You’ll look like a go-getter and you will create a memorable accomplishment. Many times interns work on tedious projects that don’t allow them to demonstrate your skills. If you can improve a processes you can demonstrate value. Spreadsheet Boot Camp’s best practice sections are filled with information on how to create well functioning spreadsheets.

 Concluding Thoughts:

Most of this advice is advice you should implement throughout your internship, but what should you do before you start or when you’re bored the first weeks, while you wait for work to come your way?  Learn Excel (and/or any other tools you will use)! To drill home the point, I’ve witnessed a very high correlation between Excel skills and job offers for interns. Interns with the best Excel skills could produce higher quality work, faster. They were given more challenging projects and made memorable impressions when asked to clean up spreadsheets. Whether you use Spreadsheet Boot Camp or something else, learning Excel is a must for many interns seeking a job offer.

If you follow the advice in this post you should be well on your way to a successful internship.

Good luck!

The post “Excel” at Your Internship or First Full-Time Job appeared first on Automate Excel.